SQL中的连接(极客时间)
SQL中的连接
关系型数据库的核心之一就是连接, 而在不同的标准中, 连接的写法上可能有区别, 最为主要的两个SQL标准就是SQL92和SQL99了, 后面的数字表示的是标准提出的时间.
SQL92中的连接
案例使用的表是球员表, 球队表和身高级别表, 下载: https://github.com/cystanford/sql_nba_data
笛卡尔积
笛卡尔积是一个数学运算, 假设两个集合X和Y, 那么X和Y的笛卡尔积就是X和Y的所有可能组合.
SQL: SELECT * FROM player, team; // 笛卡尔积
简单的来说就是两个表的数据行的乘积就是结果行.
等值连接
等值连接就是用两张表都存在的列进行连接, 可以对多张表进行等值连接.
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 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中只有左外连接和右外连接, 没有全外连接.
左外连接就是左边的表是主表, 需要显示全部行, 右侧的表是从表. 右外连接则相反.
SELECT * FROM player, team where player.team_id = team.team_id(+); // 左外连接
SELECT * FROM player, team where player.team_id(+) = team.team_id; // 右外连接
不过笔者在测试的时候发现(+)是不能使用的, 所以请参考下面的SQL99的写法
SQL：SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id; // 左外连接
SQL：SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id; // 右外连接
自连接
自连接可以对多个表进行操作，也可以对同一个表进行操作。也就是说查询条件使用了当前表的字段(不过按照笔者的理解就是自己和自己连接查询)。
SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克 - 格里芬' and a.height < b.height; // 自连接查询比布雷克 - 格里芬身高高的球员
SQL99的连接
还是上面的表, 看看SQL99中是如何使用连接的.
交叉连接
交叉连接也就是SQL92中的笛卡尔积, 这里采用CROSS JOIN, 如下可以得到笛卡尔积的结果:
SELECT * FROM player CROSS JOIN team; // 使用CROSS JOIN实现笛卡尔积
自然连接
自然连接就是SQL92中的等值连接, 会自动查询连接表中所有相同的字段, 进行等值连接.
SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team ; // 使用NATURAL JOIN可以自动查询连接表的相同字段
这个有缺点有优点, 缺点在于如果设计表的时候设计不规范, 可能造成两张表字段名相同但表达意义不同, 这个时候直接使用NATURAL JOIN出来的结果可能就不是想要的结果. 当然优点在于写法简单
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连接实现自然连接
ON连接也可以进行非等值连接, 如下
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; // 查询球员的身高等级
USING连接
USING指定数据表里同名字段进行等值连接
SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id);
优点缺点与用NATURAL JOIN一样
外连接
SQL99中的外连接:
- 左外连接: LEFT JOIN或LEFT OUTER JOIN
- 右外连接: RIGHT JOIN或RIGHT OUTER JOIN
- 全外连接: FULL JOIN或FULL OUTER JOIN
SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id; // 左外连接
SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id; // 右外连接
SELECT * FROM player FULL JOIN team ON player.team_id = team.team_id; // 全外连接
需要注意的是 MySQL 不支持全外连接，否则的话全外连接会返回左表和右表中的所有行。当表之间有匹配的行，会显示内连接的结果。当某行在另一个表中没有匹配时，那么会把另一个表中选择的列显示为空值。
也就是说，全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
自连接
自连接在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的写法, 看上去会更简洁与清晰明了.
