SQL中如何連接JOIN表

本篇文章給大家分享的是有關SQL中如何連接JOIN表，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

CROSS JOIN(交叉連接)

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

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

想象一下，我們有以下數據： 

現在結果將如下所示：

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

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

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

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

EQUI JOIN

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

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

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

上述查詢選擇了所有演員及其電影。有兩個INNER  JOIN操作，一個將actors連接到film_actor關系表中的相應條目(因為演員可以演許多電影，而電影可以有許多演員出演)，并且另一個連接每個film_actor與關于電影本身的附加信息的關系。

特點

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

因此，換句話說：

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

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

替代語法：USING

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

SELECT *FROM actorJOIN 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 aJOIN 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 actorNATURAL JOIN film_actorNATURAL JOIN film

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

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

SELECT *FROM actorJOIN 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部門：

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

正式地說，LEFT OUTER JOIN是一個像這樣帶有UNION的INNER 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

將產生：

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

FULL OUTER JOIN

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

讓我們再來看一下這個數據：

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

現在結果看起來像這樣：

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

備選語法：“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  fWHERE 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”?，F在，每個(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 actorWHERE 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 actorWHERE UNKNOWN

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

當然，這樣還不夠：

不要在SQL中使用NOT IN謂詞，除非你添加常量，非空值。

——Lukas Eder?，F在。

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

(危險)替代語法：LEFT JOIN / IS NULL

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

SELECT *FROM actor aLEFT JOIN film_actor faUSING (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關鍵字的例子：

事實上，與其在所有部門和所有日子之間進行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 fON 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 ASRETURN   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; } @Entityclass Film { }

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

備代語法：Oracle

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

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

替代語法：PostgreSQL

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

SELECT   a AS actor,   array_agg(     f   ) AS filmsFROM actor AS aJOIN 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表，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。