SQL JOIN 教學:INNER、LEFT、RIGHT、FULL JOIN 差異與範例一次看懂

SQL JOIN 是把兩張以上的資料表,依照彼此相關的欄位接起來一起查詢的語法。像是「列出每位客戶和他的訂單」「找出還沒下過單的客戶」,都要靠 JOIN 才查得出來。

這篇 SQL JOIN 教學用同一組範例資料,一次比較 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN、CROSS JOIN 和 NATURAL JOIN 六種寫法,每一種都附上 SQL 與實際查詢結果,最後整理各資料庫的支援差異和常見錯誤。

範例資料表

以下所有範例都用這兩張表。客戶資料表 customers:

C_IdNameCity
1張一台北市
2王二新竹縣
3李三高雄縣

訂單資料表 orders,其中 C_Id 對應到 customers.C_Id:

O_IdOrder_NoC_Id
125723
273753
375201
410541
512575

請特別注意兩個地方,等一下各種 JOIN 的差別就出在這裡:

  • 王二(C_Id = 2)沒有任何訂單。
  • 第 5 筆訂單的 C_Id 是 5,但客戶表裡沒有 5 號客戶,是一筆「找不到客戶」的訂單。

想自己動手跑的話,可以用下面這段建立範例資料(MySQL、PostgreSQL、SQLite 都能用):

SQL
CREATE TABLE customers (
  C_Id INT PRIMARY KEY,
  Name VARCHAR(20),
  City VARCHAR(20)
);

CREATE TABLE orders (
  O_Id INT PRIMARY KEY,
  Order_No INT,
  C_Id INT
);

INSERT INTO customers VALUES
  (1, '張一', '台北市'),
  (2, '王二', '新竹縣'),
  (3, '李三', '高雄縣');

INSERT INTO orders VALUES
  (1, 2572, 3),
  (2, 7375, 3),
  (3, 7520, 1),
  (4, 1054, 1),
  (5, 1257, 5);

SQL JOIN 有哪幾種?一張表看懂

JOIN 種類回傳哪些資料常見用途
INNER JOIN只回傳兩邊都對得上的資料列出有下單的客戶與訂單
LEFT JOIN左表全部+右表對得上的,對不上補 NULL列出所有客戶,含沒下單的
RIGHT JOIN右表全部+左表對得上的,對不上補 NULL列出所有訂單,含找不到客戶的
FULL JOIN兩邊全部,對不上的那邊補 NULL對帳、找出兩邊不一致的資料
CROSS JOIN兩表所有可能的組合(笛卡兒乘積)產生所有組合,例如尺寸 × 顏色
NATURAL JOIN自動用兩表同名欄位當連接條件欄位命名很一致時的簡寫(不建議)
SQL JOIN 種類文氏圖:INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN
四種常用 JOIN 的範圍:紫色是兩邊對得上的資料

INNER JOIN:只取兩邊都對得上的資料

INNER JOIN(內部連接)只回傳兩張表都符合連接條件的資料,對不上的一律不出現。INNER 可以省略,直接寫 JOIN 就是 INNER JOIN。

SQL
SELECT customers.Name, orders.Order_No
FROM customers
INNER JOIN orders
  ON customers.C_Id = orders.C_Id
ORDER BY orders.O_Id;
NameOrder_No
李三2572
李三7375
張一7520
張一1054
INNER JOIN 查詢結果

沒有訂單的王二、找不到客戶的 1257 號訂單,都不在結果裡。

LEFT JOIN:保留左表全部資料

LEFT JOIN(左外部連接,也寫作 LEFT OUTER JOIN)會保留 FROM 後面那張表(左表)的每一筆資料;右表對不上的欄位會填 NULL。

SQL
SELECT customers.Name, orders.Order_No
FROM customers
LEFT JOIN orders
  ON customers.C_Id = orders.C_Id
ORDER BY customers.C_Id, orders.O_Id;
NameOrder_No
張一7520
張一1054
王二NULL
李三2572
李三7375
LEFT JOIN 查詢結果

實用技巧:找出沒有對應資料的那一筆

