您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 進階連接查詢完全指南：表別名、內連接、自連接、自然連接、外連接實戰詳解

編輯：MySQL 綜合教學

MySQL 進階連接查詢完全指南：表別名、內連接、自連接、自然連接、外連接實戰詳解

多表關聯查詢是關係型數據庫的核心能力。MySQL 支持 INNER JOIN、LEFT/RIGHT OUTER JOIN、SELF JOIN、NATURAL JOIN 等多種連接模式，配合表別名簡化複雜查詢。本文系統講解語法、語義、適用場景與性能考量。

一、表別名：簡化與消除歧義

-- 列別名
SELECT cust_name, UPPER(cust_name) AS upper_name FROM customers;

-- 表別名 (AS 可省略)
SELECT c.cust_name, o.order_num 
FROM customers AS c 
INNER JOIN orders AS o ON c.cust_id = o.cust_id;

兩大作用：

縮短 SQL：長表名簡寫為 1-2 字母
消除歧義：同表多次出現 (自連接) 或多表同名列時，必須用別名限定

二、內連接：等值連接的標準形式

-- 顯式內連接 (推薦)
SELECT c.cust_name, o.order_num
FROM customers c
INNER JOIN orders o ON c.cust_id = o.cust_id;

-- 等價隱式內連接 (舊語法，不推薦)
SELECT c.cust_name, o.order_num
FROM customers c, orders o
WHERE c.cust_id = o.cust_id;

只返回兩表關聯條件匹配的行
ON 子句放關聯條件，WHERE 放過濾條件，語義清晰
多表連接：FROM a JOIN b ON ... JOIN c ON ...，優化器自動選擇驅動表順序

三、自連接：表與自身關聯

場景：層級結構 (員工-經理)、同表不同維度對比。

-- 查詢數學滿分學生的英語成績
SELECT s1.name AS student, s1.math, s2.english
FROM scores s1
INNER JOIN scores s2 ON s1.student_id = s2.student_id
WHERE s1.subject = '數學' AND s1.score = 100
  AND s2.subject = '英語';

關鍵點：必須給同一表起不同別名 (s1, s2)，所有列引用必須帶別名前綴，否則報錯「Column 'name' in field list is ambiguous」。

四、自然連接：自動匹配同名列

-- 自然連接：自動用所有同名列做關聯條件
SELECT * FROM customers NATURAL JOIN orders;
-- 等價於
SELECT * FROM customers c JOIN orders o 
  ON c.cust_id = o.cust_id 
  AND c.cust_name = o.cust_name  -- 若 orders 也有 cust_name 會誤加入
  ...;

⚠️ 高風險：

自動匹配所有同名列，含無關同名列 (如 created_at) 導致語義錯誤
不可控、不可讀、難維護，**生產環境嚴禁使用**
標準做法：顯式 ON 子句指定關聯列

五、外連接：保留無匹配行

場景：查所有客戶及其訂單，**含無訂單客戶**。

左外連接 (LEFT OUTER JOIN)

SELECT c.cust_id, c.cust_name, o.order_num
FROM customers c
LEFT OUTER JOIN orders o ON c.cust_id = o.cust_id;
-- 結果：所有 customers 行，無匹配訂單時 orders 列為 NULL

右外連接 (RIGHT OUTER JOIN)

SELECT c.cust_id, c.cust_name, o.order_num
FROM customers c
RIGHT OUTER JOIN orders o ON c.cust_id = o.cust_id;
-- 結果：所有 orders 行，無匹配客戶時 customers 列為 NULL

左右外連接等價轉換

-- 以下兩條語義完全等價
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;
SELECT * FROM b RIGHT JOIN a ON a.id = b.a_id;

習慣統一用 LEFT JOIN，從主表 (駕駛表) 出發向右擴展，符合閱讀直覺。

多表外連接

SELECT c.cust_name, o.order_num, oi.prod_id
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
LEFT JOIN orderitems oi ON o.order_num = oi.order_num;
-- 客戶→訂單→訂單項，任一層無匹配後續列均為 NULL

六、連接查詢性能與最佳實踐

原則	說明
小表驅動大表	驅動表 (外層循環) 行數少 → 總關聯次數少。優化器通常自動選擇，可用 STRAIGHT_JOIN 強制
關聯列建索引	被驅動表關聯列必須有索引，否則每次掃描全表 (Type=ALL)，性能災難
僅選需要列	避免 SELECT *，減少回表、網絡、排序開銷
WHERE 過濾前置	駕駛表過濾條件盡早應用，減少參與關聯行數
避免外連接嵌套過深	>3 表外連接優化器壓力大，考慮拆分為多查詢應用層組裝
外連接條件放 ON 不放 WHERE	WHERE 會將外連接隱式轉為內連接 (NULL 值被過濾)

七、總結對照表

連接類型	關鍵字	結果集特徵	典型場景
內連接	INNER JOIN / JOIN	僅匹配行	主流業務關聯 (訂單-用戶、商品-分類)
左外連接	LEFT OUTER JOIN	左表全量 + 右表匹配/NULL	報表統計 (所有用戶含無訂單)、主從表展示
右外連接	RIGHT OUTER JOIN	右表全量 + 左表匹配/NULL	極少用，轉為 LEFT JOIN
自連接	表別名 + INNER/LEFT JOIN	同表不同維度關聯	組織架構、同表對比、遞歸層級
自然連接	NATURAL JOIN	自動同名列關聯	禁用，不可控風險極高