SQL 集合

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

union和union 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 |
+--------+