SQL 集合運算子 IN / ANY / SOME / ALL / EXISTS 完整用法指南

運算子比較總覽

1. IN 運算子

固定集合

SELECT * FROM T_Book WHERE FYearPublished IN (2001, 2003, 2005);

動態子查詢

SELECT * FROM T_Reader
WHERE FYearOfJoin IN (SELECT FYearPublished FROM T_Book);

2. ANY / SOME (同義詞)

需搭配比較運算子 (>, =, <, >=, <=) 放在前面。

SELECT * FROM T_Reader
WHERE FYearOfJoin = ANY (SELECT FYearPublished FROM T_Book);
-- 等價於 IN，但 ANY 可用 >, < 等
SELECT * FROM T_Book
WHERE FYearPublished > ANY (SELECT FYearOfJoin FROM T_Reader);
-- 只要大於讀者入會年份中「任一」值即符合

錯誤示範：WHERE FYearPublished < ANY (2001,2003,2005) — ANY/SOME/ALL 不支援固定集合。

3. ALL 運算子

SELECT * FROM T_Book
WHERE FYearPublished < ALL (SELECT FYearOfJoin FROM T_Reader);
-- 出版年份必須小於「所有」讀者入會年份

⚠️ ALL 空集合陷阱

SELECT * FROM T_Book
WHERE FYearPublished < ALL (
    SELECT FYearOfJoin FROM T_Reader WHERE FProvince = 'JiangSu'
);

若子查詢無資料 (空集合)，ALL 判定為 TRUE，導致外層查詢回傳所有列。此為經典 Bug 來源，使用時務必確認子查詢非空，或改寫為 NOT EXISTS / IN 配合 NOT。

4. EXISTS 運算子

• 單目運算子，不比對特定欄位，子查詢可多欄。
• 語意：子查詢是否至少回傳一列。
• 效能通常優於 IN (特別是大量資料)，因為只要找到一列即停止掃描。

SELECT * FROM T_Category
WHERE EXISTS (
    SELECT 1 FROM T_Book
    WHERE T_Book.FCategoryId = T_Category.FId
      AND T_Book.FYearPublished < 1950
);
-- 找出至少有一本 1950 年前出版書籍的類別

效能與寫法建議

1. 優先用 EXISTS 替代 IN：子查詢有索引時，EXISTS 可短路求值。
2. 避免 ALL 空集合：改寫為 NOT EXISTS (SELECT 1 FROM ... WHERE ... AND col >= outer.col)。
3. ANY/SOME 可讀性：> ANY 等同 > (SELECT MIN(...))，< ALL 等同 < (SELECT MIN(...))，必要時改寫為聚合函數更直觀。
4. 固定集合小量值：直接用 IN (v1,v2...) 最簡潔。