sql中join时，where和on的区别

---

前言

最近在学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的一行记录删除