SQL 聯結類型

本文探討不同類型的 SQL 聯結。如果您是這個主題的新手，您可能需要查看 SQL 聯結文章。請注意，聯結僅適用於關聯式資料庫。

SQL 聯結類型快速回顧

SQL 聯結會告知資料庫合併來自不同表格的欄。我們通常透過比對一個表格中的外來鍵與另一個表格中的主索引鍵來聯結表格。例如，products 表格中的每個記錄在 products.id 欄位中都有一個唯一 ID：這是主索引鍵。為了比對索引鍵，orders 中的每個記錄在 orders.product_id 欄位中都有一個產品 ID：這是外來鍵。如果我們想要將關於訂單的資訊與關於訂購產品的資訊合併，我們可以執行內部聯結

SELECT
  orders.total as total,
  products.title as title
FROM
  orders INNER JOIN products
ON
  orders.product_id = products.id

非常重要的是，我們在聯結中使用 Orders.product_id 而不是 Orders.id：這兩個欄位都只是數字，因此某些訂單 ID 會比對某些產品 ID，但這些比對將毫無意義。

SQL 聯結的問題說明

即使我們使用正確的欄位，這裡對於粗心大意的人來說仍然有一個陷阱。很容易檢查 Orders 中的每個記錄是否都包含產品 ID — Orders.product_id 中空值數量的計數會傳回 0

SELECT
  count(*)
FROM
  orders
WHERE
  orders.product_id IS NULL

| count(*) |
| -------- |
| 0        |

但是如果事情並非總是比對呢？例如，假設我們想要找出哪些產品缺少評論。如果我們查看 reviews 表格，它有 1,112 個條目

SELECT
  count(*)
FROM
  reviews

| count(*) |
| -------- |
| 1112     |

每則評論都指向一個產品

SELECT
  count(*)
FROM
  reviews
WHERE
  reviews.product_id IS NULL

| count(*) |
| -------- |
| 0        |

但是每個產品都有評論嗎？為了找出答案，讓我們計算產品數量

SELECT
  count(*)
FROM
  products

| count(*) |
| -------- |
| 200      |

然後我們可以合併 products 和 reviews 表格，並計算結果中不同產品的數量。（在現實生活中，我們可能會使用 SELECT COUNT(DISTINCT product_id) FROM reviews 來取得這個數字，但使用 INNER JOIN 有助於我們說明這個概念。）

SELECT
  count(distinct products.id)
FROM
  products INNER JOIN reviews
ON
  products.id = reviews.product_id

| count(*) |
| -------- |
| 176      |

在 200 個產品中，只有 176 個產品有任何評論。因此，如果我們計算每個產品的評論數量，我們只會得到一些評論的計數 — 我們的查詢不會告訴我們任何關於缺少評論的產品的資訊，因為在合併表格時，內部聯結找不到任何比對。此查詢示範了這個問題

SELECT
  products.title as title, count(*) as number_of_reviews
FROM
  products INNER JOIN reviews
ON
  products.id = reviews.product_id
GROUP BY
  products.id
ORDER BY
  number_of_reviews ASC

| products.title            | number_of_reviews |
| ------------------------- | ----------------- |
| Rustic Copper Hat         |                 1 |
| Incredible Concrete Watch |                 1 |
| Practical Aluminum Coat   |                 1 |
| Awesome Aluminum Table    |                 1 |
| ...                       |               ... |

我們已依計數以遞增順序排序結果；如此所示，最低計數為 1，但它應該是 0。

外部 SQL 聯結類型來救援

好的：我們知道有多少產品沒有評論，但它們是哪些產品？回答這個問題的一種方法是使用稱為左外部聯結的 SQL 聯結類型，也稱為「左聯結」。這種聯結始終會從我們提及的第一個表格（即左邊的表格）傳回至少一個記錄。若要了解它的運作方式，請想像我們有兩個小表格，分別稱為 paint 和 fabric。paint 表格包含三列

| brand     | color |
| --------- | ----- |
| Premiere  | red   |
| Premiere  | blue  |
| Special   | blue  |

而 fabric 表格僅包含兩列

| kind   | shade |
| ------ | ----- |
| nylon  | green |
| cotton | blue  |

如果我們對這兩個表格執行內部聯結，將 paint.color 比對到 fabric.shade，則只有 blue 記錄會比對

SELECT
  *
FROM
  paint INNER JOIN fabric
ON
  paint.color = fabric.shade

| paint.brand | paint.color | fabric.kind | fabric.shade |
| ----------- | ----------- | ----------- | ------------ |
| Premiere    | blue        | cotton      | blue         |
| Special     | blue        | cotton      | blue         |

fabric 表格中沒有任何紅色，因此 paint 中的第一個記錄未包含在結果中。同樣地，paint 中沒有任何綠色，因此 fabric 中的尼龍材質也會被捨棄。

但是，如果我們執行左外部聯結，則資料庫會保留左表格中缺少比對的每個記錄。由於右表格中沒有比對的值，因此 SQL 會在這些欄中填入 NULL

SELECT
  *
FROM
  paint LEFT JOIN fabric
ON
  paint.color = fabric.shade

