概述

在 SQL 数据查询中，我们常常会需要组合多个表进行数据查询。针对这种组合多个表进行数据查询的场景，我们称之为联表查询。
在本文中，我们将会讲解 SQL 联表查询相关的知识。

SQL92 & SQL99 标准

SQL 有两个主要的标准，分别是 SQL92 和 SQL99。
92 和 99 代表了标准提出的时间，SQL92 就是 92 年提出的标准规范。
一般来说 SQL92 的形式更简单，但是写的 SQL 语句会比较长，可读性较差。而 SQL99 相比于 SQL92 来说，语法更加复杂，但可读性更强。

联表查询

我们还是以上一篇文章用到的 NBA 数据为例进行本文知识的实践数据，下载地址：https://github.com/cystanford/sql_nba_data。
其中，我们主要用到的分别是 player 球员表和 team 球队表，其中，player 表中包含 37 个球员，每个球员信息包含 4 个字段，team 表中包含 3 个球队，每个球队信息包含 2 个字段。

SQL92 中联表查询

SQL92 中支持 5 种连接方式，它们分别是：

  • 笛卡尔积
  • 等值连接
  • 非等值连接
  • 外连接（左连接、右连接）
  • 自连接

下面我们来依次看一下。

笛卡尔积

笛卡尔乘积是一个数学运算，假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合。
我们来看下两张表的笛卡尔积的结果，这是笛卡尔积的调用方式：

  1. SELECT * FROM player, team;

image.png
可以看到，运行结果中一共包含了 37 * 3 = 111 条记录、每条记录包含了 4 + 2 = 6 个字段。
PS：其中，不同表中不重复的字段保留原始字段名称，重名的字段为自动解析为 ${表名}.${字段名} 。
笛卡尔积也称为交叉连接，英文是 CROSS JOIN，它的作用就是可以把任意表进行连接，即使这两张表不相关。

等值连接

两张表的等值连接就是用两张表中都存在的列进行连接。类似的，我们也可以对多张表进行等值连接。
例如，我们可以针对 player 表和 team 表都存在 team_id 这一列，我们可以用等值连接进行查询：

  1. SELECT
  2.   player_id, player.team_id, player_name, height, team_name
  3.   FROM player, team
  4.   WHERE player.team_id = team.team_id;

image.png
可以看到运行结果中一共包含 37 条记录，相当于我们在笛卡尔积的组成结果中进行过滤，找出了其中满足等值条件的全部记录。
此外，在进行等值连接的时候，可以使用表的别名，这样会让 SQL 语句更简洁：

  1. SELECT
  2.   player_id, a.team_id, player_name, height, team_name
  3.   FROM player AS a, team AS b
  4.   WHERE a.team_id = b.team_id;

非等值连接

当我们进行多表查询的时候，如果连接多个表的条件是等号时，就是等值连接，其他的运算符连接就是非等值查询。
假设我们再利用一个身高级别表 height_grades：
image.png
我们知道 player 表中有身高 height 字段，如果想要知道每个球员的身高的级别，可以采用非等值连接查询：

  1. SELECT
  2.   p.player_name, p.height, h.height_level
  3.   FROM player AS p, height_grades AS h
  4.   WHERE p.height BETWEEN h.height_lowest AND h.height_highest;

image.png

外连接

上述的等值连接和非等值连接其实都是查询符合某些条件的记录。而外连接可以查询某一方不满足条件的记录。
两张表的外连接，会有一张是主表，另一张是从表。如果是多张表的外连接，那么第一张表是主表，即显示全部的行，而第剩下的表则显示对应连接的信息。
在 SQL92 中，支持左外连接和右外连接两种外连接。它们分别表示什么含义呢？
左外连接，就是指左边的表是主表，需要显示左边表的全部行，而右侧的表是从表，（+）表示哪个是从表：

  1. SELECT * FROM player, team where player.team_id = team.team_id(+);

右外连接，指的就是右边的表是主表，需要显示右边表的全部行，而左侧的表是从表：

  1. SELECT * FROM player, team where player.team_id(+) = team.team_id;

image.png
那么，外连接和等值连接之类的区别在哪儿呢？
简单来说，针对外连接而言，即使没有满足匹配条件，主表中的全部记录也都会显示，只不过在没匹配到场景下，针对非主表中的其他字段则补充为 null 。

自连接

