SQL 經典回顧：JOIN 表連線操作不完全指南

2017-01-16 分類：資料庫、程式設計開發、首頁精華0人評論發表於2017-01-16

SQL

本文由碼農網 – 小峰原創翻譯，轉載請看清文末的轉載要求，歡迎參與我們的付費投稿計劃！

也許最強大的SQL功能是JOIN操作。這讓所有非關聯式資料庫羨慕不已，因為當你想“合併”兩個資料集時，這個概念是如此簡單，並且又普遍適用。

簡單地說，連線兩個表，就是將一個表中的每一行與另一個表中的每一行結合起來。來自SQL Masterclass的插圖展示了這個原理。

參見我們最近關於使用Venn圖來說明JOIN的文章。上面的插圖比較了INNER JOIN和不同的OUTER JOIN操作，但是這些並不是所有的可能性。讓我們從更系統的角度來看問題。

請注意，每當本文中我說“X發生在Y之前”時，我的意思是“邏輯上，X發生在Y之前”。資料庫優化器仍然可以選擇在X之前執行Y，因為這更快，而不改變結果。有關操作的語法/邏輯順序的更多資訊點選這裡檢視。

好的，讓我們一個個檢視所有的join型別吧！

CROSS JOIN（交叉連線）

最基本的JOIN操作是真正的笛卡爾乘積。它只是組合一個表中的每一行和另一個表中的每一行。維基百科通過一副卡片給出了笛卡爾乘積的最佳例子，交叉連線ranks表和suits表：

在現實世界的場景中，CROSS JOIN在執行報告時非常有用，例如，你可以生成一組日期（例如一個月的天數）並與資料庫中的所有部門交叉連線，以建立完整的天/部門表。使用PostgreSQL語法：

SELECT *

-- This just generates all the days in January 2017
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Here, we're combining all days with all departments
CROSS JOIN departments

想象一下，我們有以下資料：

+--------+   +------------+
| day    |   | department |
+--------+   +------------+
| Jan 01 |   | Dept 1     |
| Jan 02 |   | Dept 2     |
| ...    |   | Dept 3     |
| Jan 30 |   +------------+
| Jan 31 |
+--------+

現在結果將如下所示：

+--------+------------+
| day    | department |
+--------+------------+
| Jan 01 | Dept 1     |
| Jan 01 | Dept 2     |
| Jan 01 | Dept 3     |

| Jan 02 | Dept 1     |
| Jan 02 | Dept 2     |
| Jan 02 | Dept 3     |
| ...    | ...        |
| Jan 31 | Dept 1     |
| Jan 31 | Dept 2     |
| Jan 31 | Dept 3     |
+--------+------------+

現在，在每個天/部門組合中，你可以計算該部門的每日收入，或其他。

特點

CROSS JOIN是笛卡爾乘積，即“乘法”中的乘積。數學符號使用乘號表示此操作：A×B，或在本文例子中：days×departments。

與“普通”算術乘法一樣，如果兩個表中有一個為空（大小為零），則結果也將為空（大小為零）。這是完全有道理的。如果我們將前面的31天與0個部門組合，我們將獲得0天/部門組合。同樣的，如果我們將空日期範圍與任何數量的部門組合，我們也會獲得0天/部門組合。

換一種說法：

size(result) = size(days) * size(departments)

替代語法

以前，在ANSI JOIN語法被引入到SQL之前，大家就會在FROM子句中寫以逗號分隔的表格列表來編寫CROSS JOIN。上面的查詢等價於：

SELECT *
FROM
  generate_series(
    '2017-01-01'::TIMESTAMP,
    '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
    INTERVAL '1 day'
  ) AS days(day),
  departments

一般來說，我強烈建議使用CROSS JOIN關鍵字，而不是以逗號分隔的表格列表，因為如果你有意地想要執行CROSS JOIN，那麼沒有什麼可以比使用實際的關鍵字能更好地傳達這個意圖（對下一個開發人員而言）。何況用逗號分隔的表格列表中有這麼多地方都有可能會出錯。你肯定不希望看到這樣的事情！

INNER JOIN（Theta-JOIN）

構建在先前的CROSS JOIN操作之上，INNER JOIN（或者只是簡單的JOIN，有時也稱為“THETA”JOIN）允許通過某些謂詞過濾笛卡爾乘積的結果。大多數時候，我們把這個謂詞放在ON子句中，它可能是這樣的：

SELECT *

-- Same as before
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Now, exclude all days/departments combinations for
-- days before the department was created
JOIN departments AS d ON day >= d.created_at

在大多數資料庫中，INNER關鍵字是可選的，因此我在本文中略去了。

請注意INNER JOIN操作是如何允許在ON子句中放置任意謂詞的，這在執行報告時也非常有用。就像在之前的CROSS JOIN示例中一樣，我們將所有日期與所有部門結合在一起，但是我們只保留那些部門已經存在的天/部門組合，即部門建立在天之前。

再次，使用此資料：

+--------+   +------------+------------+
| day    |   | department | created_at |
+--------+   +------------+------------+
| Jan 01 |   | Dept 1     | Jan 10     |
| Jan 02 |   | Dept 2     | Jan 11     |
| ...    |   | Dept 3     | Jan 12     |
| Jan 30 |   +------------+------------+
| Jan 31 |
+--------+

現在結果將如下所示：

+--------+------------+
| day    | department |
+--------+------------+
| Jan 10 | Dept 1     |

| Jan 11 | Dept 1     |
| Jan 11 | Dept 2     |

