PostgreSQL 多表连接

在关系型数据库中，通常将不同的实体和它们之间的联系存储到多个表中。比如员工的个人信息存储在employees表中，而与部门相关的信息存储在departments表中，同时employees表中存在一个外键字段（department_id），引用了departments表的主键（department_id）。当我们想要查看员工的信息时，通常只需要查询员工表；但是如果想要同时查看员工的个人信息以及他/她所在的部门信息，就需要同时查询employees和departments表中的信息。此时，我们需要使用连接查询。连接查询（JOIN）基于两个表中的连接字段将数据行拼接到一起，可以同时返回两个表中的相关数据。

PostgreSQL支持各种类型的SQL连接查询：

• 内连接（INNER JOIN）
• 左外连接（LEFT OUTER JOIN）
• 右外连接（RIGHT OUTER JOIN）
• 全外连接（FULL OUTER JOIN）
• 交叉连接（CROSS JOIN）
• 自然连接（NATURAL JOIN）
• 自连接（Self Join）

其中，左外连接、右外连接以及全外连接统称为外连接（OUTER JOIN）

内连接

内连接用于返回两个表中匹配的数据行，使用关键字INNER JOIN表示，也可以简写成JOIN；以下是内连接的示意图（基于两个表的id进行连接）：

其中，id = 1和id = 3是两个表中匹配（table1.id = table2.id）的数据，因此内连接返回了2行记录。以下是一个内连接查询的示例：

select e.department_id, d.department_id, d.department_name, e.first_name, e.last_name
from employees e
         join departments d on d.department_id = e.department_id

其中，JOIN表示内连接，ON表示连接条件。另外，SELECT列表中的字段名加上了表名限定，例如d.department_id，这是因为两个表中都存在部门编号，必须明确指定需要显示哪个表中的字段。不过，如果某个字段只存在于一个表中，可以省略表名，例如first_name。该查询的结果如下（显示部分内容）

建议在多表查询中，总是加上表名限定，明确字段的来源。对于内连接而言，也可以使用FROM和WHERE表示：

SELECT d.department_id, e.department_id, d.department_name, e.first_name, e.last_name
FROM employees e,
     departments d
WHERE e.department_id = d.department_id;

在这种语法中，多个表在FROM子句中使用逗号进行分割，连接条件使用WHERE子句表示。实际上，在SQL历史中定义了两种多表连接的语法：

• ANSI SQL/86标准使用FROM和WHERE关键字指定表的连接条件。
• ANSI SQL/92标准使用JOIN和ON关键字指定表的连接条件。

推荐使用JOIN和ON，它们的语义更清晰，更符合SQL的声明性。当WHERE子句中包含多个查询条件，又用于指定表的连接关系时，显得比较混乱。

左/右外连接

左外连接返回左表中所有的数据行；对于右表，如果没有匹配的数据，显示为空值。左外连接使用关键字LEFT OUTER JOIN表示，也可以简写成LEFT JOIN。左外连接参考以下示意图（基于两个表的id进行连接）：

查询首先返回左表中的全部数据（id等于1、2、3）。由于id = 2在table2中不存在对应的数据，对于table2中的字段返回空值。由于某些部门刚刚成立，可能还没有员工，因此前面的内连接查询不会显示这些部门的信息。如果想要在连接查询中返回这些部门的信息，需要使用左外连接：

SELECT d.department_id, e.department_id, d.department_name, e.first_name, e.last_name
FROM departments d
         LEFT JOIN employees e ON e.department_id = d.department_id;

右外连接返回右表中所有的数据行；对于左表，如果没有匹配的数据，显示为空值。右外连接使用关键字RIGHT OUTER JOIN表示，也可以简写成RIGHT JOIN。也就是说：

table1 RIGHT JOIN table2

等价于

table2 LEFT JOIN table1

因此，上面的查询也可以使用右外连接来表示：

SELECT d.department_id, e.department_id, d.department_name, e.first_name, e.last_name
FROM departments d
         RIGHT JOIN employees e ON d.department_id = e.department_id;

全外连接

全外连接等效于左外连接加上右外连接，返回左表和右表中所有的数据行。全外连接使用关键字FULL OUTER JOIN表示，也可以简写成FULL JOIN。全外连接的示意图如下（基于两个表的id进行连接）：

查询首先返回两个表中匹配的数据（id等于1和3），对于table1中的id = 2，table2中的对应字段（price）显示为空，对于table2中的id = 5，对应的table1中的字段（name）显示为空。以下查询将员工表和部门表进行全外连接，连接字段为部门编号：

