《PostgreSQL 开发指南》第 14 篇多表连接

最新推荐文章于 2023-09-02 17:53:02 发布

不剪发的Tony老师

最新推荐文章于 2023-09-02 17:53:02 发布

阅读量3.3k

点赞数 7

分类专栏： PostgreSQL开发指南文章标签： PostgreSQL 连接查询 JOIN

本文链接：https://blog.csdn.net/horses/article/details/92848291

版权

PostgreSQL开发指南专栏收录该内容

33 篇文章 50 订阅 ¥69.90 ¥99.00

订阅专栏

超级会员免费看

《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 进行连接）：
inner_join
其中，id = 1 和 id = 3 是两个表中匹配（ table1.id = table2.id ）的数据，因此内连接返回了 2 行记录。以下是一个内连接查询的示例：

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

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

该查询的结果如下（显示部分内容）：
join

建议在多表查询中，总是加上表名限定，明确字段的来源。

对于内连接而言，也可以使用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;

该查询的结果如下（显示部分内容）：
left join
右外连接返回右表中所有的数据行；对于左表，如果没有匹配的数据，显示为空值。右外连接使用关键字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 进行连接）：
full join
查询首先返回两个表中匹配的数据（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条件过滤掉了大部分的结果。
full join
查询结果不但包含了没有员工的部门，同时还存在一个没有部门的员工。

对于外连接，需要注意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），因此员工表不会返回任何数据。但是由于查询指定的是左外连接，仍然会返回部门信息，查询结果如下图所示（显示部分内容）。
left join
对于相同的查询条件，使用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 进行连接）：
cross join
以下查询通过笛卡儿积返回九九乘法表：

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

查询的结果如下图所示（显示部分内容）。
cross join
上面的交叉连接也可以使用以下等效写法：

SELECT v || '*' || h || '=' || v*h
  FROM generate_series(1,9) v, 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;

查询的结果如下图所示（显示部分内容）。
natural join
查询返回的员工满足以下条件：他/她的经理也是他/她所在部门的经理。

自连接

连接（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），必须为它们指定不同的表别名。查询的结果如下图所示（显示部分内容）。
self join
如果还需要知道员工的职位信息，比如职位名称，可以在连接查询中加上 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;

查询结果如下（显示部分内容）：
join
人生本来短暂，你又何必匆匆！点个赞再走吧！

不剪发的Tony老师

关注

7
点赞
踩
20

收藏

觉得还不错? 一键收藏
打赏
2
评论
《PostgreSQL 开发指南》第 14 篇多表连接

连接查询（join）基于两个表中的连接字段将数据行拼接到一起，可以同时返回两个表中的相关数据。PostgreSQL 支持各种类型的 SQL 连接查询：内连接（INNER JOIN）、左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN）、交叉连接（CROSS JOIN）、自然连接（NATURAL JOIN）、自连接。
复制链接

扫一扫