连接可以对多个表进行操作，也可以对同一个表进行操作。自连接就是说查询条件使用了当前表的字段，即与表自身进行相连。
我们还是以一个示例为例，比如我们想要查看比布雷克·格里芬高的球员都有谁，以及他们的对应身高：

  1. SELECT
  2.   b.player_name, b.height
  3.   FROM player as a , player as b
  4.   WHERE a.player_name = '布雷克-格里芬' and a.height < b.height;

image.png

SQL99 中联表查询

与 SQL92 类似，SQL 99 中支持如下联表查询方式：

  • 交叉连接
  • 自然连接
  • ON连接
  • USING连接
  • 外连接
  • 自连接

下面我们来展开分别了解一下：

交叉连接

交叉连接实际上就是 SQL92 中的笛卡尔乘积，只是这里我们采用的是 CROSS JOIN：

  1. SELECT * FROM player CROSS JOIN team;

自然连接

可以把自然连接理解为 SQL92 中的等值连接，它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

  1. SELECT
  2.   player_id, team_id, player_name, height, team_name
  3.   FROM player NATURAL JOIN team;

可以看到，NATURAL JOIN 实际上相当于自动补充了 WHERE 条件：player.team_id = team.team_id。

ON连接

ON 连接可以用于自定义连接条件。例如，针对上述查询而言，我们也可以改写为 ON 连接的查询方式：

  1. SELECT
  2.   player_id, player.team_id, player_name, height, team_name
  3.   FROM player JOIN team
  4.   ON player.team_id = team.team_id;

除了等值连接场景之外，ON连接也可以用于非等值连接场景：

  1. SELECT
  2.   p.player_name, p.height, h.height_level
  3.   FROM player as p JOIN height_grades as h
  4.   ON height BETWEEN h.height_lowest AND h.height_highest;

USING 连接

进行连接的时候，可以用 USING 指定数据表里的同名字段进行等值连接，我们来看一个示例：

  1. SELECT
  2.   player_id, team_id, player_name, height, team_name
  3.   FROM player JOIN team USING(team_id);

它和自然连接比较类似，只是还需要在 USING 中主动传入匹配的同名字段。

外连接

SQL99 中除了左外连接、右外连接之外，还支持了全外连接，分别表示为：

  • 左外连接：LEFT JOIN
  • 右外连接：RIGHT JOIN
  • 全外连接：FULL JOIN

示例如下：

  1. SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id;
  2. SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id;
  3. SELECT * FROM player FULL JOIN team ON player.team_id = team.team_id;

PS：全外连接会返回左表和右表中的所有行。当表之间有匹配的行，会显示内连接的结果。当某行在另一个表中没有匹配时，那么会把另一个表中选择的列显示为空值。即：全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
PS：MySQL 不支持全外连接。
联表查询也可以用于超过2个表的联合查询场景下，示例如下：

  1. SELECT * FROM
  2.   ( player RIGHT JOIN team t on player.team_id = t.team_id)
  3.   LEFT JOIN player_score on player.player_id = player_score.player_id;

自连接

自连接在 SQL92 和 SQL99 中使用方式类似，只有语法稍有差异：

  1. SELECT
  2.   b.player_name, b.height
  3.   FROM player as a JOIN player as b
  4.   ON a.player_name = '布雷克-格里芬' and a.height < b.height

联表查询注意事项

SQL 连接操作基本上可以分成三种情况：

  • 内连接：将多个表之间满足连接条件的数据行查询出来。它包括了等值连接、非等值连接和自连接。
  • 外连接：会返回一个表中的所有记录，以及另一个表中匹配的行中的数据。它包括了左外连接、右外连接和全连接。
  • 交叉连接：也称为笛卡尔积，返回左表中每一行与右表中每一行的组合。

多表连接使用 SQL99 标准，因为层次性更强，可读性更强，比如：

  1. SELECT ...
  2. FROM table1
  3.     JOIN table2 ON table1和table2的连接条件
  4.         JOIN table3 ON table2和table3的连接条件

不同的数据库对于连接查询的支持都有差异，例如：

  • 不是所有的 DBMS 都支持全外连接，例如 MySQL、MariaDB、Access、SQLite 都不支持；
  • Oracle 不支持通过 AS 设置表别名；
  • SQLite 的外连接只有左连接；

此外，关于联表查询还有一些通用的注意事项，主要集中如下：

  • 控制连接表的数量，连接过多会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。
  • 连接时不要忘记 WHERE/ON 进行过滤，筛选符合条件的数据行；
  • 优先使用子连接，而不是子查询。在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。