PostgreSQL 教程: FULL OUTER JOIN 全外连接

九月 1, 2023

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

PostgreSQL FULL OUTER JOIN 简介

假设您要执行表 A 和表 B 的完全外连接。以下说明了FULL OUTER JOIN语法：

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

在此语法中，OUTER关键字是可选的。

完全外连接结合了左连接和右连接的结果。

如果连接表中的行不匹配，则完全外连接会为表中没有匹配行的每一列设置 NULL 值。

如果一个表中的一行与另一个表中的行匹配，则结果行将包含由两个表中的行列填充的列。

下面的维恩图说明了FULL OUTER JOIN操作：

PostgreSQL Join - Full Outer Join

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

PostgreSQL FULL OUTER JOIN 示例

首先，创建两个新表用于演示：employees和departments：

DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS employees;

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

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

每个部门有零个或多个员工，每个员工属于零个或一个部门。

其次，将一些示例数据插入到departments和employees表中。

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);

第三步，从departments和employees表中查询数据：

SELECT * FROM departments;

PostgreSQL Full Outer Join - Departments Table

SELECT * FROM employees;

PostgreSQL Full Outer Join - Employees Table

第四步，使用FULL OUTER JOIN从employees和departments表中查询数据。

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

PostgreSQL Full Outer Join Example

结果集包括属于某个部门的每个员工以及拥有该员工的每个部门。此外，它还包括不属于某个部门的每个员工以及没有员工的每个部门。

要查找没有任何员工的部门，请使用 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;

PostgreSQL Full Outer Join with WHERE clause

结果显示Production部门没有任何员工。

要查找不属于任何部门的员工，请检查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;