王秀文的博客

MySQL 连接

发布
分类
MySQL
篇幅
2512 字 / 6 分钟
阅读
362

SQL92 连接

笛卡尔积

笛卡尔乘积是一个数学运算。假设我有两个集合X和Y，那么X和Y的笛卡尔积就是X和Y的所有可能组合，也就是第一个对象来自于X，第二个对象来自于Y的所有可能。
笛卡尔积也称为交叉连接，英文是CROSS JOIN，它的作用就是可以把任意表进行连接，即使这两张表不相关。

SELECT * FROM player, team;

SQL92 等值连接

SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id;
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; -- 使用表别名
SELECT player_id, player.team_id, player_name, height, team_name FROM player AS a, team AS b WHERE a.team_id = b.team_id； -- 使用了表的别名，在查询字段中就只能使用别名进行代替，不能使用原有的表名，否则会报错

SQL92 非等值连接

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

SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest; -- 查询每个球员的身高的级别

SQL92 外连接

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

什么是左外连接，什么是右外连接呢？

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

SELECT * FROM player, team where player.team_id = team.team_id(+) -- SQL92
SQL：SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id; -- SQL99

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

SELECT * FROM player, team where player.team_id(+) = team.team_id -- SQL92
SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id -- sql99

注意, LEFT JOIN和RIGHT JOIN只存在于SQL99及以后的标准中，在SQL92中不存在，只能用（+）表示。

SQL92 自连接

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

SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height -- 查看比布雷克·格里芬高的球员都有谁，以及他们的对应身高

如果不用自连接的话，需要采用两次SQL查询。首先需要查询布雷克·格里芬的身高。

SELECT height FROM player WHERE player_name = '布雷克-格里芬';

然后再查询比2.08高的球员都有谁，以及他们的对应身高：

SELECT player_name, height FROM player WHERE height > 2.08;

eg1:

-- team表中一共有3支球队，现在这3支球队需要进行比赛，请用一条SQL语句显示出所有可能的比赛组合
SELECT a.team_name as '主队', b.team_name as '客队' FROM team AS a,team AS b WHERE a.team_id != b.team_id; -- 分主客场
SELECT t1.team_name as '主队', t2.team_name as '客队' FROM team as t1 LEFT JOIN team as t2 on t1.team_id<> t2.team_id; -- 分主客场
SELECT t1.team_name as '队伍1' , t2.team_name as '队伍2' FROM team as t1,team as t2 where t1.team_id <  t2.team_id;

SQL99 连接

SQL99 交叉连接

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

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

自然连接(等同于 SQL92 等值连接)

在 SQL99 中用 NATURAL JOIN 替代了 WHERE player.team_id = team.team_id:

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； -- SQL92
SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team;  -- SQL99

ON连接

ON连接用来指定连接条件, 它同样可以实现自然连接：

SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id; -- 相当于 ON 进行了 team_id 字段的等值连接

-- 查询球员的身高等级
SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest; -- SQL92

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

USING连接

-- 以下两个查询等价，使用JOIN USING可以简化JOIN ON的等值连接
SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id);
SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id;

外连接

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

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

左外连接

SELECT * FROM player, team where player.team_id = team.team_id(+) -- SQL92
SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id -- SQL99

右外连接

SELECT * FROM player, team where player.team_id(+) = team.team_id -- SQL92
SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id -- SQL99

全外连接

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

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

Oracle支持全外连接 FULL JOIN，而 MySQL 不支持。否则的话 MySQL 全外连接会返回左表和右表中的所有行。
不过 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
连接方式 含义
内连接 最早的一种连接。还可以被称为普通连接或者自然连接
自然连接 是一种特殊的等值连接，他要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。要求是两个这两个关系中参与比较的属性列必须是同名、同属性。
外连接 左连接，右连接和全连接
自连接 连接的两个表都是同一个表

自连接

自连接的原理在SQL92和SQL99中都是一样的，只是表述方式不同。
查看比布雷克·格里芬身高高的球员都有哪些:

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

SQL99和SQL92的区别

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

SQL92中的WHERE和SQL99中的JOIN区别：

  • 在SQL92中进行查询时，会把所有需要连接的表都放到FROM之后，然后在WHERE中写明连接的条件。而SQL99在这方面更灵活，它不需要一次性把所有需要连接的表都放到FROM之后，而是采用JOIN的方式，每次连接一张表，可以多次使用JOIN进行连接。
    建议多表连接使用SQL99标准，因为层次性更强，可读性更强，比如：
SELECT ...
FROM table1
    JOIN table2 ON table1和table2的连接条件
        JOIN table3 ON table2和table3的连接条件

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

for t1 in table1:
    for t2 in table2:
       if condition1:
           for t3 in table3:
              if condition2:
                  output t1 + t2 + t3
  • SQL99在SQL92的基础上提供了一些特殊语法，比如NATURAL JOIN和JOIN USING。它们在实际中是比较常用的，省略了ON后面的等值条件判断，让SQL语句更加简洁。

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

语法问题

  • 不是所有的DBMS都支持全外连接

    虽然SQL99标准提供了全外连接，但不是所有的DBMS都支持。不仅MySQL不支持，Access、SQLite、MariaDB等数据库软件也不支持。不过在Oracle、DB2、SQL Server中是支持的。

  • Oracle没有表别名AS

    为了让SQL查询语句更简洁，我们经常会使用表别名AS，不过在Oracle中是不存在AS的，使用表别名的时候，直接在表名后面写上表别名即可，比如player p，而不是player AS p。

  • SQLite的外连接只有左连接

    SQLite 是一款轻量级的数据库软件，在外连接上只支持左连接，不支持右连接，不过如果你想使用右连接的方式，比如table1 RIGHT JOIN table2，在SQLite你可以写成table2 LEFT JOIN table1，这样就可以得到相同的效果。

性能问题：

  • 控制连接表的数量

    多表连接就相当于嵌套for循环一样，非常消耗资源，会让SQL查询性能下降得很严重，因此不要连接不必要的表。在许多DBMS中，也都会有最大连接表的限制。

  • 在连接时不要忘记WHERE语句

    多表连接的目的不是为了做笛卡尔积，而是筛选符合条件的数据行，因此在多表连接的时候不要忘记了WHERE语句，这样可以过滤掉不必要的数据行返回。

  • 使用自连接而不是子查询

    我们在查看比布雷克·格里芬高的球员都有谁的时候，可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多DBMS的处理过程中，对于自连接的处理速度要比子查询快得多。你可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分DBMS中都对自连接处理进行了优化。

面向集合的思考

set game
关于面向集合的思考，《Oracle SQL高级编程》

MySQL