SQL 如何使用自聯結、自然聯結和外聯結

本文介紹自聯結（self-join）、自然聯結（natural join）和外聯結（outer join），包括它們的含義和使用方法。介紹如何使用表別名，如何對被聯結的表使用聚集函數。一、使用表別名 SQL 如何創建計算欄位介紹瞭如何使用別名引用被檢索的表列。給列起別名的語法如下： SELEC ...

一、使用表別名
二、使用不同類型的聯結
三、使用帶聚集函數的聯結
四、使用聯結和聯結條件
五、小結

本文介紹自聯結（self-join）、自然聯結（natural join）和外聯結（outer join），包括它們的含義和使用方法。介紹如何使用表別名，如何對被聯結的表使用聚集函數。

一、使用表別名

SQL 如何創建計算欄位介紹瞭如何使用別名引用被檢索的表列。給列起別名的語法如下：

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
        AS vend_title
FROM Vendors
ORDER BY vend_name;

SQL 除了可以對列名和計算欄位使用別名，還允許給表名起別名。這樣做有兩個主要理由：

縮短 SQL 語句；
允許在一條 SELECT 語句中多次使用相同的表。

請看下麵的 SELECT 語句。它與如何使用 SQL INNER JOIN 聯結兩個或多個表例子中所用的語句基本相同，但改成了使用別名：

SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
 AND OI.order_num = O.order_num
 AND prod_id = 'RGAN01';

可以看到，FROM 子句中的三個表全都有別名。Customers AS C 使用 C 作為 Customers 的別名，如此等等。

這樣，就可以使用省略的 C 而不用全名 Customers。

在這個例子中，表別名只用於 WHERE 子句。其實它不僅能用於 WHERE 子句，還可以用於 SELECT 的列表、ORDER BY 子句以及其他語句部分。

註意：Oracle 中沒有 AS

Oracle 不支持 AS 關鍵字。

要在 Oracle 中使用別名，可以不用 AS，簡單地指定列名即可（因此，應該是 Customers C，而不是 Customers AS C）。

需要註意，表別名只在查詢執行中使用。與列別名不一樣，表別名不返回到客戶端。

二、使用不同類型的聯結

迄今為止，我們使用的只是內聯結或等值聯結的簡單聯結。現在來看三種其他聯結：自聯結（self-join）、自然聯結（natural join）和外聯結（outer join）。

2.1 自聯結

如前所述，使用表別名的一個主要原因是能在一條 SELECT 語句中不止一次引用相同的表。下麵舉一個例子。

假如要給與 Jim Jones 同一公司的所有顧客發送一封信件。這個查詢要求首先找出 Jim Jones 工作的公司，然後找出在該公司工作的顧客。

下麵是解決此問題的一種方法：

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');

輸出：

cust_id      cust_name          cust_contact
--------     --------------     --------------
1000000003   Fun4All            Jim Jones
1000000004   Fun4All            Denise L. Stephens

這是第一種解決方案，使用了子查詢。內部的 SELECT 語句做了一個簡單檢索，返回 Jim Jones 工作公司的 cust_name。

該名字用於外部查詢的 WHERE 子句中，以檢索出為該公司工作的所有雇員（SQL 如何使用子查詢中介紹了子查詢，更多信息請參閱此文章）。

現在來看使用聯結的相同查詢：

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
 AND c2.cust_contact = 'Jim Jones';

輸出：

cust_id     cust_name       cust_contact
-------     -----------     --------------
1000000003  Fun4All         Jim Jones
1000000004  Fun4All         Denise L. Stephens

提示：Oracle 中沒有 AS

Oracle 用戶應該記住去掉 AS。

此查詢中需要的兩個表實際上是相同的表，因此 Customers 表在 FROM 子句中出現了兩次。

雖然這是完全合法的，但對 Customers 的引用具有歧義性，因為 DBMS 不知道你引用的是哪個 Customers 表。