| Jan 12 | Dept 1     |
| Jan 12 | Dept 2     |
| Jan 12 | Dept 3     |

| Jan 13 | Dept 1     |
| Jan 13 | Dept 2     |
| Jan 13 | Dept 3     |
| ...    | ...        |
| Jan 31 | Dept 1     |
| Jan 31 | Dept 2     |
| Jan 31 | Dept 3     |
+--------+------------+

因此，我們在1月10日之前沒有任何結果，因為這些行被過濾掉了。

特點

INNER JOIN操作是過濾後的CROSS JOIN操作。這意味著如果兩個表中有一個是空的，那麼結果也保證為空。但是與CROSS JOIN不同的是，由於謂詞的存在，我們總能獲得比CROSS JOIN提供的更少的結果。

換一種說法：

size(result) <= size(days) * size(departments)

替代語法

雖然ON子句對於INNER JOIN操作是強制的，但是你不需要在其中放置JOIN謂詞（雖然從可讀性角度強烈推薦）。大多數資料庫將以同樣的方式優化以下等價查詢：

SELECT *
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- You can always JOIN .. ON true (or 1 = 1 in other DBs)
-- to turn an syntactic INNER JOIN into a semantic CROSS JOIN
JOIN departments AS d ON true

-- ... and then turn the CROSS JOIN back into an INNER JOIN
-- by putting the JOIN predicate in the WHERE clause:
WHERE day >= d.created_at

當然，再次，那只是為讀者模糊了查詢，但你可能有你的理由，對吧？如果我們進一步，那麼下面的查詢也是等效的，因為大多數優化器可以指出等價物並轉而執行INNER JOIN：

SELECT *
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)

-- Now, this is really a syntactic CROSS JOIN
CROSS JOIN departments AS d
WHERE day >= d.created_at

…並且，如前所述，CROSS JOIN只是用逗號分隔的表格列表的語法糖。在這種情況下，我們保留WHERE子句以獲得在引入ANSI JOIN語法之前人們經常做的事情：

SELECT *
FROM
  generate_series(
    '2017-01-01'::TIMESTAMP,
    '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
    INTERVAL '1 day'
  ) AS days(day),
  departments AS d
WHERE day >= d.created_at

所有這些語法都了做同樣的事情，通常沒有效能損失，但顯然，它們比原始的INNER JOIN語法更不可讀。

EQUI JOIN

有時，在著作中，你會聽到EQUI JOIN這個術語，其中“EQUI”不是SQL關鍵字，而只是作為一種特殊的INNER JOIN寫法。

事實上，令人奇怪的是“EQUI”JOIN是特殊情況，因為我們在SQL中EQUI JOIN做得最多，並且在OLTP應用程式中，我們只是通過主鍵/外來鍵關係JOIN。例如：

SELECT *
FROM actor AS a
JOIN film_actor AS fa ON a.actor_id = fa.actor_id
JOIN film AS f ON f.film_id = fa.film_id

上述查詢選擇了所有演員及其電影。有兩個INNER JOIN操作，一個將actors連線到film_actor關係表中的相應條目（因為演員可以演許多電影，而電影可以有許多演員出演），並且另一個連線每個film_actor與關於電影本身的附加資訊的關係。

特點

該操作的特點與“一般的”INNER JOIN操作的特點相同。“EQUI”JOIN仍然結果集減少了的笛卡爾乘積（CROSS JOIN），即僅包含給定演員在給定電影中實際播放的那些演員/電影組合。

因此，換句話說：

size(result) <= size(actor) * size(film)

結果大小等於演員大小乘以電影大小，但是每個演員在每部電影中都出演是不太可能的。

替代語法：USING

再次，和前面一樣，我們可以寫INNER JOIN操作，而不使用實際的INNER JOIN語法，而是使用CROSS JOIN或以逗號分隔的表格列表。這很無聊，但更有趣的是以下兩種替代語法，其中之一是非常有用的：

SELECT *
FROM actor
JOIN film_actor USING (actor_id)
JOIN film USING (film_id)

USING子句替換ON子句，並允許列出必須在JOIN操作的兩側出現的一組列。如果你以與Sakila資料庫相同的方式仔細設計資料庫，即每個外來鍵列具有與它們引用的主鍵列相同的名稱（例如actor.actor_id = film_actor.actor_id），那麼你至少可以在這些資料庫中使用USING 用於“EQUI”JOIN：

Derby
Firebird
HSQLDB
Ingres
MariaDB
MySQL
Oracle
PostgreSQL
SQLite
Vertica

不幸的是，這些資料庫不支援這個語法：

Access
Cubrid
DB2
H2
HANA
Informix
SQL Server
Sybase ASE
Sybase SQL Anywhere

雖然這產生的結果與ON子句完全相同（幾乎相同），但讀取和寫入更快。我之所以“幾乎”是因為一些資料庫（以及SQL標準）指定，任何出現在USING子句中的列失去其限定。例如：

SELECT
  f.title,   -- Ordinary column, can be qualified
  f.film_id, -- USING column, shouldn't be qualified
  film_id    -- USING column, correct / non-ambiguous here
FROM actor AS a
JOIN film_actor AS fa USING (actor_id)
JOIN film AS f USING (film_id)

另外，當然，這種語法有點限制。有時，你的表中有多個外來鍵，但不是所有鍵都具有主鍵列名稱。例如：

CREATE TABLE film (
  ..
  language_id          BIGINT REFERENCES language,
  original_language_id BIGINT REFERENCES language,
)

