对查询结果进行合并、剔除、取重操作可以通过UNION、EXCEPT和INTERSECT实现
任意一种操作都要满足以下两个条件:
1.字段的数量和顺序一致
2.对应字段的数据类型相兼容
一、UNION
类似集合的并集运算
示例:下面两个表,查询拥有银行存款账号或贷款账号的客户姓名
表Depositor:
Customer_name Acc_num
1 Nora 101
2 Robin 103
3 James 107
4 Jennifer 109
表Borrower:
Customer_name Loan_num
1 Nora 301
2 Robin 305
3 James 306
4 Jenne 308
无[ALL]修饰:
1 SELECT Customer_name FROM Depositor 2 UNION 3 SELECT Customer_name FROM Borrower
查询结果:
Customer_name
1 James 2 Jenne 3 Jennifer 4 Nora 5 Robin 6 Tom
有[ALL]修饰:
1 SELECT Customer_name FROM Depositor 2 UNION ALL 3 SELECT Customer_name FROM Borrower
查询结果:
Customer_name
1 Nora 2 Robin 3 James 4 Jennifer 5 Tom 6 Nora 7 Robin 8 James 9 Jenne
二、EXCEPT
类似于集合的减法,从前一个表中剔除两个表中相同的记录。
示例:还是基于上文两个表,查询只有银行存款账号而没有贷款账号的客户姓名
1 SELECT Customer_name FROM Depositor 2 EXCEPT 3 SELECT Customer_name FROM Borrower
查询结果:
Customer_name
1 Jennifer
三、INTERSECT
类似于集合的交集运算,选出两个表中相同的记录
示例:同样基于上文的两个表,查询既有银行存款账号又有贷款账号的客户信息
1 SELECT Customer_name FROM Depositor 2 INTERSECT 3 SELECT Customer_name FROM Borrower
查询结果:
Customer_name
1 James 2 Nora 3 Robin