on后面的条件:
- 对于左表来说,不管on的条件是否为真,左表都会全部返回
- 对于右表来说,当on的条件为假时,左表数据用null返回
where后面的条件:
- 对连接后的结果表进行过滤
如果将右表的过滤条件放在了where中,就可能会出现左连接后部分数据丢失的情况。
举个例子
有两个表,leader和department
下面看这两个SQL执行结果的区别
select * from department d LEFT JOIN leader l on d.departmentID=l.departmentID and l.departmentID is not null;
select * from department d LEFT JOIN leader l on d.departmentID=l.departmentID where l.departmentID is not null;
再看下面这两个SQL的区别
select * from leader l LEFT JOIN department d on l.departmentID=d.departmentID and l.departmentID is not null;
select * from leader l LEFT JOIN department d on l.departmentID=d.departmentID where l.departmentID is not null;