如果你想通過ORIGINAL_LANGUAGE_ID連線，則必須訴諸ON子句。

備選語法：NATURAL JOIN

“EQUI”JOIN的一個更極端和更少有用的形式是NATURAL JOIN子句。前面的例子可以通過NATURAL JOIN替換USING來進一步“改進”，像這樣：

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

請注意，我們不再需要指定任何JOIN標準，因為NATURAL JOIN將自動從它加入的兩個表中獲取所有共享相同名稱的列，並將它們放置在“隱藏”的USING子句中。正如我們前面所看到的，由於主鍵和外來鍵具有相同的列名，這看起來很有用。

真相是：這是沒用的。在Sakila資料庫中，每個表還有一個LAST_UPDATE列，這是NATURAL JOIN會自動考慮的。因此NATURAL JOIN查詢等價於：

SELECT *
FROM actor
JOIN film_actor USING (actor_id, last_update)
JOIN film USING (film_id, last_update)

…這當然完全沒有任何意義。所以，馬上將NATURAL JOIN拋之腦後吧（除了一些非常罕見的情況，例如當連線Oracle的診斷檢視，如v$sql NATURAL JOIN v$sql_plan等，用於ad-hoc分析）

OUTER JOIN

我們之前已經見識過INNER JOIN，它僅針對左/右表的組合返回結果，其中ON謂詞產生true。

OUTER JOIN允許我們保留rowson的左/ 右側，因此我們就找不到匹配的組合。讓我們回到日期和部門的例子：

SELECT *
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)
LEFT JOIN departments AS d ON day >= d.created_at

同樣，OUTER關鍵字是可選的，所以我在示例中省略了它。

此查詢與INNER JOIN計數器部分有著非常微妙的不同，它每天總會返回至少一行，即使在給定的某一天沒有在該天建立的部門。例如：所有部門都在1月10日建立。上述查詢仍將返回1月1日至9日：

+--------+   +------------+------------+
| day    |   | department | created_at |
+--------+   +------------+------------+
| Jan 01 |   | Dept 1     | Jan 10     |
| Jan 02 |   | Dept 2     | Jan 11     |
| ...    |   | Dept 3     | Jan 12     |
| Jan 30 |   +------------+------------+
| Jan 31 |
+--------+

除了我們之前在INNER JOIN示例中獲得的行之外，我們現在還有從1月1日到9日的所有日期，其中包含NULL部門：

+--------+------------+
| day    | department |
+--------+------------+
| Jan 01 |            | -- Extra rows with no match here
| Jan 02 |            | -- Extra rows with no match here
| ...    |            | -- Extra rows with no match here
| Jan 09 |            | -- Extra rows with no match here
| Jan 10 | Dept 1     |
| Jan 11 | Dept 1     |
| Jan 11 | Dept 2     |
| Jan 12 | Dept 1     |
| Jan 12 | Dept 2     |
| Jan 12 | Dept 3     |
| Jan 13 | Dept 1     |
| Jan 13 | Dept 2     |
| Jan 13 | Dept 3     |
| ...    | ...        |
| Jan 31 | Dept 1     |
| Jan 31 | Dept 2     |
| Jan 31 | Dept 3     |
+--------+------------+

換句話說，每一天在結果中至少出現一次。 LEFT JOIN對左表執行此操作，即它保留結果中來自左表的所有行。

正式地說，LEFT OUTER JOIN是一個像這樣帶有UNION的INNER JOIN：

-- Convenient syntax:
SELECT *
FROM a LEFT JOIN b ON <predicate>

-- Cumbersome, equivalent syntax:
SELECT a.*, b.*
FROM a JOIN b ON <predicate>
UNION ALL
SELECT a.*, NULL, NULL, ..., NULL
FROM a
WHERE NOT EXISTS (
  SELECT * FROM b WHERE <predicate>
)

RIGHT OUTER JOIN

RIGHT OUTER JOIN正好相反。它保留結果中來自右表的所有行。讓我們新增更多部門

+--------+   +------------+------------+
| day    |   | department | created_at |
+--------+   +------------+------------+
| Jan 01 |   | Dept 1     | Jan 10     |
| Jan 02 |   | Dept 2     | Jan 11     |
| ...    |   | Dept 3     | Jan 12     |
| Jan 30 |   | Dept 4     | Apr 01     |
| Jan 31 |   | Dept 5     | Apr 02     |
+--------+   +------------+------------+

新的部門4和5將不會在以前的結果中，因為它們是在1月31日之後的某一天建立的。但是它將顯示在右連線結果中，因為部門是連線操作的右表，並且來自右表中的所有行都將被保留。

執行此查詢：

SELECT *
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)
RIGHT JOIN departments AS d ON day >= d.created_at

將產生：

+--------+------------+
| day    | department |
+--------+------------+
| Jan 10 | Dept 1     |
| Jan 11 | Dept 1     |
| Jan 11 | Dept 2     |
| Jan 12 | Dept 1     |
| Jan 12 | Dept 2     |
| Jan 12 | Dept 3     |
| Jan 13 | Dept 1     |
| Jan 13 | Dept 2     |
| Jan 13 | Dept 3     |
| ...    | ...        |
| Jan 31 | Dept 1     |
| Jan 31 | Dept 2     |
| Jan 31 | Dept 3     |
|        | Dept 4     | -- Extra rows with no match here
|        | Dept 5     | -- Extra rows with no match here
+--------+------------+

