SQL必知必会学习笔记(三)

学极客时间的 SQL必知必会专栏，做记录（SQL标准、连接）

标准

SQL 有两种主要的标准，SQL92 和 SQL99 ，92 和 99 代表它们提出的时间。

SQL92 中的连接

SQL92 中的 5 种连接方式分别是笛卡尔积、等值连接、非等值连接、外连接（左连接、右连接）和自连接。

笛卡尔积

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。笛卡尔积也称为交叉连接，英文是 CROSS JOIN，它的作用就是可以把任意表进行连接，即使这两张表不相关。但我们通常进行连接还是需要筛选的，因此你需要在连接后面加上 WHERE 子句，也就是作为过滤条件对连接数据进行筛选。比如后面要讲到的等值连接。

等值连接

两张表的等值连接就是用两张表中都存在的列进行连接。我们也可以对多张表进行等值连接。

非等值连接

当我们进行多表查询的时候，如果连接多个表的条件是等号时，就是等值连接，其他的运算符连接就是非等值查询。

外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。两张表的外连接，会有一张是主表，另一张是从表。如果是多张表的外连接，那么第一张表是主表，即显示全部的行，而第剩下的表则显示对应连接的信息。在 SQL92 中采用（+）代表从表所在的位置，而且在 SQL92 中，只有左外连接和右外连接，没有全外连接。

左外连接，就是指左边的表是主表，需要显示左边表的全部行，而右侧的表是从表，（+）表示哪个是从表。右外连接，指的就是右边的表是主表，需要显示右边表的全部行，而左侧的表是从表。

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

自连接

自连接可以对多个表进行操作，也可以对同一个表进行操作。也就是说查询条件使用了当前表的字段。

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

SQL92 中的连接

SQL99中的连接方式分别是： 交叉连接、自然连接、ON连接、USING连接、外连接和自连接

交叉连接

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

SQL: SELECT * FROM player CROSS JOIN team;
SQL: SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3;

自然连接

类似 SQL92 中的等值连接，只是这里是用 NATURAL JOIN 替代了 SQL92 中的 WHERE 等值判断

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

ON 连接

ON 连接用来指定我们想要的连接条件

SQL99：SELECT p.player_name, p.height, h.height_level
FROM player as p JOIN height_grades as h
ON height BETWEEN h.height_lowest AND h.height_highest

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

USING 连接

用 USING 指定数据表里的同名字段进行等值连接。

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

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN USING 可以简化 JOIN ON 的等值连接

外连接

SQL 外连接包括了三种形式（一般省略 OUTER 不写）：

• 左外连接：LEFT JOIN 或 LEFT OUTER JOIN

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

• 右外连接：RIGHT JOIN 或 RIGHT OUTER JOIN

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

• 全外连接：FULL JOIN 或 FULL OUTER JOIN

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

MySQL 不支持全外连接，否则的话全外连接会返回左表和右表中的所有行。当表之间有匹配的行，会显示内连接的结果。当某行在另一个表中没有匹配时，那么会把另一个表中选择的列显示为空值。也就是说，全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

自连接

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

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

小结

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

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

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

1. 不是所有的 DBMS 都支持全外连接。虽然 SQL99 标准提供了全外连接，但不是所有的 DBMS 都支持。不仅 MySQL 不支持，Access、SQLite、MariaDB 等数据库软件也不支持。不过在 Oracle、DB2、SQL Server 中是支持的。
2. Oracle 没有表别名。 AS为了让 SQL 查询语句更简洁，我们经常会使用表别名 AS，不过在 Oracle 中是不存在 AS 的，使用表别名的时候，直接在表名后面写上表别名即可，比如 player p，而不是 player AS p。
3. SQLite 的外连接只有左连接。SQLite 是一款轻量级的数据库软件，在外连接上只支持左连接，不支持右连接，不过如果你想使用右连接的方式，比如table1 RIGHT JOIN table2，在 SQLite 你可以写成table2 LEFT JOIN table1，这样就可以得到相同的效果。

关于连接的性能问题：

1. 控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。
2. 在连接时不要忘记 WHERE 语句。多表连接的目的不是为了做笛卡尔积，而是筛选符合条件的数据行，因此在多表连接的时候不要忘记了 WHERE 语句，这样可以过滤掉不必要的数据行返回。
3. 使用自连接而不是子查询。我们在查看比布雷克·格里芬高的球员都有谁的时候，可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。你可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。