解決此問題，需要使用表別名。Customers 第一次出現用了別名 c1，第二次出現用了別名 c2。現在可以將這些別名用作表名。

例如，SELECT 語句使用 c1 首碼明確給出所需列的全名。

如果不這樣，DBMS 將返回錯誤，因為名為 cust_id、cust_name、cust_contact 的列各有兩個。DBMS 不知道想要的是哪一列（即使它們其實是同一列）。

WHERE 首先聯結兩個表，然後按第二個表中的 cust_contact 過濾數據，返回所需的數據。

提示：用自聯結而不用子查詢

自聯結通常作為外部語句，用來替代從相同表中檢索數據的使用子查詢語句。

雖然最終的結果是相同的，但許多 DBMS 處理聯結遠比處理子查詢快得多。

應該試一下兩種方法，以確定哪一種的性能更好。

2.2 自然聯結

無論何時對錶進行聯結，應該至少有一列不止出現在一個表中（被聯結的列）。

標準的聯結（如何使用 SQL INNER JOIN 聯結兩個或多個表中介紹的內聯結）返回所有數據，相同的列甚至多次出現。

自然聯結排除多次出現，使每一列只返回一次。

怎樣完成這項工作呢？答案是，系統不完成這項工作，由你自己完成它。

自然聯結要求你只能選擇那些唯一的列，一般通過對一個表使用通配符（SELECT *），而對其他表的列使用明確的子集來完成。下麵舉一個例子：

SELECT C.*, O.order_num, O.order_date,
       OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O,
     OrderItems AS OI
WHERE C.cust_id = O.cust_id
 AND OI.order_num = O.order_num
 AND prod_id = 'RGAN01';

提示：Oracle 中沒有 AS

Oracle 用戶應該記住去掉 AS。

在這個例子中，通配符只對第一個表使用。所有其他列明確列出，所以沒有重覆的列被檢索出來。

事實上，我們迄今為止建立的每個內聯結都是自然聯結，很可能永遠都不會用到不是自然聯結的內聯結。

2.3 外聯結

許多聯結將一個表中的行與另一個表中的行相關聯，但有時候需要包含沒有關聯行的那些行。例如，可能需要使用聯結完成以下工作：

對每個顧客下的訂單進行計數，包括那些至今尚未下訂單的顧客；
列出所有產品以及訂購數量，包括沒有人訂購的產品；
計算平均銷售規模，包括那些至今尚未下訂單的顧客。

在上述例子中，聯結包含了那些在相關表中沒有關聯行的行。這種聯結稱為外聯結。

註意：語法差別

需要註意，用來創建外聯結的語法在不同的 SQL 實現中可能稍有不同。

下麵段落中描述的各種語法形式覆蓋了大多數實現，在繼續學習之前請參閱你使用的 DBMS 文檔，以確定其語法。

下麵的 SELECT 語句給出了一個簡單的內聯結。它檢索所有顧客及其訂單：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;

外聯結語法類似。要檢索包括沒有訂單顧客在內的所有顧客，可如下進行：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

輸出：

cust_id        order_num
----------     ---------
1000000001     20005
1000000001     20009
1000000002     NULL
1000000003     20006
1000000004     20007
1000000005     20008

類似如何使用 SQL INNER JOIN 聯結兩個或多個表提到的內聯結，這條 SELECT 語句使用了關鍵字 OUTER JOIN 來指定聯結類型（而不是在 WHERE 子句中指定）。

但是，與內聯結關聯兩個表中的行不同的是，外聯結還包括沒有關聯行的行。

在使用 OUTER JOIN 語法時，必須使用 RIGHT 或 LEFT 關鍵字指定包括其所有行的表（RIGHT 指出的是 OUTER JOIN 右邊的表，而 LEFT 指出的是 OUTER JOIN 左邊的表）。

上面的例子使用 LEFT OUTER JOIN 從 FROM 子句左邊的表（Customers 表）中選擇所有行。