在大多數情況下，每個LEFT OUTER JOIN表示式都可以轉換為等效的RIGHT OUTER JOIN表示式，反之亦然。因為RIGHT OUTER JOIN通常不太可讀，大多數人只使用LEFT OUTER JOIN。

FULL OUTER JOIN

最後，還有FULL OUTER JOIN，它保留JOIN操作兩側的所有行。在我們的示例中，這意味著每一天在結果中至少出現一次，就像每個部門在結果中至少出現一次一樣。

讓我們再來看一下這個資料：

+--------+   +------------+------------+
| day    |   | department | created_at |
+--------+   +------------+------------+
| Jan 01 |   | Dept 1     | Jan 10     |
| Jan 02 |   | Dept 2     | Jan 11     |
| ...    |   | Dept 3     | Jan 12     |
| Jan 30 |   | Dept 4     | Apr 01     |
| Jan 31 |   | Dept 5     | Apr 02     |
+--------+   +------------+------------+

現在，讓我們執行這個查詢：

SELECT *
FROM generate_series(
  '2017-01-01'::TIMESTAMP,
  '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day',
  INTERVAL '1 day'
) AS days(day)
FULL JOIN departments AS d ON day >= d.created_at

現在結果看起來像這樣：

+--------+------------+
| day    | department |
+--------+------------+
| Jan 01 |            | -- row from the left table
| Jan 02 |            | -- row from the left table
| ...    |            | -- row from the left table
| Jan 09 |            | -- row from the left table
| Jan 10 | Dept 1     |
| Jan 11 | Dept 1     |
| Jan 11 | Dept 2     |
| Jan 12 | Dept 1     |
| Jan 12 | Dept 2     |
| Jan 12 | Dept 3     |
| Jan 13 | Dept 1     |
| Jan 13 | Dept 2     |
| Jan 13 | Dept 3     |
| ...    | ...        |
| Jan 31 | Dept 1     |
| Jan 31 | Dept 2     |
| Jan 31 | Dept 3     |
|        | Dept 4     | -- row from the right table
|        | Dept 5     | -- row from the right table 
+--------+------------+

如果你堅持，正式地說來，LEFT OUTER JOIN是一個像這樣帶有UNION的INNER JION：

-- Convenient syntax:
SELECT *
FROM a LEFT JOIN b ON <predicate>

-- Cumbersome, equivalent syntax:
SELECT a.*, b.*
FROM a JOIN b ON <predicate>
-- LEFT JOIN part
UNION ALL
SELECT a.*, NULL, NULL, ..., NULL
FROM a
WHERE NOT EXISTS (
  SELECT * FROM b WHERE <predicate>
)
-- RIGHT JOIN part
UNION ALL
SELECT NULL, NULL, ..., NULL, b.*
FROM b
WHERE NOT EXISTS (
  SELECT * FROM a WHERE <predicate>
)

備選語法：“EQUI”OUTER JOIN

上面的例子再次使用了某種“帶過濾器的笛卡爾積”JOIN。然而，更常見的是“EQUI”OUTER JOIN方法，其中我們連線了主鍵/外來鍵關係。讓我們回到Sakila資料庫示例。一些演員沒有在任何電影中出演，那麼我們可能希望像這樣查詢：

SELECT *
FROM actor
LEFT JOIN film_actor USING (actor_id)
LEFT JOIN film USING (film_id)

此查詢將返回所有actors至少一次，無論他們是否在電影中出演。如果我們還想要所有沒有演員的電影，那麼我們可以使用FULL OUTER JOIN來組合結果：

SELECT *
FROM actor
FULL JOIN film_actor USING (actor_id)
FULL JOIN film USING (film_id)

當然，這也適用於NATURAL LEFT JOIN，NATURAL RIGHT JOIN，NATURAL FULL JOIN，但同樣的，這些都沒有用，因為我們將再次使用USING（…，LAST_UPDATE），這使之完全沒有任何意義。

備選語法：Oracle和SQL Server style OUTER JOIN

這兩個資料庫在ANSI語法建立之前有OUTER JOIN。它看起來像這樣：

-- Oracle
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id = fa.actor_id(+)
AND fa.film_id = f.film_id(+)

-- SQL Server
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id *= fa.actor_id
AND fa.film_id *= f.film_id

很好，假定某個時間點（在80年代？？），ANSI沒有指定OUTER JOIN。但80年代是在30多年前，所以，可以安全地說這個東西已經過時了。

SQL Server做了正確的事情，很久以前就棄用（以及後面刪除）了語法。因為向後相容性的原因，Oracle仍然支援。

但是關於這種語法沒有什麼是合理或可讀的。所以不要使用它。用ANSI JOIN替換。

PARTITIONED OUTER JOIN

這是Oracle特定的，但我必須說，這是一個真正的恥辱，因為沒有其他資料庫偷竊該功能。還記住我們用來將每一天與每個部門組合的CROSS JOIN操作？因為，有時，這是我們想要的結果：所有的組合，並且如果有一個匹配的話也匹配行中的值。

這很難用文字描述，用例子講就容易多了。下面是使用Oracle語法的查詢：

WITH

  -- Using CONNECT BY to generate all dates in January
  days(day) AS (
    SELECT DATE '2017-01-01' + LEVEL - 1
    FROM dual
    CONNECT BY LEVEL <= 31
  ),

  -- Our departments
  departments(department, created_at) AS (
    SELECT 'Dept 1', DATE '2017-01-10' FROM dual UNION ALL
    SELECT 'Dept 2', DATE '2017-01-11' FROM dual UNION ALL
    SELECT 'Dept 3', DATE '2017-01-12' FROM dual UNION ALL
    SELECT 'Dept 4', DATE '2017-04-01' FROM dual UNION ALL
    SELECT 'Dept 5', DATE '2017-04-02' FROM dual
  )
