PostgreSQL组合查询之UNION,EXCEPT,INTERSECT
2020-05-24 10:11:12 
阿炯
在PostgreSQL中对多个SELECT语句可以使用UNION，INTERSECT和EXCEPT进行集合处理，其中UNION用于求并集，INTERSECT用于求交集，EXCEPT用于求差集。用法如下：
query1 UNION query2
query1 EXCEPT query2
query1 INTERSECT query2
其中query1和query2的SELECT子句中字段个数必须相同，且对应的数据类型必须相同(如果int和bigint结果取bigint,varchar(5)和varchar(10)结果取varchar(10))，字段名可以不同，但是最终结果会以第一个SELECT子句中字段名称返回。
UNION有效地把query2的结果附加到query1的结果上(不过我们不能保证这就是这些行实际被返回的顺序)。此外，它将删除结果中所有重复的行， 就象DISTINCT做的那样，除非你使用了UNION ALL。
INTERSECT返回那些同时存在于query1和query2的结果中的行，除非声明了INTERSECT ALL， 否则所有重复行都被消除。
EXCEPT返回所有在query1的结果中但是不在query2的结果中的行(有时侯这叫做两个查询的差)。同样的，除非声明了EXCEPT ALL，否则所有重复行都被消除。
为了计算两个查询的并、交、差，这两个查询必须是“并操作兼容的”，也就意味着它们都返回同样数量的列，并且对应的列有兼容的数据类型。
intersect 可以查看表一和表二一样的数据，求交集：
select t1.name,t1.age,t1.country from table1 t1
except
select t2.name,t2.age,t2.country from table2 t2 order by name,age;
可以对两表使用where条件。
将except结果创建成一张新的表：
create table table3 as (
select t1.name,t1.age,t1.country from table1 t1
except
select t2.name,t2.age,t2.country from table2 t2 order by name,age;
);
下面来通过实例来简单的表述。
示例表和数据
create table t1(
fa int,
fb varchar(6),
fc varchar(10)
);
create table t2(
fd int,
fe varchar(10),
ff varchar(32)
);
insert into t1(fa,fb,fc) values (1,'freeoa','12'),(2,'FREEOA','5432');
insert into t2(fd,fe,ff) values (1,'freeoa','hkno'),(3,'foa','kinglive');
一.UNION
两个查询使用UNION的结果使用图片表示如下：
select fa,fb from t1 union select fd,fe from t2;
fa | fb
----+--------
1 | freeoa
3 | foa
2 | FREEOA
select fa,fb from t1 union all select fd,fe from t2;
fa | fb
----+--------
1 | freeoa
2 | FREEOA
1 | freeoa
3 | foa
UNION默认会去重，即重复数据只会返回一行，如果需要保留重复数据所有行可以使用UNION ALL。
二.EXCEPT
两个查询使用EXCEPT(A EXCEPT B)求差集的结果：
select fa,fb from t1 except select fd,fe from t2;
fa | fb
----+--------
2 | FREEOA
三.INTERSECT
两个查询使用INTERSECT求交集的结果使用图片表示如下：
select fa,fb from t1 intersect select fd,fe from t2;
fa | fb
----+--------
1 | freeoa
参考来源
7.4. 组合查询
7.4. Combining Queries
SQL EXCEPT vs NOT IN
query1 UNION query2
query1 EXCEPT query2
query1 INTERSECT query2
其中query1和query2的SELECT子句中字段个数必须相同，且对应的数据类型必须相同(如果int和bigint结果取bigint,varchar(5)和varchar(10)结果取varchar(10))，字段名可以不同，但是最终结果会以第一个SELECT子句中字段名称返回。
UNION有效地把query2的结果附加到query1的结果上(不过我们不能保证这就是这些行实际被返回的顺序)。此外，它将删除结果中所有重复的行， 就象DISTINCT做的那样，除非你使用了UNION ALL。
INTERSECT返回那些同时存在于query1和query2的结果中的行，除非声明了INTERSECT ALL， 否则所有重复行都被消除。
EXCEPT返回所有在query1的结果中但是不在query2的结果中的行(有时侯这叫做两个查询的差)。同样的，除非声明了EXCEPT ALL，否则所有重复行都被消除。
为了计算两个查询的并、交、差，这两个查询必须是“并操作兼容的”，也就意味着它们都返回同样数量的列，并且对应的列有兼容的数据类型。
intersect 可以查看表一和表二一样的数据，求交集：
select t1.name,t1.age,t1.country from table1 t1
except
select t2.name,t2.age,t2.country from table2 t2 order by name,age;
可以对两表使用where条件。
将except结果创建成一张新的表：
create table table3 as (
select t1.name,t1.age,t1.country from table1 t1
except
select t2.name,t2.age,t2.country from table2 t2 order by name,age;
);
下面来通过实例来简单的表述。
示例表和数据
create table t1(
fa int,
fb varchar(6),
fc varchar(10)
);
create table t2(
fd int,
fe varchar(10),
ff varchar(32)
);
insert into t1(fa,fb,fc) values (1,'freeoa','12'),(2,'FREEOA','5432');
insert into t2(fd,fe,ff) values (1,'freeoa','hkno'),(3,'foa','kinglive');
一.UNION
两个查询使用UNION的结果使用图片表示如下：
select fa,fb from t1 union select fd,fe from t2;
fa | fb
----+--------
1 | freeoa
3 | foa
2 | FREEOA
select fa,fb from t1 union all select fd,fe from t2;
fa | fb
----+--------
1 | freeoa
2 | FREEOA
1 | freeoa
3 | foa
UNION默认会去重，即重复数据只会返回一行，如果需要保留重复数据所有行可以使用UNION ALL。
二.EXCEPT
两个查询使用EXCEPT(A EXCEPT B)求差集的结果：
select fa,fb from t1 except select fd,fe from t2;
fa | fb
----+--------
2 | FREEOA
三.INTERSECT
两个查询使用INTERSECT求交集的结果使用图片表示如下：
select fa,fb from t1 intersect select fd,fe from t2;
fa | fb
----+--------
1 | freeoa
参考来源
7.4. 组合查询
7.4. Combining Queries
SQL EXCEPT vs NOT IN