left join 注意事项

有student和class两个表(学生表和班级表),student表的class_id表示class表的id

image.png
image.png


需求是只查询学生1对应的班级(left join),结果 student_name = '学生1'这个查询条件没有生效,查出了所有学生的信息,完整sql为

select * from student s 
left join class c 
on s.class_id = c.id and s.student_name = '学生1'; 

查询结果为


image.png


原因是,a left join b on xxx and xxx这样的sql,on后的条件,仅用于\color{red}{两表连接时},从b表筛选满足条件的记录。
a表的第1行(即学生1)与b表连接时,执行了on后的条件,所以查出了学生1的班级;
a表的第2行(即学生2)与b表连接时,执行了on后的条件,b表查不到满足条件的记录,由于是left join,依然生成了一行记录。

而a left join b on xxx where xxx这样的sql,where条件,用于\color{red}{两表连接后},从结果集筛选条件,因此正确的sql应该是

select * from student s 
left join class c on s.class_id = c.id 
where s.student_name = '学生1'; 


再看下面一个例子
需求是查询所有学生信息,顺便查询其有效的班级信息(left join),class表的valid=0表示该班级有效,sql如下

select * from student s left join class c on s.class_id = c.id where c.valid = 0;

发现一条记录都没查出来
然而事实上,因为是left join,即使没有有效的班级信息,也应该把学生信息查出来
该sql的问题在于,如果学生没有有效班级信息,class部分信息都是null,c.valid也是null,不符合where c.valid = 0,所以被过滤掉了
正确的写法是:

select * from student s left join class c on s.class_id = c.id and c.valid = 0;


总结来说,left join时,被关联表(即left join后面的表)的条件要写在on后还是where后,要视需求而定,不能一概而论。