SQL99 标准中的连接查询

交叉连接

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

  1. SELECT * FROM player CROSS JOIN team

多张表交叉连接:

  1. SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3

自然连接

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

这个相同字段是怎么判断的?

  • player.team_id 与 team.team_id

SQL92 中的写法:

  1. SELECT player_id, a.team_id, player_name, height, team_name FROM player as a, team as b WHERE a.team_id = b.team_id

在 SQL99 中你可以写成：

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

实际上，在 SQL99 中用 NATURAL JOIN 替代了 WHERE player.team_id = team.team_id。

ON 连接

配合 JOIN 关键字.

ON 连接用来指定我们想要的连接条件，针对上面的例子，它同样可以帮助我们实现自然连接的功能：

  • 这里我们指定了连接条件是 ON player.team_id = team.team_id ，相当于是用 ON 进行了 team_id 字段的等值连接。
  1. SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id
  • 当然你也可以 ON 连接进行非等值连接，比如我们想要查询球员的身高等级，需要用 player 和 height_grades 两张表：
  1. # SQL99
  2. SELECT 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
  6. # SQL92
  7. SELECT p.player_name, p.height, h.height_level
  8. FROM player AS p, height_grades AS h
  9. WHERE p.height BETWEEN h.height_lowest AND h.height_highest

一般来说在 SQL99 中，我们需要连接的表会采用 JOIN 进行连接，ON 指定了连接条件，后面可以是等值连接，也可以采用非等值连接。

USING 连接

当我们进行连接的时候，可以用 USING 指定数据表里的同名字段进行等值连接。比如：

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

外连接

SQL99 的外连接包括了三种形式：

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

全外连接实际上就是左外连接和右外连接的结合。在这三种外连接中，我们一般省略 OUTER 不写。

左外连接

SQL92:

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

SQL99:

  1. SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id

右外连接

SQL92:

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

SQL99:

  1. SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id

全外连接

SQL99:

  1. SELECT * FROM player FULL JOIN team ON player.team_id = team.team_id

image.png

需要注意的是 MySQL 不支持全外连接，否则的话全外连接会返回左表和右表中的所有行。当表之间有匹配的行，会显示内连接 (类似自然连接) 的结果。当某行在另一个表中没有匹配时，那么会把另一个表中选择的列显示为空值。

也就是说，全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

自连接

自连接的原理在 SQL92 和 SQL99 中都是一样的，只是表述方式不同。

SQL92:

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

SQL99:

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

SQL99 和 SQL92 的区别

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

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

SQL92 在这三种连接操作中，和 SQL99 还存在着明显的区别:

SQL92 中的 WHERE 和 SQL99 中的 JOIN

  • SQL92 中进行查询时，会把所有需要连接的表都放到 FROM 之后，然后在 WHERE 中写明连接的条件。
  • SQL99 是采用 JOIN 的方式，每次连接一张表，可以多次使用 JOIN 进行连接。

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

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

它的嵌套逻辑类似我们使用的 FOR 循环：

  1. for t1 in table1:
  2.     for t2 in table2:
  3.        if condition1:
  4.            for t3 in table3:
  5.               if condition2:
  6.                   output t1 + t2 + t3

SQL99 在 SQL92 的基础上提供了一些特殊语法

比如 NATURAL JOIN 和 JOIN USING。它们在实际中是比较常用的，省略了 ON 后面的等值条件判断，让 SQL 语句更加简洁。

不同 DBMS 中使用连接需要注意的地方

不同的 DBMS 在使用规范上会存在差异:

  • 不是所有的 DBMS 都支持全外连接
    • MySQL 不支持
  • Oracle 没有表别名 AS
  • SQLite 的外连接只有左连接

一些关于连接的性能问题需要你注意:

  • 控制连接表的数量
  • 在连接时不要忘记 WHERE 语句
  • 使用自连接而不是子查询


总结

image.png

精选留言

lmingzhi

“需要注意的是 MySQL 不支持全外连接，否则的话全外连接会返回左表和右表中的所有行。当表之间有匹配的行，会显示内连接的结果。当某行在另一个表中没有匹配时，那么会把另一个表中选择的列显示为空值。”

请问一下，这里的否则，指的是支持全外连接的数据库吗？

作者回复: 对的 就是讲解下如果支持全外连接的数据库是怎样的结果。Oracle支持全外连接 FULL JOIN，而MySQL不支持，不过想要写全外连接的话，可以用 左外连接 UNION 右外连接，比如： SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id

UNION

SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id