PostgreSQL 完全外连接(FULL OUTER JOIN) 教程

本文学习如何使用PostgreSQL 完全外连接，从两个或多个表中查询数据。

完全外连接介绍

完全外连接合并左连接和右连接的结果。对于连接表不匹配的行，完全连接设置每一列的值为null 。如果一个表的行与另一个表匹配，那么结果将包含来自两个的相应列。

假设需要从A,B两个完全连接查询结果，语法如下：

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

其中 [OUTER] 关键字为可选的。下图使用韦恩图表示完全连接：

图1

其结果包括两个表匹配的记录，也包不匹配的记录。

完全外连接示例

我们创建两个示例表，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
);

每个部门有0个或多个员工，每个员工属于0个或一个部门。下面插入一些示例数据：

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
	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
Bette Nicholson	Sales
Christian Gable	Sales
Joe Swank	Marketing
Fred Costner	HR
Sandra Kilmer	IT
Julia Mcqueen
	Production

我们看到 Julia Mcqueen 没有部门，同时 Production 部门没有所属员工。如果仅查询没有部门的员工，可以增加where条件进行过滤：

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

总结

本文我们学习了完全连接查询，使用完全连接可以返回匹配记录和不匹配记录，避免信息丢失。