SELECT *
FROM days 
LEFT JOIN departments 
  PARTITION BY (department) -- This is where the magic happens
  ON day >= created_at

不幸的是，PARTITION BY用在具有不同含義的各種上下文中（例如針對視窗函式）。在這種情況下，這意味著我們通過departments.department列“partition”我們的資料，為每個部門建立一個“partition”。現在，每個（partition）分割槽將獲得每一天的副本，無論在我們的謂詞中是否有匹配（不像在普通的LEFT JOIN情況下，我們有一堆“缺少部門”的日期）。上面的查詢結果現在是這樣的：

+--------+------------+------------+
| day    | department | created_at |
+--------+------------+------------+
| Jan 01 | Dept 1     |            | -- Didn't match, but still get row
| Jan 02 | Dept 1     |            | -- Didn't match, but still get row
| ...    | Dept 1     |            | -- Didn't match, but still get row
| Jan 09 | Dept 1     |            | -- Didn't match, but still get row
| Jan 10 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 11 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 12 | Dept 1     | Jan 10     | -- Matches, so get join result
| ...    | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 31 | Dept 1     | Jan 10     | -- Matches, so get join result

| Jan 01 | Dept 2     |            | -- Didn't match, but still get row
| Jan 02 | Dept 2     |            | -- Didn't match, but still get row
| ...    | Dept 2     |            | -- Didn't match, but still get row
| Jan 09 | Dept 2     |            | -- Didn't match, but still get row
| Jan 10 | Dept 2     |            | -- Didn't match, but still get row
| Jan 11 | Dept 2     | Jan 11     | -- Matches, so get join result
| Jan 12 | Dept 2     | Jan 11     | -- Matches, so get join result
| ...    | Dept 2     | Jan 11     | -- Matches, so get join result
| Jan 31 | Dept 2     | Jan 11     | -- Matches, so get join result

| Jan 01 | Dept 3     |            | -- Didn't match, but still get row
| Jan 02 | Dept 3     |            | -- Didn't match, but still get row
| ...    | Dept 3     |            | -- Didn't match, but still get row
| Jan 09 | Dept 3     |            | -- Didn't match, but still get row
| Jan 10 | Dept 3     |            | -- Didn't match, but still get row
| Jan 11 | Dept 3     |            | -- Didn't match, but still get row
| Jan 12 | Dept 3     | Jan 12     | -- Matches, so get join result
| ...    | Dept 3     | Jan 12     | -- Matches, so get join result
| Jan 31 | Dept 3     | Jan 12     | -- Matches, so get join result

| Jan 01 | Dept 4     |            | -- Didn't match, but still get row
| Jan 02 | Dept 4     |            | -- Didn't match, but still get row
| ...    | Dept 4     |            | -- Didn't match, but still get row
| Jan 31 | Dept 4     |            | -- Didn't match, but still get row

| Jan 01 | Dept 5     |            | -- Didn't match, but still get row
| Jan 02 | Dept 5     |            | -- Didn't match, but still get row
| ...    | Dept 5     |            | -- Didn't match, but still get row
| Jan 31 | Dept 5     |            | -- Didn't match, but still get row
+--------+------------+

正如你所看到的，我已經為5個部門建立了5個分割槽。每個分割槽通過每一天來組合部門，但不像CROSS JOIN時做的那樣，我們現在實際得到的是LEFT JOIN .. ON ..結果，萬一謂詞有匹配的話。這在Oracle報告中是一個非常好的功能！

SEMI JOIN

在關係代數中，存在半連線操作的概念，遺憾的是這在SQL中沒有語法表示。如果有語法的話，可能會是LEFT SEMI JOIN和RIGHT SEMI JOIN，就像Cloudera Impala語法擴充套件提供的那樣。

什麼是“SEMI” JOIN？

當寫下如下虛構查詢時：

SELECT *
FROM actor
LEFT SEMI JOIN film_actor USING (actor_id)

我們真正的意思是，我們想要電影中出演的所有演員。但我們不想在結果中出現任何電影，只要演員。更具體地說，我們不想讓每個演員出現多次，即一部電影出現一次。我們希望每個演員在結果中只出現一次（或零次）。

Semi在拉丁語中為“半”的意思，即我們只實現“半連線”，在這種情況下，即左半部分。

在SQL中，有兩個可以模擬“SEMI”JOIN的替代語法

備選語法：EXISTS

這是更強大和更冗長的語法

SELECT *
FROM actor a
WHERE EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

我們正在尋找存在於一部電影的所有演員，即在電影中演出的演員。使用這種語法（即，“SEMI”JOIN被放置在WHERE子句中），很明顯我們可以在結果中最多得到每個演員一次。語法中沒有實際的JOIN。

儘管如此，大多數資料庫能夠識別這裡真正發生的是“SEMI”JOIN，而不僅僅是一個普通的EXISTS()謂詞。例如，對上述查詢考慮Oracle執行計劃：

注意Oracle如何呼叫操作“HASH JOIN（SEMI）” ——此處存在SEMI關鍵字。 PostgreSQL也是這樣：

或SQL Server：