SELECT d.department_id, e.department_id, d.department_name, e.first_name, e.last_name
FROM departments d
         FULL JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IN (176, 177, 178)
   OR d.department_id IN (110, 120, 130);

查询结果不但包含了没有员工的部门，同时还存在一个没有部门的员工。对于外连接，需要注意WHERE条件和ON条件之间的差异：ON条件是针对连接之前的数据进行过滤，WHERE是针对连接之后的数据进行过滤，同一个条件放在不同的子句中可能会导致不同的结果。以下示例将部门表与员工表进行左外连接查询，并且在ON子句中指定了多个条件：

SELECT d.department_id, e.department_id, d.department_name, e.first_name, e.last_name
FROM departments d
         LEFT JOIN employees e ON d.department_id = e.department_id AND e.employee_id = 0;

ON子句指定了一个不存在的员工（e.employee_id = 0），因此员工表不会返回任何数据。但是由于查询指定的是左外连接，仍然会返回部门信息，查询结果如下图所示（显示部分内容）。

对于相同的查询条件，使用WHERE子句的示例如下：

SELECT d.department_id, e.department_id, d.department_name, e.first_name, e.last_name
FROM departments d
         LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = 0;

查询结果没有返回任何数据，因为左连接产生的结果经过WHERE条件（e.employee_id = 0）过滤之后没有任何满足的数据。

交叉连接

当连接查询没有指定任何连接条件时，就称为交叉连接。交叉连接使用关键字CROSS JOIN表示，也称为笛卡尔积（Cartesian product）。两个表的笛卡尔积相当于一个表的所有行和另一个表的所有行两两组合，结果数量为两个表的行数相乘。假如第一个表有100行，第二个表有200行，它们的交叉连接将会产生100 × 200 = 20000行结果。交叉连接的示意图如下（基于两个表的id进行连接）：

 

以下查询通过笛卡儿积返回九九乘法表：

select v || '*' || h || ' = ' || v * h
from generate_series(1, 9) v
         CROSS JOIN generate_series(1, 9) h;

上面的交叉连接也可以使用以下等效写法：

SELECT v || '*' || h || '=' || v * h
FROM generate_series(1, 9) v
         JOIN generate_series(1, 9) h ON TRUE;

自然连接

对于连接查询，如果满足以下条件，可以使用USING替代ON子句，简化连接条件的输入

• 连接条件是等值连接，即t1.col1 = t2.col1；
• 两个表中的列必须同名同类型，即t1.col1和t2.col1的类型相同

由于employees表和departments表中的department_id字段名称和类型都相同，可以使用USING简写前文中的连接查询：

SELECT d.department_id,
       e.department_id,
       d.department_name,
       e.first_name,
       e.last_name
FROM employees e
         JOIN departments d USING (department_id);

USING条件中的字段不需要指定表名，它是公共的字段。如果USING子句中包含了两个表中所有的这种同名同类型字段，可以使用更加简单的自然连接（NATURAL JOIN）表示。例如，employees表和departments表拥有2个同名同类型字段：department_id和manager_id，如果基于这2个字段进行等值连接，可以使用自然连接：

SELECT d.department_id, d.department_name, e.first_name, e.last_name
FROM departments d
         NATURAL JOIN employees e;

自连接

连接（Self Join）是指连接操作符的两边都是同一个表，即把一个表和它自己进行连接。自连接本质上并没有什么特殊之处，主要用于处理那些对自己进行了外键引用的表。例如，员工表中的经理字段（manager_id）是一个外键列，指向了员工表自身的员工编号字段（employee_id）。如果要显示员工姓名以及他们经理的姓名，可以通过自连接实现：

SELECT e.first_name || ', ' || e.last_name AS employee_name,
       m.first_name || ', ' || m.last_name AS manageer_name
FROM employees m
         JOIN employees e ON m.employee_id = e.manager_id;

由于查询多次使用了同一个表（employees），必须为它们指定不同的表别名。查询的结果如下图所示（显示部分内容）

 如果还需要知道员工的职位信息，比如职位名称，可以在连接查询中加上jobs 表。以下是三个表连接查询的示例

SELECT d.department_name, e.first_name || ', ' || e.last_name AS employee_name, j.job_title
FROM departments d
         JOIN employees e ON d.department_id = e.department_id
         JOIN jobs j ON j.job_id = e.job_id;