提要:在本教程中,您将学习如何使用 PostgreSQL 的 FULL OUTER JOIN 从两个或多个表中查询数据。

PostgreSQL FULL OUTER JOIN 简介

假设,需要对 AB 两张表进行完整的外部连接,可以使用 FULL OUTER JOIN(全外连接)。下面是它的语法:

SELECT * FROM A
FULL [OUTER] JOIN B on A.id = B.id;

OUTER 关键字是可选的。

全外连接将 LEFT JOINRIGHT JOIN 的结果合并为一个结果集进行返回。如果被连接的表里不存在对应的行,全外连接将把相应的行设置为 NULL。对于匹配的行,将包含从两个连接(LEFT JOINRIGHT JOIN)里填充的结果。

下图展示了全外连接:

full-outer-join

结果包括两个表中的匹配行以及不匹配的行。

PostgreSQL FULL OUTER JOIN 示例

首先,创建两个用于演示的 employees(雇员) 表和 departments(部门)表。

CREATE TABLE
IF NOT EXISTS departments (
 department_id serial PRIMARY KEY,
 department_name VARCHAR (255) NOT NULL
);

CREATE TABLE
IF NOT EXISTS employees (
 employee_id serial PRIMARY KEY,
 employee_name VARCHAR (255),
 department_id INTEGER
);

每个部门有零个或多个雇员;而每个雇员隶属于零个或多个部门。

下面的 INSERT 语句 给这两个表添加一些数据:

INSERT INTO departments (department_name)
VALUES
 ('Sales'),
 ('Marketing'),
 ('HR'),
 ('IT'),
 ('Production');

INSERT INTO employees (
 employee_name,
 department_id
)
VALUES
 ('Bette Nicholson', 1),
 ('Christian Gable', 1),
 ('Joe Swank', 2),
 ('Fred Costner', 3),
 ('Sandra Kilmer', 4),
 ('Julia Mcqueen', NULL);

下面,我们分别从这两张表里查询数据:

# SELECT * FROM departments;
 department_id | department_name
---------------+-----------------
             1 | Sales
             2 | Marketing
             3 | HR
             4 | IT
             5 | Production
(5 rows)
# SELECT * FROM employees;
 employee_id |  employee_name  | department_id
-------------+-----------------+---------------
           1 | Bette Nicholson |             1
           2 | Christian Gable |             1
           3 | Joe Swank       |             2
           4 | Fred Costner    |             3
           5 | Sandra Kilmer   |             4
           6 | Julia Mcqueen   |
(6 rows)

下面,我们使用 FULL OUTER JOIN 从这两张表中查询数据:

SELECT
 employee_name,
 department_name
FROM
 employees e
FULL OUTER JOIN departments d ON d.department_id = e.department_id;

结果集既包含了属于某部门的雇员以及每个部门拥有的雇员,同时也包含不属于某部门的雇员和没有雇员的部门:

  employee_name  | department_name
-----------------+-----------------
 Bette Nicholson | Sales
 Christian Gable | Sales
 Joe Swank       | Marketing
 Fred Costner    | HR
 Sandra Kilmer   | IT
 Julia Mcqueen   | NULL
 NULL            | Production
(7 rows)

通过下面的 WHERE 可以查询出没有任务雇员的部门:

SELECT
 employee_name,
 department_name
FROM
 employees e
FULL OUTER JOIN departments d ON d.department_id = e.department_id
WHERE
 employee_name IS NULL;
employee_name | department_name
---------------+-----------------
 NULL          | Production
(1 row)

WHERE 里检查 department_name 是否为 NULL,可以筛选出不属于任何部门的雇员:

SELECT
 employee_name,
 department_name
FROM
 employees e
FULL OUTER JOIN departments d ON d.department_id = e.department_id
WHERE
 department_name IS NULL;
 employee_name | department_name
---------------+-----------------
 Julia Mcqueen | NULL
(1 row)

相关主题