也許最強大的SQL功能是JOIN操作。這讓所有非關系數(shù)據(jù)庫羨慕不已，因為當你想“合并”兩個數(shù)據(jù)集時，這個概念是如此簡單，并且又普遍適用。

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

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

請注意，每當本文中我說“X發(fā)生在Y之前”時，我的意思是“邏輯上，X發(fā)生在Y之前”。數(shù)據(jù)庫優(yōu)化器仍然可以選擇在X之前執(zhí)行Y，因為這更快，而不改變結果。有關操作的語法/邏輯順序的更多信息點擊這里查看。

好的，讓我們一個個查看所有的join類型吧！

CROSS JOIN（交叉連接）

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

在現(xiàn)實世界的場景中，CROSS JOIN在執(zhí)行報告時非常有用，例如，你可以生成一組日期（例如一個月的天數(shù)）并與數(shù)據(jù)庫中的所有部門交叉連接，以創(chuàng)建完整的天/部門表。使用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

想象一下，我們有以下數(shù)據(jù)：

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

現(xiàn)在結果將如下所示：

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

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

特點

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

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

換一種說法：

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

替代語法

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

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

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

INNER JOIN（Theta-JOIN）

構建在先前的CROSS JOIN操作之上，INNER JOIN（或者只是簡單的JOIN，有時也稱為“THETA”JOIN）允許通過某些謂詞過濾笛卡爾乘積的結果。大多數(shù)時候，我們把這個謂詞放在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

在大多數(shù)數(shù)據(jù)庫中，INNER關鍵字是可選的，因此我在本文中略去了。

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

再次，使用此數(shù)據(jù)：

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

現(xiàn)在結果將如下所示：

+--------+------------+ | 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謂詞（雖然從可讀性角度強烈推薦）。大多數(shù)數(shù)據(jù)庫將以同樣的方式優(yōu)化以下等價查詢：

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

當然，再次，那只是為讀者模糊了查詢，但你可能有你的理由，對吧？如果我們進一步，那么下面的查詢也是等效的，因為大多數(shù)優(yōu)化器可以指出等價物并轉而執(zhí)行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語法之前人們經(jīng)常做的事情：

SELECT * FROMgenerate_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操作的兩側出現(xiàn)的一組列。如果你以與Sakila數(shù)據(jù)庫相同的方式仔細設計數(shù)據(jù)庫，即每個外鍵列具有與它們引用的主鍵列相同的名稱（例如actor.actor_id = film_actor.actor_id），那么你至少可以在這些數(shù)據(jù)庫中使用USING 用于“EQUI”JOIN：

• Derby
• Firebird
• HSQLDB
• Ingres
• MariaDB
• MySQL
• Oracle
• PostgreSQL
• SQLite
• Vertica

不幸的是，這些數(shù)據(jù)庫不支持這個語法：

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

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

SELECTf.title, -- Ordinary column, can be qualifiedf.film_id, -- USING column, shouldn't be qualifiedfilm_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數(shù)據(jù)庫中，每個表還有一個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

我們之前已經(jīng)見識過INNER JOIN，它僅針對左/右表的組合返回結果，其中ON謂詞產(chǎn)生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計數(shù)器部分有著非常微妙的不同，它每天總會返回至少一行，即使在給定的某一天沒有在該天創(chuàng)建的部門。例如：所有部門都在1月10日創(chuàng)建。上述查詢?nèi)詫⒎祷?月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示例中獲得的行之外，我們現(xiàn)在還有從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 | +--------+------------+

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

正式地說，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日之后的某一天創(chuàng)建的。但是它將顯示在右連接結果中，因為部門是連接操作的右表，并且來自右表中的所有行都將被保留。

運行此查詢：

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

將產(chǎn)生：

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

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

FULL OUTER JOIN

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

讓我們再來看一下這個數(shù)據(jù)：

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

現(xiàn)在，讓我們運行這個查詢：

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

現(xiàn)在結果看起來像這樣：

+--------+------------+ | 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”O(jiān)UTER JOIN

上面的例子再次使用了某種“帶過濾器的笛卡爾積”JOIN。然而，更常見的是“EQUI”O(jiān)UTER JOIN方法，其中我們連接了主鍵/外鍵關系。讓我們回到Sakila數(shù)據(jù)庫示例。一些演員沒有在任何電影中出演，那么我們可能希望像這樣查詢：

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

這兩個數(shù)據(jù)庫在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多年前，所以，可以安全地說這個東西已經(jīng)過時了。

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

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

PARTITIONED OUTER JOIN

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

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

WITH-- Using CONNECT BY to generate all dates in Januarydays(day) AS (SELECT DATE '2017-01-01' + LEVEL - 1FROM dualCONNECT BY LEVEL <= 31),-- Our departmentsdepartments(department, created_at) AS (SELECT 'Dept 1', DATE '2017-01-10' FROM dual UNION ALLSELECT 'Dept 2', DATE '2017-01-11' FROM dual UNION ALLSELECT 'Dept 3', DATE '2017-01-12' FROM dual UNION ALLSELECT 'Dept 4', DATE '2017-04-01' FROM dual UNION ALLSELECT 'Dept 5', DATE '2017-04-02' FROM dual) SELECT * FROM days LEFT JOIN departments PARTITION BY (department) -- This is where the magic happensON day >= created_at

不幸的是，PARTITION BY用在具有不同含義的各種上下文中（例如針對窗口函數(shù)）。在這種情況下，這意味著我們通過departments.department列“partition”我們的數(shù)據(jù)，為每個部門創(chuàng)建一個“partition”。現(xiàn)在，每個（partition）分區(qū)將獲得每一天的副本，無論在我們的謂詞中是否有匹配（不像在普通的LEFT JOIN情況下，我們有一堆“缺少部門”的日期）。上面的查詢結果現(xiàn)在是這樣的：

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

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

