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子句來查詢來自多個表的資料。