LEFT JOIN 再加上 WHERE 右表欄位 IS NULL,就能找出「沒有下過單的客戶」:

SQL
SELECT customers.Name
FROM customers
LEFT JOIN orders
  ON customers.C_Id = orders.C_Id
WHERE orders.O_Id IS NULL;
Name
王二
沒有訂單的客戶

RIGHT JOIN:保留右表全部資料

RIGHT JOIN(右外部連接,也寫作 RIGHT OUTER JOIN)和 LEFT JOIN 相反,保留 JOIN 後面那張表(右表)的每一筆資料。

SQL
SELECT customers.Name, orders.Order_No
FROM customers
RIGHT JOIN orders
  ON customers.C_Id = orders.C_Id
ORDER BY orders.O_Id;
NameOrder_No
李三2572
李三7375
張一7520
張一1054
NULL1257
RIGHT JOIN 查詢結果

1257 號訂單找不到客戶,所以 Name 是 NULL。實務上 RIGHT JOIN 比較少用,因為把兩張表的順序對調、改寫成 LEFT JOIN,結果完全一樣,而且大多數人讀起來比較順。

FULL JOIN:兩邊資料全部保留

FULL JOIN(完整外部連接,也寫作 FULL OUTER JOIN)等於 LEFT JOIN 加上 RIGHT JOIN:兩張表的資料都會出現,對不上的那一邊填 NULL。

SQL
SELECT customers.Name, orders.Order_No
FROM customers
FULL JOIN orders
  ON customers.C_Id = orders.C_Id;
NameOrder_No
張一7520
張一1054
王二NULL
李三2572
李三7375
NULL1257
FULL JOIN 查詢結果(順序依資料庫而定)

MySQL 不支援 FULL JOIN 怎麼辦?

MySQL 沒有 FULL JOIN,可以用 LEFT JOIN 加上「只取右表多出來的部分」,再用 UNION ALL 合併:

SQL
SELECT customers.Name, orders.Order_No
FROM customers
LEFT JOIN orders ON customers.C_Id = orders.C_Id

UNION ALL

SELECT customers.Name, orders.Order_No
FROM customers
RIGHT JOIN orders ON customers.C_Id = orders.C_Id
WHERE customers.C_Id IS NULL;

第二段加上 WHERE customers.C_Id IS NULL,只補上「找不到客戶的訂單」,避免兩邊都對得上的資料重複出現。網路上常見直接用 UNION 合併 LEFT JOIN 和 RIGHT JOIN 的寫法,在多數情況也能用,但如果結果本來就有完全相同的兩列,UNION 會把它們合併成一列。

CROSS JOIN:產生所有組合

CROSS JOIN(交叉連接)不需要連接條件,會把左表每一筆和右表每一筆兩兩配對,也就是笛卡兒乘積(Cartesian product)。

SQL
SELECT customers.Name, orders.Order_No
FROM customers
CROSS JOIN orders;

範例中客戶 3 筆、訂單 5 筆,結果會有 3 × 5 = 15 筆。前 5 筆是張一配上每一張訂單:

NameOrder_No
張一2572
張一7375
張一7520
張一1054
張一1257
……
CROSS JOIN 查詢結果(共 15 筆,僅列前 5 筆;實際排列順序依資料庫而定)

CROSS JOIN 適合用在「本來就要所有組合」的情境,例如把所有尺寸和所有顏色組成商品規格。兩張大表做 CROSS JOIN 筆數會暴增,使用前先算一下結果有多少筆。

NATURAL JOIN:自動用同名欄位連接

NATURAL JOIN(自然連接)不寫 ON,資料庫會自動拿兩張表所有同名的欄位當連接條件。範例中兩張表只有 C_Id 同名,所以結果和 INNER JOIN 一樣:

SQL
SELECT Name, Order_No
FROM customers
NATURAL JOIN orders;

另外也有 NATURAL LEFT JOIN、NATURAL RIGHT JOIN,規則相同,只是改成外部連接。

