SQL FULL OUTER JOIN子句

在本教學中，您將學習如何使用SQL FULL OUTER JOIN子句查詢來自多個表的數據。

1. SQL FULL OUTER JOIN子句簡介

理論上，完全外連接是左連接和右連接的組合。 完整外連接包括連接表中的所有行，無論另一個表是否具有匹配的行。

如果連接表中的行不匹配，則完整外連接的結果集包含缺少匹配行的表的每列使用NULL值。 對於匹配的行，結果集中包含從連接表填充列的行。

以下語句說明了兩個表的完全外連接的語法：

SELECT column_list
FROM A
FULL OUTER JOIN B ON B.n = A.n;

請注意，OUTER關鍵字是可選的。
以下圖說明瞭兩個表的完整外連接。

2. SQL FULL OUTER JOIN示例

讓我們舉一個使用FULL OUTER JOIN子句來看它是如何工作的例子。

首先，創建兩個新表：用於演示的baskets和fruits表。 每個籃子存儲零個或多個水果，每個水果可以存儲在零個或一個籃子中。

-- 創建表1
CREATE TABLE fruits (
    fruit_id INTEGER PRIMARY KEY,
    fruit_name VARCHAR (255) NOT NULL,
    basket_id INTEGER
);
-- 創建表2
CREATE TABLE baskets (
    basket_id INTEGER PRIMARY KEY,
    basket_name VARCHAR (255) NOT NULL
);

其次，將一些樣本數據插入到baskets和fruits表中。

-- 插入數據1
INSERT INTO baskets (basket_id, basket_name)
VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C');

-- 插入數據2
INSERT INTO fruits (
    fruit_id,
    fruit_name,
    basket_id
)
VALUES
    (1, 'Apple', 1),
    (2, 'Orange', 1),
    (3, 'Banana', 2),
    (4, 'Strawberry', NULL);

第三，以下查詢返回籃子中的每個水果和每個有水果的籃子，但也返回不在任何籃子中的每個水果和每個沒有任何水果的籃子。

SELECT
    basket_name,
    fruit_name
FROM
    fruits
FULL OUTER JOIN baskets ON baskets.basket_id = fruits.basket_id;

執行上面示例代碼，得到以下結果 -

 basket_name | fruit_name
-------------+------------
 A           | Apple
 A           | Orange
 B           | Banana
 (null)      | Strawberry
 C           | (null)

如上所見，籃子C沒有任何水果，Strawberry不在任何籃子裏。

您可以將WHERE子句添加到使用FULL OUTER JOIN子句的語句中以獲取更具體的資訊。

例如，要查找不存儲任何水果的空籃子，請使用以下語句：

SELECT
    basket_name,
    fruit_name
FROM
    fruits
FULL OUTER JOIN baskets ON baskets.basket_id = fruits.basket_id
WHERE
    fruit_name IS NULL;

執行上面示例代碼，得到以下結果 -

 basket_name | fruit_name
-------------+------------
 C           | (null)
(1 row)

同樣，如果想查看哪個水果不在任何籃子中，請使用以下語句：

SELECT
    basket_name,
    fruit_name
FROM
    fruits
FULL OUTER JOIN baskets ON baskets.basket_id = fruits.basket_id
WHERE
    basket_name IS NULL;

執行上面示例代碼，得到以下結果 -

basket_name | fruit_name
-------------+------------
(null)       | Strawberry
(1 row)

在本教學中，我們演示了如何使用SQL FULL OUTER JOIN子句來查詢來自多個表的數據。