前言
最近在学sql的join方法时候，用到了on的关键字，因此看了一下和where的区别，特意记在这里，以备之后遗忘。
一、SQL之Left join、right join和inner join的区别
- left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
- right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
- inner join(等值连接) 只返回两个表中联结字段相等的记录
- join的本质是两个表中的记录做笛卡尔积，on后面的条件是对这个笛卡尔积做一个过滤形成一张临时表，如果没有where就直接返回结果，如果有where就对上一步的临时表再进行过滤。当联表过多或联行数很多的表时，会严重影响操作的效率，因此在联表之前尽可能用where条件进行筛选。
二、举例说明
1.准备两张表
person表(id, name, age)
account表(id, account)
2.inner join
select * FROM person p inner join account a on p.id = a.id;
结果一
select * FROM person p inner join account a on p.id = a.id and p.id != 1;
结果二
select * FROM person p inner join account a on p.id = a.id where p.id != 1;
结果三
- 从结果中可以看出，在做inner join操作时，on和where筛选的结果是一致的
- 虽然结果相同，但应该养成最小化join的习惯，可以优化为
select *
FROM (select * FROM person where id != 1) p
inner join account on account.id = p.id;
3.left join
select * FROM person p left join account a on p.id = a.id ;
结果一
select * FROM person p left join account a on p.id = a.id and p.id != 1;
结果二
select * FROM person p left join account a on p.id = a.id where p.id != 1;
结果三
- 从结果来看，在做left join时，区别很大，left join会保留左表中的全部元素，根据on的条件对右表进行筛选，最终结果中不含account表中id=1的一行
- where条件是对最终结果进行筛选，因此person表中id=1的一行记录也不存在
4.right join
select * FROM person p inner join account a on p.id = a.id;
结果一
select * FROM person p right join account a on p.id = a.id and p.id != 1;
结果二
select * FROM person p right join account a on p.id = a.id and p.id != 1;
结果三
- 从结果来看，right join时，on和where做筛选同样区别很大，类似于left join
- right join是以右表为主，所以不含id=4的记录
- 结果二中看出，on做筛选时，会将做笛卡尔积的临时表中，person表中id=1的记录删除
- where是将join后的结果进行筛选，因此将id=1的一行记录删除