SQL 集合

本文深入探讨了SQL中的集合操作符,包括UNION、INTERSECT和EXCEPT的使用方法和区别。UNION用于合并两个查询结果,同时去除重复记录;UNION ALL保留所有记录,包括重复项;INTERSECT返回两个查询的公共部分;EXCEPT则返回第一个查询结果中不在第二个查询中的记录。文章通过实例展示了如何使用这些操作符,并提供了在MySQL中模拟INTERSECT和EXCEPT的方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

union

intersect

except

 


集合操作符

当对两个数据集执行集合操作符时,应遵循以下规范:

  • 两个数据集必须有相同数目的列
  • 两个数据集中对应列的数据类型必须相同
SELECT 1 _num, 'abc' _str UNION SELECT 9 _num, 'xyz' _str;
+-----+-----+
| _num | _str |
+-----+-----+
|   1 | abc |
|   9 | xyz |
+-----+-----+

多个集合操作规则:

  • 在调用集合操作时,Intersect操作符比其它操作符具有更高优先级
  • 可以使用圆括号对多个查询进行封装,以明确执行次序 
(SELECT [...] UNION ALL SELECT [...])
INTERSECT
(SELECT [...] EXCEPT SELECT [...])
  • union

unionunion all可以连接多个数据集,区别在于union对连接后的集合排序并去除重复项,而union all保留重复项,使用union all得到的最终行数总是等于所要连接的各个集合行数之和。

下面符合语句中第一个查询获取分配到2号支行的所有柜员,第二个查询返回所有在第二支行开户的雇员,结果ID为10的雇员重复。

SELECT emp_id FROM employee WHERE assigned_branch_id=2 AND (title='Teller' OR title='Head Teller') 
    -> UNION ALL SELECT DISTINCT open_emp_id FROM account WHERE open_branch_id=2;
+--------+
| emp_id |
+--------+
|     10 |
|     11 |
|     12 |
|     10 |
+--------+

SELECT emp_id FROM employee WHERE assigned_branch_id=2 AND (title='Teller' OR title='Head Teller') 
    -> UNION SELECT DISTINCT open_emp_id FROM account WHERE open_branch_id=2;
+--------+
| emp_id |
+--------+
|     10 |
|     11 |
|     12 |
+--------+
  • intersect

intersect运算符是一个集合运算符,它只返回两个查询或更多查询的交集。

SELECT emp_id FROM employee WHERE assigned_branch_id=2 AND (title='Teller' OR title='Head Teller') 
    -> INTERSECT SELECT DISTINCT open_emp_id FROM account WHERE open_branch_id=2;
+--------+
| emp_id |
+--------+
|     10 |
+--------+

MySQL不支持INTERSECT操作符。 但是我们可以模拟INTERSECT操作符。

  • 使用DISTINCT运算符和INNER JOIN子句模拟MySQL INTERSECT运算符
SELECT DISTINCT e.emp_id FROM employee e INNER JOIN account a ON a.open_emp_id=e.emp_id 
    -> WHERE e.assigned_branch_id=2 AND a.open_branch_id=2 AND (e.title='Teller' OR e.title='Head Teller');

+--------+
| emp_id |
+--------+
|     10 |
+--------+
  • 使用IN运算符和子查询模拟MySQL INTERSECT运算符
SELECT DISTINCT emp_id FROM employee WHERE emp_id IN 
    -> (SELECT DISTINCT open_emp_id FROM account WHERE open_branch_id=2);
+--------+
| emp_id |
+--------+
|     10 |
+--------+

SELECT DISTINCT emp_id FROM employee WHERE emp_id IN 
    -> (SELECT DISTINCT open_emp_id FROM account WHERE open_branch_id=2)
    -> AND assigned_branch_id=2 AND (title='Teller' OR title='Head Teller');
+--------+
| emp_id |
+--------+
|     10 |
+--------+
  • except

except操作符返回第一个表减去与第二表重合元素后剩下的部分。

SELECT emp_id FROM employee WHERE assigned_branch_id=2 AND (title='Teller' OR title='Head Teller') 
    -> EXCEPT SELECT DISTINCT open_emp_id FROM account WHERE open_branch_id=2;
+--------+
| emp_id |
+--------+
|     11 |
|     12 |
+--------+
  • 使用IN运算符和子查询模拟MySQL EXCEPT运算符
SELECT DISTINCT emp_id FROM employee WHERE emp_id NOT IN 
    -> (SELECT DISTINCT open_emp_id FROM account WHERE open_branch_id=2) AND assigned_branch_id=2;
+--------+
| emp_id |
+--------+
|     11 |
|     12 |
+--------+

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值