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子句来查询来自多个表的数据。