標準 SQL 三大集合運算
| 運算 | 關鍵字 | 數學意義 | MySQL 支援 |
|---|
| 並集 | UNION | A ∪ B | ✅ 完整支援 |
| 交集 | INTERSECT | A ∩ B | ❌ 不支援 |
| 差集 | EXCEPT (MINUS) | A - B | ❌ 不支援 |
前置條件
- 參與查詢的欄位數相同
- 對應位置資料型別相容
ORDER BY 只能放在最後，以數字指定欄位位置
UNION 範例（MySQL 原生）
-- 標準 SQL（MySQL 不支援）
SELECT * FROM student WHERE sdept = 'CS'
INTERSECT
SELECT * FROM student WHERE sage <= 19;
-- 替代 1：WHERE AND（最簡單）
SELECT * FROM student WHERE sdept = 'CS' AND sage <= 19;
-- 替代 2：IN 子查詢
SELECT * FROM student WHERE sdept = 'CS'
AND sno IN (SELECT sno FROM student WHERE sage <= 19);
-- 替代 3：EXISTS 相關子查詢
SELECT * FROM student sx
WHERE sx.sdept = 'CS'
AND EXISTS (SELECT 1 FROM student sy WHERE sy.sage <= 19 AND sy.sno = sx.sno);
-- 替代 4：JOIN
SELECT sx.* FROM student sx
JOIN student sy ON sx.sno = sy.sno
WHERE sx.sdept = 'CS' AND sy.sage <= 19;
EXCEPT 替代寫法（MySQL）
場景：查詢 CS 系但年齡>19 的學生（CS 系 - 年齡≤19）
-- IN
SELECT sno FROM sc WHERE cno = '1'
AND sno IN (SELECT sno FROM sc WHERE cno = '2');
-- EXISTS
SELECT sno FROM sc sx
WHERE cno = '1'
AND EXISTS (SELECT 1 FROM sc sy WHERE sy.cno = '2' AND sy.sno = sx.sno);
-- JOIN
SELECT sx.sno FROM sc sx
JOIN sc sy ON sx.sno = sy.sno
WHERE sx.cno = '1' AND sy.cno = '2';
集合操作結果排序規則
