掌握多表關聯、子查詢與集合運算是 SQL 進階的分水嶺。本文系統整理 ANSI JOIN、舊式 JOIN、子查詢四大類型、EXISTS 語義、TOP 分頁優化、UNION 集合運算,附帶效能考量與反模式警示。
連接條件置於 `FROM` 子句,語義清晰,支援外部連接語法標準化。
SELECT t1.col1, t2.col2
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.PK = t2.FK -- 內連接:僅返回匹配行
LEFT JOIN Table3 t3 ON t1.PK = t3.FK -- 左外連接:保留左表所有行
RIGHT JOIN Table4 t4 ON t1.PK = t4.FK -- 右外連接:保留右表所有行
FULL JOIN Table5 t5 ON t1.PK = t5.FK; -- 全外連接:保留雙邊所有行
表列於 `FROM`,連接/過濾條件混雜於 `WHERE`。
SELECT t1.col1, t2.col2
FROM Table1 t1, Table2 t2
WHERE t1.PK = t2.FK -- 內連接條件
AND t1.Status = 'Active'; -- 過濾條件
子查詢 = 巢狀 `SELECT`,可出現在 `SELECT`、`FROM`、`WHERE`、`HAVING`、`INSERT`/`UPDATE`/`DELETE` 的 `SET`/`WHERE` 等位置。
子查詢產生一個虛擬結果集供外層查詢使用,**必須加別名**。
SELECT d.DeptName, e.EmpCount
FROM Dept d
JOIN (
SELECT DeptID, COUNT(*) AS EmpCount
FROM Employee
GROUP BY DeptID
) e ON d.DeptID = e.DeptID;
效能提示:現代優化器常將其「展開」合併執行計畫,但複雜派生表可能阻礙索引下推,必要時改用 CTE (`WITH ...`) 或暫存表。
出現在 `SELECT` 列清單、`WHERE` 比較運算子右側。必須保證回傳 0 或 1 行 1 列,否則執行時報錯。
-- 列清單:計算每位員工薪資與部門平均差距
SELECT EmpName, Salary,
Salary - (SELECT AVG(Salary) FROM Employee e2 WHERE e2.DeptID = e1.DeptID) AS DiffFromAvg
FROM Employee e1;
-- WHERE:找出薪資高於公司平均者
SELECT * FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);
內層查詢引用外層查詢的欄位,**外層每一行都會執行一次內層查詢**。「相關」即參考外層相關欄位。
-- 找出每個部門薪資最高的員工 (經典相關子查詢)
SELECT DeptID, EmpName, Salary
FROM Employee e1
WHERE Salary = (SELECT MAX(Salary) FROM Employee e2 WHERE e2.DeptID = e1.DeptID);
效能警示:大資料量下相關子查詢極慢 (N 次掃描)。優化方向:改寫為 `JOIN`、派生表、`APPLY` (CROSS/OUTER APPLY) 或窗口函數 (`ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC)`)。
-- 同時選修課程 1 和 2 的學生
SELECT * FROM Student s
WHERE EXISTS (SELECT 1 FROM Enrollment e WHERE e.StudentID = s.StudentID AND e.CourseID = 1)
AND EXISTS (SELECT 1 FROM Enrollment e WHERE e.StudentID = s.StudentID AND e.CourseID = 2);
-- 所有課程及格 (無不及格記錄) 的學生
SELECT * FROM Student s
WHERE NOT EXISTS (SELECT 1 FROM Enrollment e WHERE e.StudentID = s.StudentID AND e.Score < 60)
AND EXISTS (SELECT 1 FROM Enrollment e WHERE e.StudentID = s.StudentID); -- 確保有選課
SELECT TOP (10) PERCENT WITH TIES EmpName, Salary
FROM Employee
ORDER BY Salary DESC;
SELECT EmpName, Salary
FROM Employee
ORDER BY Salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- 第 3 頁,每頁 10 筆
| 運算子 | 語義 | 重複值處理 | 關鍵限制 |
|---|---|---|---|
UNION | 聯集 (A ∪ B) | 預設去重 (`DISTINCT`) | 所有查詢: 1. 欄位數相同 2. 對應位置資料型別相容 3. 欄位順序對應 (非名稱) |
UNION ALL | 聯集 (保留重複) | **不去重,效能高得多** | |
EXCEPT | 差集 (A - B) | 去重 | |
INTERSECT | 交集 (A ∩ B) | 去重 |
排序:僅允許在**最後一個查詢**後加 `ORDER BY`,作用於最終結果集。結果集欄位名稱取自**第一個查詢**。
善用執行計畫 (`Ctrl+M`)、`SET STATISTICS IO/TIME ON`、`Query Store` 驗證改寫效果,才是 SQL 高手的必經之路。