SQL Server 進階查詢：子查詢分類與表連接（交叉/內/外連接）完整指南

子查詢與表連接是 T-SQL 多表查詢的兩大支柱。掌握子查詢分類（獨立/相關）、連接類型（交叉/內/左/右/全外連接）及其執行邏輯，是寫出高效、正確 SQL 的前提。

一、子查詢分類

1. 獨立子查詢

內部查詢**不依賴**外部查詢，可單獨執行，結果傳遞給外部。

• 標量子查詢：返回單值，用於 =, >, <, >=, <=, <> 比較。

  SELECT * FROM Score 
  WHERE StuId = (SELECT StuId FROM Student WHERE StuName = N'Kencery');

• 多值子查詢：返回單列多行，用於 IN / NOT IN / ANY / ALL。

  SELECT * FROM Score 
  WHERE StuId IN (SELECT StuId FROM Student WHERE StuName = N'Kencery');

2. 相關子查詢

內部查詢**引用外部查詢的列**，逐行執行，邏輯上等價於連接但語義不同。

SELECT S.StuName, S.StuAge
FROM Student S
WHERE EXISTS (
    SELECT 1 FROM Score SC 
    WHERE SC.StuId = S.StuId AND SC.TestBase > 90
);

子查詢寫作規範

• 用括號包裹：(SELECT ...)。
• 必要時為表起別名：Table AS Alias。
• 避免在高併發 OLTP 中大量使用相關子查詢，優先改寫為 JOIN 或 APPLY。

二、表連接類型詳解

1. 交叉連接

笛卡爾積：表 A 每行 × 表 B 每行。無 ON 子句。

SELECT * FROM TestNum1 CROSS JOIN TestNum2;
-- 等價舊語法 (不推薦)
SELECT * FROM TestNum1, TestNum2;

自交叉連接：表與自身連接，需別名區分。

SELECT T1.Num1, T2.Num2 FROM TestNum1 AS T1 CROSS JOIN TestNum1 AS T2;

2. 內連接

僅返回**滿足 ON 條件**的匹配行。最常用、效能最好（優化器選擇空間大）。

SELECT S.StuId, S.StuName, SC.TestBase, SC.TestBeyond
FROM Student AS S
INNER JOIN Score AS SC ON S.StuId = SC.StuId
WHERE S.StuIsDel = 0;

3. 外連接

在內連接基礎上，**保留主表所有行**，從表無匹配則填 NULL。

外連接實戰：顯示無學生的課程

-- 建表
CREATE TABLE TblMain (Id INT, Name NVARCHAR(20), Fid INT);
CREATE TABLE TblOther (Id INT, Name NVARCHAR(20));
INSERT INTO TblMain VALUES (1,N'張三',1),(2,N'李四',2);
INSERT INTO TblOther VALUES (1,N'C++'),(2,N'.NET'),(3,N'Java');

-- 內連接：Java 不顯示
SELECT * FROM TblMain T1 INNER JOIN TblOther T2 ON T1.Fid = T2.Id;

-- 右外連接：保留 TblOther 所有行，Java 顯示
SELECT * FROM TblMain T1 RIGHT JOIN TblOther T2 ON T1.Fid = T2.Id;
-- 結果：Java 行 T1 列為 NULL

連接效能與選型指南

最佳實踐清單

1. **顯式 JOIN 語法** (ANSI SQL-92)，棄用舊式逗號/WHERE 連接。
2. **別名簡短有意義**：Student S, Score SC。
3. **ON 條件只放連接邏輯**，業務過濾放 WHERE。
4. **外連接謹慎用 WHERE 過濾從表列** (會隱式轉為內連接)，改放 ON 或子查詢。
5. 大表連接確保 **連接列有索引**，並考慮統計信息更新。