| paint.brand | paint.color | fabric.kind | fabric.shade |
| ----------- | ----------- | ----------- | ------------ |
| Premiere    | red         | NULL        | NULL         |
| Premiere    | blue        | cotton      | blue         |
| Special     | blue        | cotton      | blue         |

事實證明，保留左表格中的所有記錄在許多不同的情況下都很有用。例如，如果我們想要查看哪些油漆沒有比對的布料，我們可以執行左外部 SQL 聯結

SELECT
  *
FROM
  paint LEFT OUTER JOIN fabric
ON
  paint.color = fabric.shade

|  paint.brand | paint.color | fabric.kind  | fabric.shade |
| ------------ | ----------- | ------------ | ------------ |
| Premiere     | red         | NULL         | NULL         |
| Premiere     | blue        | cotton       | blue         |
| Special      | blue        | cotton       | blue         |

如果我們僅選取右側表格中的值為 NULL 的列，則更容易閱讀

SELECT
  *
FROM
  paint LEFT OUTER JOIN fabric
ON
  paint.color = fabric.shade
WHERE
  fabric.shade IS NULL

|  paint.brand | paint.color | fabric.kind  | fabric.shade |
| ------------ | ----------- | ------------ | ------------ |
| Premiere     | red         | NULL         | NULL         |

我們可以使用此技術來取得沒有任何評論的產品清單，方法是執行左外部聯結，並且僅保留 reviews.product_id 已填入 NULL 的列

SELECT
  products.title
FROM
  products LEFT OUTER JOIN reviews
ON
  products.id = reviews.product_id
WHERE
  reviews.product_id IS NULL

| products.title          |
| ----------------------- |
| Small Marble Shoes      |
| Ergonomic Silk Coat     |
| Synergistic Steel Chair |
| ...                     |

右外部 SQL 聯結和完整外部聯結呢？

SQL 標準為外部聯結定義了另外兩種 SQL 聯結類型，但它們的使用頻率遠低於前者 — 甚至低到某些資料庫甚至沒有實作它們。右外部聯結的運作方式與左外部聯結完全相同，不同之處在於它始終保留右表格中的列，並且在沒有比對時，在左表格的欄中填入 NULL。很容易看出，您可以始終透過交換表格來使用左外部聯結來取代右外部聯結；沒有特別的理由偏愛其中一種，但幾乎每個人都使用左手形式，因此我們建議您也這樣做。

完整外部聯結會保留來自兩個表格的所有資訊。如果左側的記錄缺少右側的比對，則資料庫會以 NULL 填入遺失的右側值，而如果右側的記錄缺少左側的比對，則它會填入遺失的左側值。例如，如果我們對 paints 和 fabrics 執行完整外部聯結，我們會得到

|  paint.brand | paint.color | fabric.kind  | fabric.shade |
| ------------ | ----------- | ------------ | ------------ |
| Premiere     | red         | NULL         | NULL         |
| Premiere     | blue        | cotton       | blue         |
| NULL         | NULL        | nylon        | green        |
| Special      | blue        | cotton       | blue         |

完整外部聯結偶爾可用於尋找兩個表格之間的重疊部分，但在撰寫 SQL 的 20 年中，我只在像這樣的一堂課中使用過它們。

要使用哪種 SQL 聯結類型？

回顧一下，共有四種基本聯結類型。內部聯結僅保留比對的記錄，而其他三種類型則以 NULL 填入遺失的值。有些人將左表格視為主表格或初始表格；您使用的聯結類型將決定您將從該初始表格傳回多少記錄，以及您將根據您想要從另一個表格取得的欄傳回的任何其他記錄。我們已經在此處看到了例外情況（例如，每個產品有多個評論），但這是一個很好的跡象，表示您有一個好的主要表格可以開始使用。

一般來說，您只需要真正使用內部聯結和左外部聯結。您使用的聯結類型取決於您是否想要在結果中包含不比對的列

• 如果您需要在主要表格中使用不比對的列，請使用左外部聯結。
• 如果您不需要不比對的列，請使用內部聯結。

如需聯結的另一個角度，可抽象化 SQL，請查看我們關於使用 Metabase 查詢產生器聯結的文章。

SQL 聯結的常見問題

執行內部 SQL 聯結而不是外部聯結

這可能是最常見的錯誤。真實資料通常有間隙，而內部聯結會在索引鍵未對齊時捨棄記錄，而不會警告您。計算一個表格中沒有在另一個表格中比對的列數是一個很好的安全檢查；如果有的話，您應該考慮使用外部聯結而不是內部聯結。

在「比對」上使用 SQL 聯結，但這些比對沒有意義

一個人的體重（以公斤為單位）及其上次購買的價值（以美元為單位）都是數字，因此可以透過比對它們來執行聯結，但結果（可能）將毫無意義。當一個表格包含多個參考不同表格的外來鍵時，就會出現一個較不輕率的範例，這可能會導致將患者資料與車輛登記而不是預約日期聯結。在表格中宣告外來鍵可以協助防止這種情況發生。

將資料中的 NULL 與來自不比對的 NULL 混淆

如果外部聯結中的其中一個表格包含 NULL，我們可能會得到一個欄，其中的值遺失是因為它們不在原始資料中，而且是因為不比對。根據我們嘗試解決的問題，這些不同「風味」的 NULL 可能很重要。