除了是正確的最佳解決方案，使用“SEMI”JOIN而不是INNER JOIN也有一些效能優勢，因為資料庫可以在找到第一個匹配後立即停止查詢匹配項！

替代語法：IN

IN和EXISTS完全等同於“SEMI”JOIN模擬。以下查詢將在大多數資料庫（不是MySQL）中生成與先前EXISTS查詢相同的計劃：

SELECT *
FROM actor
WHERE actor_id IN (
  SELECT actor_id FROM film_actor
)

如果你的資料庫支援“SEMI”JOIN操作的兩種語法，你或許可以從文體的角度選擇你喜歡的。

這與下面的JOIN是不一樣的。

ANTI JOIN

原則上，“ANTI”JOIN正好與“SEMI”JOIN相反。當寫下如下虛構查詢時：

SELECT *
FROM actor
LEFT ANTI JOIN film_actor USING (actor_id)

…我們正在做的是找出所有沒有在任何電影中出演的演員。不幸的是，再次，SQL並沒有這個操作的內建語法，但我們可以用EXISTS來模擬它：

替代語法：NOT EXISTS

以下查詢正好有預期的語義：

SELECT *
FROM actor a
WHERE NOT EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

（危險）替代語法：NOT IN

小心！雖然EXISTS和IN是等效的，但NOT EXISTS和NOT IN是不等效的。因為NULL值！

在這種特殊情況下，下面的NOT IN查詢等同於先前的NOT EXISTS查詢，因為我們的film_actor表在film_actor.actor_id上有一個NOT NULL約束

SELECT *
FROM actor
WHERE actor_id NOT IN (
  SELECT actor_id FROM film_actor
)

然而，如果actor_id變為可空，那麼查詢將是錯誤的。不相信嗎？嘗試執行：

SELECT *
FROM actor
WHERE actor_id NOT IN (1, 2, NULL)

它不會返回任何記錄。為什麼？因為NULL在SQL中是UNKNOWN值。所以，上面的謂詞如下是一樣的：

SELECT *
FROM actor
WHERE actor_id NOT IN (1, 2, UNKNOWN)

並且因為我們不能確定actor_id是否在一個值為UNKNOWN（是4？還是5？抑或-1？）的一組值中，因此整個謂詞變為UNKNOWN

SELECT *
FROM actor
WHERE UNKNOWN

如果你想了解更多，這裡有一篇Joe Celko寫的關於三值邏輯的好文章。

當然，這樣還不夠：

不要在SQL中使用NOT IN謂詞，除非你新增常量，非空值。
——Lukas Eder。現在。

甚至不要在存在NOT NULL約束時進行冒險。也許，一些DBA可能暫時關閉約束來載入一些資料，但是你的查詢當下卻會是錯的。只使用NOT EXISTS。或者，在某些情況下…

（危險）替代語法：LEFT JOIN / IS NULL

奇怪的是，有些人喜歡以下語法：

SELECT *
FROM actor a
LEFT JOIN film_actor fa
USING (actor_id)
WHERE film_id IS NULL

這是正確的，因為我們：

連線電影加到演員
保留所有演員而不保留電影（LEFT JOIN）
保留沒有出演電影的演員（film_id IS NULL）

好吧，我個人不怎麼喜歡這種語法，因為它一點也沒有傳達“ANTI”JOIN的意圖。而且有可能會很慢，因為你的優化器不認為這是一個“ANTI”JOIN操作（或者事實上，它不能正式證明它可能是）。所以，再次，使用NOT EXISTS代替。

一個有趣的（但有點過時）部落格文章比較了這三個語法：

https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server

LATERAL JOIN

LATERAL是SQL標準中相對較新的關鍵字，並且它得到了PostgreSQL和Oracle的支援。SQL Server人員有一個特定於供應商的替代語法，總是使用APPLY關鍵字（這個我個人更喜歡）。讓我們看一個使用PostgreSQL / Oracle LATERAL關鍵字的例子：

WITH
  departments(department, created_at) AS (
    VALUES ('Dept 1', DATE '2017-01-10'),
           ('Dept 2', DATE '2017-01-11'),
           ('Dept 3', DATE '2017-01-12'),
           ('Dept 4', DATE '2017-04-01'),
           ('Dept 5', DATE '2017-04-02')
  )
SELECT *
FROM departments AS d
CROSS JOIN LATERAL generate_series(
  d.created_at, -- We can dereference a column from department!
  '2017-01-31'::TIMESTAMP, 
  INTERVAL '1 day'
) AS days(day)

事實上，與其在所有部門和所有日子之間進行CROSS JOIN，為什麼不直接為每個部門生成必要的日期？這就是LATERAL的作用。它是任何JOIN操作（包括INNER JOIN，LEFT OUTER JOIN等）右側的字首，允許右側從左側訪問列。

這當然與關係代數不再有關，因為它強加了一個JOIN順序（從左到右）。有時，這是OK的，有時，你的表值函式（或子查詢）是如此複雜，於是那通常是你可以實際使用它的唯一方法。

