別再「不確定就用 LEFT JOIN」了

SQL 的 JOIN 語法雖然簡單,但選錯類型會讓資料出現重複或消失,是個容易踩雷的功能。很多人靠「不確定就用 LEFT JOIN」撐過去,但理解各種 JOIN 的真正差異,能省下之後追查異常列數的時間。

這篇文章是以各 JOIN 類型的對照表與結果列數解說為核心的參考資料,最後也會介紹多資料表 JOIN 的寫法,以及如何依 JOIN 類型重新檢視既有的 SQL。

JOIN 的基本語法與用法

JOIN 是以共同欄位把多個資料表連起來,合成一份結果的語法。基本形式如下。

SELECT 欄位名稱
FROM 資料表A
INNER JOIN 資料表B ON 資料表A.鍵欄位 = 資料表B.鍵欄位;
  • 寫在 FROM 後面的是左資料表(起點),寫在 JOIN 後面的是右資料表
  • ON 寫的是「哪些欄位互相對應」(多半是主鍵與外鍵)
  • 資料表名稱很長時可以加上別名:FROM users u INNER JOIN orders o ON u.id = o.user_id
  • 兩個資料表有相同欄位名稱時,要像 users.id 這樣加上資料表名稱(別名)。不加會因為不明確而出錯
  • 只寫 JOIN 時,會被視為 INNER JOIN

JOIN 的名稱:英文名稱與中文名稱

英文名稱 中文名稱 補充
INNER JOIN 內部連接 只寫 JOIN 也相同
LEFT JOIN 左外部連接 與 LEFT OUTER JOIN 相同(OUTER 可省略)
RIGHT JOIN 右外部連接 與 RIGHT OUTER JOIN 相同
FULL JOIN 完整外部連接 與 FULL OUTER JOIN 相同
CROSS JOIN 交叉連接 不帶條件,產生所有組合(笛卡兒積)
SELF JOIN 自我連接 以別名把同一個資料表用兩次

內部連接是「只留兩邊都有對應的」,外部連接是「保留某一邊(或兩邊)的全部」,這是基本的差別。

拿不定主意時如何選擇

  • 只想要兩個資料表都有對應的列 → INNER JOIN
  • 想保留左資料表的全部(右邊沒有就是 NULL) → LEFT JOIN
  • 想要兩個資料表的全部 → FULL JOIN
  • 想產生所有組合 → CROSS JOIN

JOIN 種類對照表

以 users(3 筆:Alice、Bob、Carol)和 orders(Alice 2 筆、Bob 1 筆、Carol 0 筆)為例,比較各 JOIN 傳回的結果。

JOIN 種類 傳回的列 Alice 與 Bob 的列數 Carol(無訂單)的處理 僅訂單存在的列
INNER JOIN 僅兩邊都有對應的列 Alice×2, Bob×1 不包含 不包含
LEFT JOIN 左表全部 + 對應的右表 Alice×2, Bob×1 Carol 的 1 列(右側為 NULL) 不包含
RIGHT JOIN 右表全部 + 對應的左表 Alice×2, Bob×1 不包含 若存在則包含(左側為 NULL)
FULL JOIN 兩表全部的列 Alice×2, Bob×1 Carol 的 1 列 若存在則包含
CROSS JOIN 所有組合(笛卡兒積) 3 人 × 3 筆 = 9 列 — —

只有 INNER JOIN 是「篩選」性質,其他所有類型都是朝「保證單邊或雙邊全部列都存在」的方向運作,這個差異正是列數波動的根本原因。

為什麼結果列數會「變多」

這是最容易出包的地方。「JOIN 之後列數比預期多」,十次有九次是因為一對多關係被如實 JOIN 出來的結果。

SELECT users.name, orders.id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

即使 users 有 3 筆、orders 有 3 筆,結果的列數會等於 orders 表貢獻的筆數(如果 Alice 有 2 筆訂單,Alice 的列也會出現 2 次)。在做彙總時忽略這點,直接用 COUNT(*),算出來的就不是使用者數,而是訂單數。

