萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 進階查詢技術全解:多表 JOIN、子查詢、UNION、TOP、派生表與相關子查詢

掌握多表關聯、子查詢與集合運算是 SQL 進階的分水嶺。本文系統整理 ANSI JOIN、舊式 JOIN、子查詢四大類型、EXISTS 語義、TOP 分頁優化、UNION 集合運算,附帶效能考量與反模式警示。

一、 多表連接查詢

1. ANSI SQL-92 JOIN 語法 (標準、推薦)

連接條件置於 `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;       -- 全外連接:保留雙邊所有行
  • INNER JOIN 可簡寫為 `JOIN`。
  • ON 子句 放連接條件;WHERE 子句 放過濾條件——嚴格區分,特別是外部連接中,將過濾條件放入 ON 會改變語義(變成內連接)。

2. 舊式 SQL Server JOIN 語法 (舊標準、不推薦新代碼使用)

表列於 `FROM`,連接/過濾條件混雜於 `WHERE`。

SELECT t1.col1, t2.col2
FROM Table1 t1, Table2 t2
WHERE t1.PK = t2.FK          -- 內連接條件
  AND t1.Status = 'Active';  -- 過濾條件
  • **缺點**:不支援標準外部連接舊語法 (`*=` / `=*`) 已棄用/移除;閱讀性差;易寫成笛卡兒積。
  • **唯一優勢**:極簡單的內連接寫法較短。

3. 連接類型語義圖解

  • 內連接:交集。
  • 左外連接:左表全集 + 交集 (右表無匹配處為 NULL)。
  • 右外連接:右表全集 + 交集。
  • 全外連接:左右全集合併。
  • 交叉連接:笛卡兒積 (`CROSS JOIN`),無 ON 子句,慎用!

二、 子查詢:邏輯分層的利器

子查詢 = 巢狀 `SELECT`,可出現在 `SELECT`、`FROM`、`WHERE`、`HAVING`、`INSERT`/`UPDATE`/`DELETE` 的 `SET`/`WHERE` 等位置。

1. 派生表 / 衍生表:置於 FROM 子句

子查詢產生一個虛擬結果集供外層查詢使用,**必須加別名**。

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 ...`) 或暫存表。

2. 純量子查詢:作為運算式回傳單一值

出現在 `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);

3. 相關子查詢:逐行動態運算

內層查詢引用外層查詢的欄位,**外層每一行都會執行一次內層查詢**。「相關」即參考外層相關欄位。

-- 找出每個部門薪資最高的員工 (經典相關子查詢)
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)`)。

4. EXISTS / NOT EXISTS:布林邏輯判斷

  • 只關心「是否存在」,不回傳資料,**效能通常優於 `IN`/`NOT IN`** (尤其 `NOT IN` 遇 NULL 陷阱)。
  • 子查詢 `SELECT *` 或 `SELECT 1` 均可,優化器忽略選取清單。
-- 同時選修課程 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); -- 確保有選課

三、 結果集限制與排序:TOP 與 OFFSET/FETCH

TOP 子句

SELECT TOP (10) PERCENT WITH TIES EmpName, Salary
FROM Employee
ORDER BY Salary DESC;
  • `TOP (n)` / `TOP (n) PERCENT`:括號為佳實踐 (支援變數/運算式)。
  • `WITH TIES`:包含並列值,**必須搭配 `ORDER BY`**。
  • **不支援 `OFFSET`** (跳過前 N 筆) —— 分頁請用標準 `OFFSET/FETCH` (SQL 2012+)。

標準分頁語法 (SQL Server 2012+)

SELECT EmpName, Salary
FROM Employee
ORDER BY Salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- 第 3 頁,每頁 10 筆

四、 集合運算:UNION / EXCEPT / INTERSECT

運算子語義重複值處理關鍵限制
UNION聯集 (A ∪ B)預設去重 (`DISTINCT`)所有查詢:
1. 欄位數相同
2. 對應位置資料型別相容
3. 欄位順序對應 (非名稱)
UNION ALL聯集 (保留重複)**不去重,效能高得多**
EXCEPT差集 (A - B)去重
INTERSECT交集 (A ∩ B)去重

排序:僅允許在**最後一個查詢**後加 `ORDER BY`,作用於最終結果集。結果集欄位名稱取自**第一個查詢**。

五、 總結:查詢模式選擇決策樹

  1. **單表過濾/聚合** → 直查。
  2. **多表關聯** → 優先 `ANSI JOIN`;需保留無匹配行 → `LEFT/RIGHT/FULL JOIN`。
  3. **「是否存在」邏輯** → `EXISTS`/`NOT EXISTS`。
  4. **需計算聚合後再關聯** → `CTE` / 派生表 / 暫存表。
  5. **逐行動態計算** → 相關子查詢 (小資料) / `APPLY` / 窗口函數 (大資料)。
  6. **合併異構查詢結果** → `UNION ALL` (已知無重複/允許重複) / `UNION` (需去重)。
  7. **分頁** → `OFFSET/FETCH` (2012+) / `ROW_NUMBER()` (舊版)。

善用執行計畫 (`Ctrl+M`)、`SET STATISTICS IO/TIME ON`、`Query Store` 驗證改寫效果,才是 SQL 高手的必經之路。

copyright © 萬盛學電腦網 all rights reserved