TRUE/FALSE,不返回實際數據SELECT * / SELECT 1 / SELECT NULL 效果相同WHERE EXISTS (subquery)WHERE column IN (subquery)SELECT c.CustomerId, c.CompanyName
FROM Customers c
WHERE EXISTS (
SELECT OrderID FROM Orders o
WHERE o.CustomerID = c.CustomerID
);
外層逐行檢查:對每個客戶,子查詢查該客戶是否有訂單,有則保留。
-- EXISTS 寫法
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS (
SELECT * FROM titles
WHERE pub_id = publishers.pub_id AND type = 'business'
);
-- IN 寫法(結果相同)
SELECT DISTINCT pub_name
FROM publishers
WHERE pub_id IN (
SELECT pub_id FROM titles WHERE type = 'business'
);
-- EXISTS
SELECT au_lname, au_fname FROM authors
WHERE EXISTS (
SELECT * FROM publishers
WHERE authors.city = publishers.city
);
-- = ANY (等價於 IN)
SELECT au_lname, au_fname FROM authors
WHERE city = ANY (SELECT city FROM publishers);
-- 不出版商業書籍的出版社
SELECT pub_name FROM publishers
WHERE NOT EXISTS (
SELECT * FROM titles
WHERE pub_id = publishers.pub_id AND type = 'business'
);
SELECT DISTINCT 姓名 FROM xs
WHERE NOT EXISTS (
SELECT * FROM kc
WHERE NOT EXISTS (
SELECT * FROM xs_kc
WHERE 學號 = xs.學號 AND 課程號 = kc.課程號
)
);
邏輯:「找出選修了所有課程的學生」(雙重否定 = 全稱肯定)。
| 場景 | 推薦 | 理由 |
|---|---|---|
| 外表小、內表大、有索引 | EXISTS | 外層驅動,內層索引查找快 |
| 外表大、內表小 | IN | 子查詢結果集小,IN 可優化為連接 |
| 子查詢返回大量重複值 | EXISTS | EXISTS 遇到第一匹配即停止,IN 需處理全集 |
| 需比較多欄位 | EXISTS | IN 單欄位,多欄位需組合或用 EXISTS |
| NOT IN 含 NULL 值 | NOT EXISTS | NOT IN 遇 NULL 結果為空(三值邏輯陷阱) |
SEMI JOINEXPLAIN 觀察 select_type:DEPENDENT SUBQUERY vs SIMPLEEXISTS,「集合成員資格」用 INNOT IN (subquery)**,改用 NOT EXISTS 或 LEFT JOIN ... IS NULLLIMIT 1 明確語意(優化器可能自動優化)JOIN 替代子查詢