-- 錯誤:原本想算使用者數,結果算成「使用者 × 訂單」的列數
SELECT COUNT(*) FROM users INNER JOIN orders ON users.id = orders.user_id;

-- 正確:去除重複後的使用者數
SELECT COUNT(DISTINCT users.id) FROM users INNER JOIN orders ON users.id = orders.user_id;

隨時掌握「JOIN 之前有幾筆」,如果 JOIN 之後的列數不是那個數字的整數倍,就該懷疑是不是 JOIN 條件出了問題——這是實務上的鐵則。

LEFT JOIN 最常踩的坑:WHERE 子句讓 JOIN 失效

明明是為了「想連沒有訂單的使用者也一併列出」才改用 LEFT JOIN,結果一寫 WHERE 條件,馬上又變回跟 INNER JOIN 一樣的結果——這是第二常見的事故。

-- 陷阱:表面上是 LEFT JOIN,但在 WHERE 篩選 orders 會讓 NULL 列消失
SELECT users.name, orders.status
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.status = 'completed';   -- Carol(NULL)在這裡被排除

Carol 沒有訂單,所以 orders.status 是 NULL。WHERE orders.status = 'completed' 會去判斷 NULL = 'completed',而在 SQL 中,任何與 NULL 的比較結果永遠是 NULL(既非真也非假),因此該列會被排除在結果之外。最終效果實質上等同於 INNER JOIN。

解法是把條件寫進 ON 子句:

-- 正確:把條件放進 ON 子句,就能保留 LEFT JOIN 的語意
SELECT users.name, orders.status
FROM users
LEFT JOIN orders ON users.id = orders.user_id AND orders.status = 'completed';
-- Carol 的列會保留,orders 那側全部是 NULL

只要記住:想保證左表全部列都在,篩選右側就寫在 ON 子句;篩選左側才寫在 WHERE 子句,就不會搞混了。

多資料表 JOIN:固定一個基準點

要 JOIN 三張以上的資料表而感到困惑時,訣竅是先決定「以哪張表為主詞」。

SELECT
  users.name,
  orders.id       AS order_id,
  order_items.quantity,
  products.name   AS product_name
FROM users
LEFT JOIN orders      ON users.id = orders.user_id
LEFT JOIN order_items ON orders.id = order_items.order_id
LEFT JOIN products    ON order_items.product_id = products.id
WHERE users.deleted_at IS NULL;

以 FROM users 為起點,依序 串接 LEFT JOIN 到訂單、訂單明細、商品。因為全程都以 users 為主詞,「沒有訂單的使用者」和「有訂單但明細資料異常」都能一併找出來(如果中途混入 INNER JOIN,就會在那個節點產生篩選,要特別留意)。

SELF JOIN:用「別名」把一張表當成兩張表

同一張表自己跟自己 JOIN 稱為 SELF JOIN,重點在於用別名(alias)把它當成兩張不同的表來處理。典型例子是「員工與其主管」這種自我參照的結構。

SELECT
  emp.name  AS employee_name,
  mgr.name  AS manager_name
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.id;

為 employees 分別取別名 emp 和 mgr,SQL 上就能寫成「兩張表的 JOIN」。因為通常也想包含沒有主管(manager_id 為 NULL)的員工,所以這裡同樣是 LEFT JOIN 比較自然。

逗號分隔、USING 與 NATURAL JOIN

在舊的程式碼中,可以看到不使用 JOIN,而是用逗號並列資料表,再以 WHERE 撰寫連接條件的寫法。

-- 舊的寫法(結果與 INNER JOIN 相同)
SELECT users.name, orders.id
FROM users, orders
WHERE users.id = orders.user_id;

結果與 INNER JOIN 相同,但只要忘了寫 WHERE 條件,就會變成所有組合(笛卡兒積),所以重新撰寫時使用 JOIN ... ON 比較安全。

如果要連接的欄位名稱在兩個資料表中相同,可以用 USING 取代 ON。

SELECT * FROM users INNER JOIN orders USING (user_id);

USING 可用於 MySQL、PostgreSQL、Oracle、SQLite,但 SQL Server 不能使用。NATURAL JOIN(自動連接所有同名欄位)只要新增欄位,結果就會改變,因此實務上還是避開比較穩妥。

