萬盛學電腦網

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

SQL JOIN 完全圖解:內連接、左/右/全外連接、交叉連接、自我連接差異與實例

連接類型總覽

類型關鍵字結果集特徵典型場景
內連接INNER JOIN僅保留兩表都滿足條件的行主表-明細表、關聯查詢
左外連接LEFT [OUTER] JOIN左表全保留,右表無匹配填 NULL主表帶出從屬資訊(如用戶+訂單)
右外連接RIGHT [OUTER] JOIN右表全保留,左表無匹配填 NULL較少用,可改 LEFT JOIN 交換表順序
全外連接FULL [OUTER] JOIN兩表全保留,無匹配側填 NULL雙向對帳、差異比對
交叉連接CROSS JOIN笛卡爾積:M×N 行生成測試資料、排程組合
自我連接JOIN 同表表與自己關聯(需別名)遞迴階層(員工-主管、分類樹)

內連接(INNER 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';

左外連接(LEFT JOIN)—— 重點掌握 ON vs WHERE

-- 條件在 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 兩行 */

右外連接(RIGHT JOIN)

SELECT * FROM t_institution i
RIGHT JOIN t_teller t ON i.inst_no = t.inst_no;  -- 右表全保留

全外連接(FULL JOIN)

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 ... */

交叉連接(CROSS 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,結果集最小、最快。
  • 外連接時,右表過濾條件寫在 ON,左表過濾寫在 WHERE。
  • 避免 CROSS JOIN 後再 WHERE 過濾,改用 INNER JOIN。
  • MySQL 8.0+ 支援 LATERAL JOIN / CTE 遞迴,取代傳統自我連接寫遞迴查詢。

總結

掌握 5 種 JOIN 語義與 ON/WHERE 位置規則,是寫出正確、高效 SQL 的基石。內連接最常用,左連接次之,全連接/交叉連接/自我連接各有專用場景。MySQL 缺 FULL JOIN 可用 UNION 模擬。

copyright © 萬盛學電腦網 all rights reserved