為了從右邊的表中選擇所有行，需要使用 RIGHT OUTER JOIN，如下例所示：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 RIGHT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

註意：SQLite 外聯結

SQLite 支持 LEFT OUTER JOIN，但不支持 RIGHT OUTER JOIN。

幸好，如果你確實需要在 SQLite 中使用 RIGHT OUTER JOIN，有一種更簡單的辦法，這將在下麵的提示中介紹。

提示：外聯結的類型

要記住，總是有兩種基本的外聯結形式：左外聯結和右外聯結。

它們之間的唯一差別是所關聯的表的順序。

換句話說，調整 FROM 或 WHERE 子句中表的順序，左外聯結可以轉換為右外聯結。

因此，這兩種外聯結可以互換使用，哪個方便就用哪個。

還存在另一種外聯結，就是全外聯結（full outer join），它檢索兩個表中的所有行並關聯那些可以關聯的行。

與左外聯結或右外聯結包含一個表的不關聯的行不同，全外聯結包含兩個表的不關聯的行。全外聯結的語法如下：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 FULL OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

註意：FULL OUTER JOIN 的支持

MariaDB、MySQL 和 SQLite 不支持 FULL OUTER JOIN 語法。

三、使用帶聚集函數的聯結

如如何使用 SQL AVG、COUNT、MAX、MIN 和 SUM 彙總數據所述，聚集函數用來彙總數據。

雖然至今為止我們舉的聚集函數的例子都只是從一個表中彙總數據，但這些函數也可以與聯結一起使用。

我們來看個例子，要檢索所有顧客及每個顧客所下的訂單數，下麵的代碼使用 COUNT() 函數完成此工作：

SELECT Customers.cust_id,
       COUNT(Orders.order_num) AS num_ord
FROM Customers
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

輸出：

cust_id        num_ord
----------     --------
1000000001     2
1000000003     1
1000000004     1
1000000005     1

這條 SELECT 語句使用 INNER JOIN 將 Customers 和 Orders 表互相關聯。

GROUP BY 子句按顧客分組數據，因此，函數調用 COUNT(Orders.order_num) 對每個顧客的訂單計數，將它作為 num_ord 返回。

聚集函數也可以方便地與其他聯結一起使用。請看下麵的例子：

SELECT Customers.cust_id,
       COUNT(Orders.order_num) AS num_ord
FROM Customers
 LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

輸出：

cust_id        num_ord
----------     -------
1000000001     2
1000000002     0
1000000003     1
1000000004     1
1000000005     1

這個例子使用左外部聯結來包含所有顧客，甚至包含那些沒有任何訂單的顧客。結果中也包含了顧客 1000000002，他有 0 個訂單，這和使用 INNER JOIN 時不同。

四、使用聯結和聯結條件

在總結討論聯結的這兩篇前，有必要彙總一下聯結及其使用的要點。

註意所使用的聯結類型。一般我們使用內聯結，但使用外聯結也有效。
關於確切的聯結語法，應該查看具體的文檔，看相應的 DBMS 支持何種語法（大多數 DBMS 使用這兩篇中描述的某種語法）。
保證使用正確的聯結條件（不管採用哪種語法），否則會返回不正確的數據。
應該總是提供聯結條件，否則會得出笛卡兒積。
在一個聯結中可以包含多個表，甚至可以對每個聯結採用不同的聯結類型。雖然這樣做是合法的，一般也很有用，但應該在一起測試它們前分別測試每個聯結。這會使故障排除更為簡單。

五、小結

本文是如何使用 SQL INNER JOIN 聯結兩個或多個表的延續，首先介紹瞭如何以及為什麼使用別名，然後討論不同的聯結類型以及每類聯結所使用的語法。

我們還介紹瞭如何與聯結一起使用聚集函數，以及在使用聯結時應該註意的問題。

原文鏈接：https://www.developerastrid.com/sql/sql-joins/

（完）