另一個非常受歡迎的用例是將“TOP-N”查詢連線到常規表中。如果你想找到每個演員，以及他們最暢銷的TOP 5電影：

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
LEFT OUTER JOIN LATERAL (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  JOIN inventory AS i USING (film_id)
  JOIN rental AS r USING (inventory_id)
  JOIN payment AS p USING (rental_id)
  WHERE fa.actor_id = a.actor_id
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f
ON true

結果可能會是：

不要擔心派生表中一長串的連線，這就是我們如何在Sakila資料庫中從FILM表到PAYMENT表獲取的原理：

基本上，子查詢計算每個演員最暢銷的TOP 5電影。因此，它不是“經典的”派生表，而是返回多個行和一列的相關子查詢。我們都習慣於寫這樣的相關子查詢：

SELECT
  a.first_name, 
  a.last_name, 
  (SELECT count(*) 
   FROM film_actor AS fa 
   WHERE fa.actor_id = a.actor_id) AS films
FROM actor AS a

特點：

LATERAL關鍵字並沒有真正改變被應用的JOIN型別的語義。如果你執行CROSS JOIN LATERAL，結果大小仍然是

size(result) = size(left table) * size(right table)

即使右表是在左列表每行的基礎上產生的。

你也可以使用OUTER JOIN來使用LATERAL，即使你的表函式不返回右側的任何行，這樣的情況下，左側的行也將被保留。

替代語法：APPLY

SQL Server沒有選擇混亂的LATERAL關鍵字，它們很久以前就引入了APPLY關鍵字（更具體地說：CROSS APPLY和OUTER APPLY），這更有意義，因為我們對錶的每一行應用一個函式。讓我們假設我們在SQL Server中有一個generate_series()函式：

-- Use with care, this is quite inefficient!
CREATE FUNCTION generate_series(@d1 DATE, @d2 DATE)
RETURNS TABLE AS
RETURN
  WITH t(d) AS (
    SELECT @d1 
    UNION ALL
    SELECT DATEADD(day, 1, d) 
    FROM t
    WHERE d < @d2
  ) 
  SELECT * FROM t;

然後，我們可以使用CROSS APPLY為每個部門呼叫函式：

WITH
  departments AS (
    SELECT * FROM (
      VALUES ('Dept 1', CAST('2017-01-10' AS DATE)),
             ('Dept 2', CAST('2017-01-11' AS DATE)),
             ('Dept 3', CAST('2017-01-12' AS DATE)),
             ('Dept 4', CAST('2017-04-01' AS DATE)),
             ('Dept 5', CAST('2017-04-02' AS DATE))
    ) d(department, created_at)
  )
SELECT *
FROM departments AS d
CROSS APPLY dbo.generate_series(
  d.created_at, -- We can dereference a column from department!
  CAST('2017-01-31' AS DATE)
)

這個語法的好處是——再次——我們對錶的每一行應用一個函式，並且該函式產生行。聽起來耳熟？在Java 8中，我們將對此使用Stream.flatMap()！考慮以下流的使用：

departments.stream()
.flatMap(department -> generateSeries(
            department.createdAt, 
             LocalDate.parse("2017-01-31"))
             .map(day -> tuple(department, day))
);

這裡發生了什麼？

DEPARTMENTS表只是一個Java部門流
我們使用一個為每個部門生成元組的函式來對映department流
這些元組包括部門本身，以及從部門CreatedAt日期開始的一系列日期中生成的一天

同樣的故事！ SQL CROSS APPLY / CROSS JOIN LATERAL與Java的Stream.flatMap()是一樣的。事實上，SQL和流並不是太不同。有關更多資訊，請閱讀此部落格文章。

注意：就像我們可以編寫LEFT OUTER JOIN LATERAL一樣，我們還可以編寫OUTER APPLY，以便保留JOIN表示式的左側。

MULTISET

很少資料庫實現這個（實際上，只有Oracle），但如果你思考它的話，它真的是一個超棒的JOIN型別。建立了巢狀集合。如果所有資料庫都實現它的話，那麼我們就不需要ORM！

來一個假設的例子（使用SQL標準語法，而不是Oracle的），像這樣：

SELECT a.*, MULTISET (
  SELECT f.*
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  WHERE a.actor_id = fa.actor_id
) AS films
FROM actor

MULTISET運算子使用相關子查詢引數，並在巢狀集合中聚合其所有生成的行。這和LEFT OUTER JOIN（我們得到了所有的演員，並且如果他們參演電影的話，我們也得到了他們的所有電影）的工作方式類似，但不是複製結果集中的所有演員，而是將它們收集到巢狀集合中。

就像我們在ORM中所做的那樣，當獲取事物到這個結構中時：

@Entity
class Actor {
  @ManyToMany
  List<Film> films;
}

@Entity
class Film {
}

忽略使用的JPA註釋的不完整性，我只想展示巢狀集合的強度。與在ORM中不同，SQL MULTISET運算子允許將相關子查詢的任意結果收集到巢狀集合中——而不僅僅是實際實體。這比ORM強上百萬倍。

備代語法：Oracle

正如我所說，Oracle實際上支援MULTISET，但是你不能建立ad-hoc巢狀集合。由於某種原因，Oracle選擇為這些巢狀集合實現名義型別化，而不是通常的SQL樣式結構型別化。所以你必須提前宣告你的型別：

CREATE TYPE film_t AS OBJECT ( ... );
CREATE TYPE film_tt AS TABLE OF FILM;

SELECT
  a.*, 
  CAST (
    MULTISET (
      SELECT f.*
      FROM film AS f
      JOIN film_actor AS fa USING (film_id)
      WHERE a.actor_id = fa.actor_id
    ) AS film_tt
  ) AS films
FROM actor

有點更冗長，但仍然取得了成功！真贊！

替代語法：PostgreSQL

超棒的PostgreSQL缺少了一個優秀的SQL標準功能，但有一個解決方法：陣列！這次，我們可以使用結構型別，哇哦！所以下面的查詢將在PostgreSQL中返回一個巢狀的行陣列：

SELECT
  a AS actor,
  array_agg(
    f
  ) AS films
FROM actor AS a
JOIN film_actor AS fa USING (actor_id)
JOIN film AS f USING (film_id)
GROUP BY a

結果是每個人的ORDBMS夢想！巢狀記錄和集合無處不在（只有兩列）：

actor                  films
--------------------   ----------------------------------------------------
(1,PENELOPE,GUINESS)   {(1,ACADEMY DINOSAUR),(23,ANACONDA CONFESSIONS),...}
(2,NICK,WAHLBERG)      {(3,ADAPTATION HOLES),(31,APACHE DIVINE),...}
(3,ED,CHASE)           {(17,ALONE TRIP),(40,ARMY FLINTSTONES),...}

如果你說對此你並不覺得令人興奮，好吧，那我也無能為力了。

結論

再次宣告，本文對SQL中JOIN表的許多不同方法可能並不完整。我希望你在這篇文章中能發現1-2個新的技巧。JOIN只是許多非常有趣的SQL操作中的其中一個。

譯文連結：http://www.codeceo.com/article/sql-join-guide.html
英文原文：A Probably Incomplete, Comprehensive Guide to ... to JOIN Tables in SQL
翻譯作者：碼農網 – 小峰
[ 轉載必須在正文中標註並保留原文連結、譯文連結和譯者等資訊。]

Oracle表連線操作——Hash Join（雜湊連線）下
2018-01-09
Oracle
Oracle表連線操作——Hash Join（雜湊連線）上
2014-06-17
Oracle
Oracle表連線操作——Merge Sort Join（合併排序連線）
2014-06-17
Oracle排序
經典排序演算法回顧：
2024-03-14
排序演算法
暴雪全家桶·經典遊戲回顧
2024-04-22
遊戲
sql 連線查詢例項（left join）三表連線查詢
2019-07-17
SQL
【SQL 學習】表連線--natural join 的一個bug
2010-09-12
SQL
圖靈成立七週年——經典回顧
2012-06-04
圖靈
SQL中聯表查詢操作（LEFT JOIN, RIGHT JOIN, INNER JOIN）
2012-10-24
SQL
mysql INNER JOIN、LEFT JOIN、RIGHT JOIN；內連線（等值連線）、左連線、右連線
2024-11-04
MySql
【SQL】表連線 --半連線
2010-09-06
SQL
經典資料結構和演算法回顧
2015-08-02
資料結構演算法
LINQ系列：LINQ to SQL Join連線
2014-10-26
SQL
SQL語句中不同的連線JOIN及SQL中join的各種用法
2018-12-17
SQL
表連線 join和(+)、union和uion all
2011-08-22
UI
MySql的join（連線）查詢（三表 left join 寫法）
2014-03-28
MySql
微課sql最佳化(15)、表的連線方法(4)-關於Hash Join(雜湊連線)
2020-03-04
SQL
Oracle(+)連線與Join連線
2011-07-25
Oracle
【SQL】13 SQL 別名、SQL 連線(JOIN)、SQL INNER JOIN 關鍵字、SQL LEFT JOIN 關鍵字、SQL RIGHT JOIN 關鍵字、SQL FULL OUTER J
2018-07-26
SQL
SQL 三表連線
2018-01-09
SQL
LEFT JOIN 和JOIN 多表連線
2018-08-27
表的連線方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN(轉)
2013-04-12
OOP
微課sql最佳化(16)、表的連線方法(5)-關於Merge Join(排序合連線)
2020-03-04
SQL排序
sql語言中join操作
2016-10-10
SQL
5. SQL回顧
2020-10-24
SQL
[資料庫][SQL]圖解各種連線join
2019-03-28
資料庫SQL圖解
Apache Spark SQL的高階Join連線技術
2019-01-08
ApacheSparkSQL
canvas繪製經典星空連線效果
2018-03-13
Canvas
分庫分表經典15連問
2022-12-13
【SQL 學習】表連線
2010-08-15
SQL
超級馬里奧系列經典遊戲三十年曆史回顧
2020-05-14
遊戲
sql 經典面試題及答案(選課表)
2018-08-09
SQL面試題
連線查詢簡析 join 、 left join 、 right join
2013-12-17
兩種連線的表達：left(right) join 和 (+)
2016-03-04
【SQL】表連線七種方式
2010-09-15
SQL
SQL表連線方式詳解
2009-10-26
SQL
回顧 Web 開發者熟悉的 10 個經典開源專案和工具
2017-08-16
Web
外連線(outer join)示例
2008-01-20

SQL 經典回顧：JOIN 表連線操作不完全指南

CROSS JOIN（交叉連線）

特點

替代語法

INNER JOIN（Theta-JOIN）

特點

替代語法

EQUI JOIN

特點

替代語法：USING

備選語法：NATURAL JOIN

OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

備選語法：“EQUI”OUTER JOIN

備選語法：Oracle和SQL Server style OUTER JOIN

PARTITIONED OUTER JOIN

SEMI JOIN

什麼是“SEMI” JOIN？

備選語法：EXISTS

替代語法：IN

ANTI JOIN

替代語法：NOT EXISTS

（危險）替代語法：NOT IN

（危險）替代語法：LEFT JOIN / IS NULL

LATERAL JOIN

替代語法：APPLY

MULTISET

備代語法：Oracle

替代語法：PostgreSQL

結論

相關文章