各資料庫的差異

資料庫 FULL JOIN 補充
MySQL 不支援 以 UNION 合併 LEFT JOIN 與 RIGHT JOIN 來替代
PostgreSQL 支援
SQL Server 支援 不能用 USING,請寫 ON
SQLite 3.39.0 以後支援 在此之前不能用 RIGHT JOIN / FULL JOIN
Oracle 支援 ANSI 的 JOIN 語法從 9i 起支援。仍留有舊式 (+) 寫法的程式碼

實際使用版本的支援情況,請查閱各資料庫的文件。

組合與美化 JOIN

如果從零開始寫多資料表 JOIN 很麻煩,可以用 Visual SQL Builder 透過表單組合資料表與 JOIN 條件。切換 JOIN 種類(JOIN/LEFT JOIN/RIGHT JOIN/FULL JOIN)並比較產生的 SQL,很適合搭配這篇的對照表一起確認實際行為。

需要讀懂既有的複雜 JOIN 查詢時,用 SQL 格式化工具 整理過後,會更容易看出哪個 JOIN 對應哪張表。這兩個工具都是完全在瀏覽器內執行,資料表名稱與結構資訊不會外傳。

常見問題

內部連接與外部連接有什麼不同?

內部連接(INNER JOIN)只回傳兩個資料表都有對應的列。外部連接(LEFT JOIN、RIGHT JOIN、FULL JOIN)會保留某一邊(或兩邊)資料表的全部,沒有對應的那一邊以 NULL 補上。想把「沒有訂單的使用者也列出來」時用外部連接,「只要有訂單的使用者」則用內部連接。

LEFT JOIN 與 LEFT OUTER JOIN 不一樣嗎?

一樣。OUTER 可以省略,所以一般寫成 LEFT JOIN。RIGHT JOIN 與 FULL JOIN 也相同。

JOIN 與逗號分隔(以 WHERE 連接)哪個比較好?

結果相同,但重新撰寫時建議使用 JOIN ... ON。連接條件(ON)與篩選條件(WHERE)可以分開寫,比較易讀,也能避免因漏寫條件而產生笛卡兒積。此外,外部連接在逗號分隔的寫法中,並沒有標準的寫法。

INNER JOIN 和 JOIN 是不一樣的東西嗎?

是一樣的。在大多數 RDBMS 中,JOIN 就是 INNER JOIN 的簡寫。不過為了在程式碼審查時更清楚,許多團隊仍會習慣明確寫出 INNER JOIN。

聽說 RIGHT JOIN 很少被使用,是真的嗎?

A RIGHT JOIN B 只要把資料表順序互換,就等同於 B LEFT JOIN A 的結果,因此不少團隊會採用「統一使用 LEFT JOIN、不使用 RIGHT JOIN」的程式碼規範。不過閱讀既有查詢時還是會遇到,理解它的意義仍有必要。

聽說有些資料庫不支援 FULL JOIN?

MySQL 長期以來都不直接支援 FULL JOIN(通常以 UNION 合併 LEFT JOIN 與 RIGHT JOIN 來替代)。PostgreSQL 和 SQL Server 則原生支援。使用前請先確認所用資料庫的支援狀況。

貼上 JOIN 條件來確認時,資料會被傳送出去嗎?

不會。Visual SQL Builder 與 SQL 格式化工具 都完全在瀏覽器內執行,輸入的資料表名稱與查詢內容都不會傳送到伺服器。

總結

  • 只有 INNER JOIN 是「篩選」方向;LEFT/RIGHT/FULL 都是保證單邊或雙邊的全部列
  • 一對多的 JOIN 會讓列數增加,彙總前請考慮使用 COUNT(DISTINCT ...)
  • LEFT JOIN 若要篩選右側請寫在 ON 子句,篩選左側則寫在 WHERE 子句;把右側條件寫進 WHERE 會讓 LEFT JOIN 實質上變成 INNER JOIN
  • 多資料表 JOIN 建議固定一張基準表,並從那裡開始串接 LEFT JOIN
  • SELF JOIN 利用別名讓同一張表看起來像兩張表