SQL 子查詢完全指南：類型、寫法與效能優化

什麼是子查詢？

一個查詢作為另一個查詢的條件。子查詢可嵌套在 SELECT、INSERT、UPDATE、DELETE 或另一子查詢中。

語法結構

SELECT ... FROM 表1 WHERE 列1 > (子查詢)

• 外層：父查詢
• 括號內：子查詢
• 執行順序：先執行子查詢得值，再執行父查詢

子查詢 vs 表連接

• 表連接可替換大多數子查詢
• 子查詢更靈活、形式多樣、適合作為篩選條件
• 部分子查詢（如標量子查詢、EXISTS）無法用連接替換

三大核心子查詢類型

1. IN / NOT IN 子查詢

子查詢回傳多行單欄或多欄（需用列建構器）。

-- 單欄
SELECT * FROM student WHERE student_id IN (SELECT student_id FROM absence);

-- 多欄（列建構器）
SELECT last_name, first_name, city, state 
FROM president 
WHERE (city, state) IN (SELECT city, state FROM president WHERE last_name = 'Roosevelt');

2. EXISTS / NOT EXISTS 子查詢

檢測子查詢結果集是否非空。EXISTS 為真/假，不回傳實際資料。

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')
    -- 執行邏輯

3. 相關子查詢

子查詢參考外部查詢的欄位，外部查詢每行都會執行一次子查詢。

SELECT * FROM outer_table o
WHERE EXISTS (SELECT 1 FROM inner_table i WHERE i.fk = o.pk AND i.status = 'active');

效能注意：相關子查詢在外部結果集上逐行執行，可能較慢。但寫得好（有索引支援）可勝過多連接+臨時表方案。

子查詢撰寫規範

1. 必須用括號包裹
2. 放在比較運算子右側（可讀性）
3. 子查詢不可包含 ORDER BY（除非配合 TOP-N）
4. 比較運算子：單行（=, >, <...）vs 多行（IN, ANY, ALL, EXISTS）

三種多表查詢方法對比

實戰效能比較：NOT IN vs NOT EXISTS vs LEFT JOIN

-- 1. NOT IN（最慢：子查詢全表掃描+排序）
SELECT a.hdr_key FROM hdr_tbl a 
WHERE a.hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl);

-- 2. LEFT JOIN + IS NULL（較好）
SELECT a.hdr_key FROM hdr_tbl a 
LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key 
WHERE b.hdr_key IS NULL;

-- 3. NOT EXISTS（最快：無大量鎖定掃描）
SELECT a.hdr_key FROM hdr_tbl a 
WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key);

驗證資料存在：EXISTS vs COUNT(*)

-- 差：全表掃描計數
IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx') > 0

-- 好：找到第一筆即停止
IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

總結

• 優先用 EXISTS/NOT EXISTS 替代 IN/NOT IN
• 相關子查詢確保連接欄位有索引
• 標量子查詢（回傳單值）可用於 SELECT 清單、WHERE、SET 子句
• 複雜邏輯優先考慮 CTE (WITH) 提升可讀度