| 類型 | 關鍵字 | 結果集特徵 | 典型場景 |
|---|---|---|---|
| 內連接 | INNER JOIN | 僅保留兩表都滿足條件的行 | 主表-明細表、關聯查詢 |
| 左外連接 | LEFT [OUTER] JOIN | 左表全保留,右表無匹配填 NULL | 主表帶出從屬資訊(如用戶+訂單) |
| 右外連接 | RIGHT [OUTER] JOIN | 右表全保留,左表無匹配填 NULL | 較少用,可改 LEFT JOIN 交換表順序 |
| 全外連接 | FULL [OUTER] JOIN | 兩表全保留,無匹配側填 NULL | 雙向對帳、差異比對 |
| 交叉連接 | CROSS JOIN | 笛卡爾積:M×N 行 | 生成測試資料、排程組合 |
| 自我連接 | JOIN 同表 | 表與自己關聯(需別名) | 遞迴階層(員工-主管、分類樹) |
-- 顯式(推薦)
SELECT i.inst_name, t.teller_name
FROM t_institution i
INNER JOIN t_teller t ON i.inst_no = t.inst_no
WHERE i.inst_no = '5801';
-- 隱式(舊語法,不推薦)
SELECT i.inst_name, t.teller_name
FROM t_institution i, t_teller t
WHERE i.inst_no = t.inst_no AND i.inst_no = '5801';
-- 條件在 ON:左表全保留,右表依條件關聯
SELECT * FROM t_institution i
LEFT JOIN t_teller t ON i.inst_no = t.inst_no AND i.inst_no = '5801';
/* 結果:5801 兩行有數據,5802/5803 右表全 NULL */
-- 條件在 WHERE:先全關聯,再過濾 → 相當於內連接
SELECT * FROM t_institution i
LEFT JOIN t_teller t ON i.inst_no = t.inst_no
WHERE i.inst_no = '5801';
/* 結果:僅 5801 兩行 */
SELECT * FROM t_institution i
RIGHT JOIN t_teller t ON i.inst_no = t.inst_no; -- 右表全保留
SELECT * FROM t_institution i
FULL JOIN t_teller t ON i.inst_no = t.inst_no; -- 兩表全保留
/* MySQL 不支援 FULL JOIN,改用 UNION 模擬:
SELECT ... LEFT JOIN ... UNION SELECT ... RIGHT JOIN ... */
-- 顯式
SELECT o.id, o.order_number, c.id, c.name
FROM orders o CROSS JOIN customers c
WHERE o.id = 1;
-- 隱式(舊語法)
SELECT o.id, o.order_number, c.id, c.name
FROM orders o, customers c
WHERE o.id = 1; -- 產生笛卡爾積再過濾,效能極差
-- 查詢機構及其上級機構
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;
/* 結果:
superior_inst sup_inst_name inst_no inst_name
800 廣州市 5801 天河區
800 廣州市 5802 越秀區
800 廣州市 5803 白雲區 */
INNER JOIN,結果集最小、最快。CROSS JOIN 後再 WHERE 過濾,改用 INNER JOIN。LATERAL JOIN / CTE 遞迴,取代傳統自我連接寫遞迴查詢。掌握 5 種 JOIN 語義與 ON/WHERE 位置規則,是寫出正確、高效 SQL 的基石。內連接最常用,左連接次之,全連接/交叉連接/自我連接各有專用場景。MySQL 缺 FULL JOIN 可用 UNION 模擬。