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<wbr><wbr><wbr><wbr><wbr><wbr><wbr>

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
翻譯作者：碼農網 – 小峰
[ 轉載必須在正文中標註並保留原文連結、譯文連結和譯者等資訊。]

相關文章