SEMI JOIN

在關系代數(shù)中，存在半連接操作的概念，遺憾的是這在SQL中沒有語法表示。如果有語法的話，可能會是LEFT SEMI JOIN和RIGHT SEMI JOIN，就像Cloudera Impala語法擴展提供的那樣。

什么是“SEMI” JOIN？

當寫下如下虛構查詢時：

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

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

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

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

備選語法：EXISTS

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

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

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

盡管如此，大多數(shù)數(shù)據(jù)庫能夠識別這里真正發(fā)生的是“SEMI”JOIN，而不僅僅是一個普通的EXISTS()謂詞。例如，對上述查詢考慮Oracle執(zhí)行計劃：

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

或SQL Server：

除了是正確的最佳解決方案，使用“SEMI”JOIN而不是INNER JOIN也有一些性能優(yōu)勢，因為數(shù)據(jù)庫可以在找到第一個匹配后立即停止查找匹配項！

替代語法：IN

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

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

如果你的數(shù)據(jù)庫支持“SEMI”JOIN操作的兩種語法，你或許可以從文體的角度選擇你喜歡的。

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

ANTI JOIN

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

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

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

替代語法：NOT EXISTS

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

SELECT * FROM actor a WHERE NOT EXISTS (SELECT * FROM film_actor faWHERE 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變?yōu)榭煽?#xff0c;那么查詢將是錯誤的。不相信嗎？嘗試運行：

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？）的一組值中，因此整個謂詞變?yōu)閁NKNOWN

SELECT * FROM actor WHERE UNKNOWN

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

當然，這樣還不夠：

不要在SQL中使用NOT IN謂詞，除非你添加常量，非空值。
——Lukas Eder。現(xiàn)在。

甚至不要在存在NOT NULL約束時進行冒險。也許，一些DBA可能暫時關閉約束來加載一些數(shù)據(jù)，但是你的查詢當下卻會是錯的。只使用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的意圖。而且有可能會很慢，因為你的優(yōu)化器不認為這是一個“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關鍵字的例子：

WITHdepartments(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等）右側的前綴，允許右側從左側訪問列。

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

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

SELECT a.first_name, a.last_name, f.* FROM actor AS a LEFT OUTER JOIN LATERAL (SELECT f.title, SUM(amount) AS revenueFROM film AS fJOIN 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_idGROUP BY f.film_idORDER BY revenue DESCLIMIT 5 ) AS f ON true

結果可能會是：

不要擔心派生表中一長串的連接，這就是我們?nèi)绾卧赟akila數(shù)據(jù)庫中從FILM表到PAYMENT表獲取的原理：

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

SELECTa.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)

即使右表是在左列表每行的基礎上產(chǎn)生的。

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

替代語法：APPLY

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

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

然后，我們可以使用CROSS APPLY為每個部門調用函數(shù)：

WITHdepartments 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) )

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

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

這里發(fā)生了什么？

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

同樣的故事！ SQL CROSS APPLY / CROSS JOIN LATERAL與Java的Stream.flatMap()是一樣的。事實上，SQL和流并不是太不同。有關更多信息，請閱讀此博客文章。

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

MULTISET

很少數(shù)據(jù)庫實現(xiàn)這個（實際上，只有Oracle），但如果你思考它的話，它真的是一個超棒的JOIN類型。創(chuàng)建了嵌套集合。如果所有數(shù)據(jù)庫都實現(xiàn)它的話，那么我們就不需要ORM！

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

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

MULTISET運算符使用相關子查詢參數(shù)，并在嵌套集合中聚合其所有生成的行。這和LEFT OUTER JOIN（我們得到了所有的演員，并且如果他們參演電影的話，我們也得到了他們的所有電影）的工作方式類似，但不是復制結果集中的所有演員，而是將它們收集到嵌套集合中。

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

@Entity class Actor {@ManyToManyList<Film> films; }@Entity class Film { }

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

備代語法：Oracle

正如我所說，Oracle實際上支持MULTISET，但是你不能創(chuàng)建ad-hoc嵌套集合。由于某種原因，Oracle選擇為這些嵌套集合實現(xiàn)名義類型化，而不是通常的SQL樣式結構類型化。所以你必須提前聲明你的類型：

CREATE TYPE film_t AS OBJECT ( ... ); CREATE TYPE film_tt AS TABLE OF FILM;SELECTa.*, CAST (MULTISET (SELECT f.*FROM film AS fJOIN film_actor AS fa USING (film_id)WHERE a.actor_id = fa.actor_id) AS film_tt) AS films FROM actor

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

替代語法：PostgreSQL

超棒的PostgreSQL缺少了一個優(yōu)秀的SQL標準功能，但有一個解決方法：數(shù)組！這次，我們可以使用結構類型，哇哦！所以下面的查詢將在PostgreSQL中返回一個嵌套的行數(shù)組：

SELECTa 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表的許多不同方法可能并不完整。我希望你在這篇文章中能發(fā)現(xiàn)1-2個新的技巧。JOIN只是許多非常有趣的SQL操作中的其中一個。

譯文鏈接：http://www.codeceo.com/article/sql-join-guide.html
英文原文：A Probably Incomplete, Comprehensive Guide to ... to JOIN Tables in SQL
翻譯作者：碼農(nóng)網(wǎng)?– 小峰

總結

以上是生活随笔為你收集整理的SQL 经典回顾：JOIN 表连接操作不完全指南的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。