SQL JOIN完全指南：內連接、左/右/全外連接、交叉連接、自我連接深度解析

JOIN是多表查詢的核心。本文系統講解五大連接類型的語法、語義、結果集差異、ON/WHERE條件位置陷阱，並以機構/出納員實戰案例演示，附自我連接遞歸查詢模式。

連接類型總覽

類型	關鍵字	結果集特徵	典型場景
內連接	INNER JOIN	僅返回雙表匹配行	主表-明細表、關聯查詢、維度退化
左外連接	LEFT [OUTER] JOIN	左表全保留，右表匹配顯示/NULL填充	主表帶明細報表、用戶帶訂單、保留無訂單用戶
右外連接	RIGHT [OUTER] JOIN	右表全保留，左表匹配顯示/NULL填充	同上，表順序反轉
全外連接	FULL [OUTER] JOIN	雙表全保留，不匹配雙向NULL填充	雙向差異對賬、全量合併視圖
交叉連接	CROSS JOIN	笛卡爾積：M×N行	生成測試數據、排程矩陣、全排列

實戰案例：機構表與出納員表

-- 表 t_institution (inst_no, inst_name, superior_inst)
-- 表 t_teller (teller_no, teller_name, inst_no)

1. 內連接（最常用）

SELECT * 
FROM t_institution i
INNER JOIN t_teller t ON i.inst_no = t.inst_no
WHERE i.inst_no = '5801';
-- 等價舊語法：FROM t_institution i, t_teller t WHERE i.inst_no = t.inst_no AND i.inst_no = '5801'

注意：INNER可省略；同名欄位需加表前綴；長表名建議別名。

2. 左外連接：ON vs WHERE 關鍵差異

條件在ON中（右表過濾不影響左表行數）：

SELECT * FROM t_institution i
LEFT OUTER JOIN t_teller t ON i.inst_no = t.inst_no AND i.inst_no = '5801';

結果：機構5801顯示兩出納員；5802、5803保留，出納員欄位為NULL

條件在WHERE中（JOIN後過濾，實際轉為內連接）：

SELECT * FROM t_institution i
LEFT OUTER JOIN t_teller t ON i.inst_no = t.inst_no
WHERE i.inst_no = '5801';

結果：僅機構5801及其出納員（5802/5803被過濾）

鐵律：LEFT JOIN中，右表過濾條件放ON，左表過濾條件放WHERE。右表條件放WHERE會過濾掉左表無匹配行，違背外連接初衷。

3. 右外連接、全外連接

語法對稱，RIGHT JOIN保留右表，FULL JOIN = LEFT JOIN UNION RIGHT JOIN（去重）。MySQL不支援FULL JOIN，需用UNION模擬。

4. 交叉連接（笛卡爾積）

SELECT * FROM t_institution CROSS JOIN t_teller;
-- 隱式：FROM t_institution, t_teller (無WHERE)

警示：無條件交叉連接極其危險，1萬×1萬=1億行，直接拖垮資料庫。

5. 自我連接（遞歸關係）

SELECT s.inst_no AS superior_inst, s.inst_name AS sup_inst_name,
       i.inst_no, i.inst_name
FROM t_institution i
JOIN t_institution s ON i.superior_inst = s.inst_no;

結果：每行顯示機構及其上級機構名（廣州市→天河/越秀/白雲區）

多表連接最佳實踐

SELECT T1.C1, T2.CX, T3.CY
FROM TAB1 T1
INNER JOIN TAB2 T2 ON T1.C1 = T2.C2
INNER JOIN TAB3 T3 ON T1.C1 = T3.C3
LEFT OUTER JOIN TAB4 T4 ON T2.C2 = T4.C3
WHERE T1.X > T3.Y;

原則：主表駐左，維度表駐右；內連接在前，外連接在後；ON只寫關聯條件，WHERE寫業務過濾。