不建議在正式程式中使用 NATURAL JOIN。它的連接條件是「看欄位名稱」決定的,哪天有人在兩張表都加了 created_at 或 status 這種同名欄位,連接條件就會悄悄改變,查詢結果跟著出錯,而且很難發現。明確寫出 ON 或 USING 比較安全。

ON 和 USING 的差別

連接條件有兩種寫法。ON 可以寫任何條件;如果兩邊欄位同名,也可以用比較短的 USING:

SQL
-- 用 ON
SELECT customers.Name, orders.Order_No
FROM customers
JOIN orders ON customers.C_Id = orders.C_Id;

-- 用 USING(兩邊欄位都叫 C_Id)
SELECT Name, Order_No
FROM customers
JOIN orders USING (C_Id);

兩種寫法結果相同。差別在於 USING 只能用在同名欄位,而且 SQL Server 不支援;欄位名稱不同、或要加其他條件時,就用 ON。

多張表 JOIN

JOIN 可以一直接下去。例如再加一張訂單明細表 order_items,就能一次查出「客戶、訂單、商品」:

SQL
SELECT c.Name, o.Order_No, i.Product, i.Qty
FROM customers AS c
JOIN orders AS o      ON c.C_Id = o.C_Id
JOIN order_items AS i ON o.O_Id = i.O_Id;

表名很長時,可以像上面一樣用 AS 取別名(c、o、i),SQL 會短很多。別名的用法可以參考 SQL AS 別名。

各資料庫支援差異

語法MySQLPostgreSQLSQL ServerOracleSQLite
INNER/LEFT JOIN✓✓✓✓✓
RIGHT JOIN✓✓✓✓3.39 以上
FULL JOIN✗✓✓✓3.39 以上
CROSS JOIN✓✓✓✓✓
NATURAL JOIN✓✓✗✓✓
USING✓✓✗✓✓

常見錯誤

LEFT JOIN 後在 WHERE 篩選右表,結果變成 INNER JOIN

想列出所有客戶,並只顯示金額大於 5000 的訂單編號。如果把條件寫在 WHERE:

SQL
SELECT customers.Name, orders.Order_No
FROM customers
LEFT JOIN orders ON customers.C_Id = orders.C_Id
WHERE orders.Order_No > 5000;
NameOrder_No
張一7520
李三7375
條件寫在 WHERE:王二不見了

王二的 Order_No 是 NULL,NULL > 5000 不成立,整列被 WHERE 濾掉,LEFT JOIN 的效果就沒了。要保留所有客戶,條件應該寫在 ON:

SQL
SELECT customers.Name, orders.Order_No
FROM customers
LEFT JOIN orders
  ON customers.C_Id = orders.C_Id
 AND orders.Order_No > 5000
ORDER BY customers.C_Id;
NameOrder_No
張一7520
王二NULL
李三7375
條件寫在 ON:所有客戶都保留

NULL 的比較規則可以參考 SQL NULL 值處理。

忘了寫連接條件,資料暴增

FROM customers, orders 這種舊式寫法如果漏了 WHERE customers.C_Id = orders.C_Id,結果就等於 CROSS JOIN。看到查詢結果筆數多得不合理,先檢查連接條件。

欄位名稱不明確(ambiguous)

兩張表都有 C_Id 時,SELECT C_Id 會出現「Column ‘C_Id’ in field list is ambiguous」之類的錯誤。在欄位前加上表名或別名,例如 customers.C_Id,就能解決。

總結

  • 只要兩邊都對得上的資料,用 INNER JOIN。
  • 要保留某一邊全部資料,用 LEFT JOIN(RIGHT JOIN 可以改寫成 LEFT JOIN)。
  • 兩邊都要保留、做對帳,用 FULL JOIN;MySQL 用 LEFT JOIN + UNION ALL 代替。
  • 要所有組合才用 CROSS JOIN。
  • NATURAL JOIN 了解就好,正式程式寫明 ON 比較安全。

延伸閱讀:SQL SELECT 查詢資料、SQL WHERE 條件篩選、SQL UNION 聯集、SQL Subquery 子查詢、SQL 聚合函數,或回到 SQL 教學目錄。

留言功能已關閉。