【PostgreSQL 17】9 集合运算符

一、并集 UNION

UNION ALL 不会去重，效率更高

SELECT
	emp_id
FROM
	excellent_emp
WHERE
	year = 2018
UNION
SELECT
	emp_id
FROM
	excellent_emp
WHERE
	year = 2019
;

SELECT
	emp_id
FROM
	excellent_emp
WHERE
	year = 2018
UNION ALL
SELECT
	emp_id
FROM
	excellent_emp
WHERE
	year = 2019
;

二、交集 INTERSECT

SELECT
	*
FROM
	(VALUES(1),(1),(2)) t1(n)
INTERSECT
SELECT
	*
FROM
	(VALUES(1),(1),(3)) t2(n)
;

SELECT
	*
FROM
	(VALUES(1),(1),(2)) t1(n)
INTERSECT ALL
SELECT
	*
FROM
	(VALUES(1),(1),(3)) t2(n)
;

三、差集 EXCEPT

SELECT *
FROM (values(1), (1), (2)) t1(n)
EXCEPT
SELECT *
FROM (values(1), (3)) t2(n)
;

SELECT *
FROM (values(1), (1), (2)) t1(n)
EXCEPT ALL
SELECT *
FROM (values(1), (3)) t2(n)
;

四、优先级

INTERSECT 优先级高于 UNION 和 EXCEPT
相同的优先级按先后
可以用括号改变优先级

SELECT *
FROM (values(1)) t1(n)
UNION ALL
SELECT *
FROM (values(1)) t2(n)
INTERSECT
SELECT *
FROM (values(1)) t3(n)
;

(
	SELECT *
	FROM (values(1)) t1(n)
	UNION ALL
	SELECT *
	FROM (values(1)) t2(n)
)
INTERSECT
SELECT *
FROM (values(1)) t3(n)
;

参考资料

[1] 不剪发的Tony老师【PostgreSQL开发指南】第27~30节