7. 集合操作
CREATE TABLE t1 (
C1 INTEGER,
C2 INTEGER
);
INSERT INTO t1 VALUES (1,2), (1,NULL), (2,2);
CREATE TABLE t2 (
C1 INTEGER,
C2 INTEGER
);
INSERT INTO t2 VALUES (1,2), (1,1), (NULL,2);
CREATE TABLE t3 (
C1 INTEGER,
C2 INTEGER
);
INSERT INTO t3 VALUES (1,1), (1,2);
7.1 UNION - 并操作
例2-29: 对表t1、表t2做UNION操作
SELECT * FROM t1 UNION SELECT * FROM t2;
c1 | c2
----+----
1 | 1
1 |
| 2
1 | 2
2 | 2
(5 rows)
7.2 EXCEPT - 差操作
例2-30: 对表t1、表t2做EXCEPT操作
SELECT * FROM t1 EXCEPT SELECT * FROM t2;
c1 | c2
----+----
1 |
2 | 2
(2 rows)
7.3 INTERSECT - 交集
例2-31: 对表t1、表t2做INTERSECT操作
SELECT * FROM t1 INTERSECT SELECT * FROM t2;
c1 | c2
----+----
1 | 2
(1 row)
7.4 DISTINCT - 去重
从示例的结果可以看出,结果集中还做了去重的操作。也就是说,UNION、EXCEPT、INTERSECT
中还隐式地隐含DISTINCT
操作,如果显式地指定上DISTINCT
关键字,它们将得到相同的结果。
-
UNION DISTINCT
例2-32: 对表t1、表t2做UNION DISTINCT
操作SELECT * FROM t1 UNION DISTINCT SELECT * FROM t2; c1 | c2 ----+---- 1 | 1 1 | | 2 1 | 2 2 | 2 (5 rows)
-
UNION ALL
如果不需要进行去重,可以通过指定ALL关键字实现。
例2-33: 对表t1、表t2做UNION ALL操作SELECT * FROM t1 UNION ALL SELECT * FROM t2; c1 | c2 ----+---- 1 | 2 1 | 2 | 2 1 | 2 1 | 1 | 2 (6 rows)