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中的外连接:

1. 左外连接: left join或left outer join
2. 右外连接: right join或right outer join
3. 全外连接: 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的写法, 看上去会更简洁与清晰明了.