有student和class两个表(学生表和班级表),student表的class_id表示class表的id
需求是查询学生1以及对应的班级(学生信息必查,班级信息选查),结果 student_name = '学生1'
这个查询条件没有生效,查出了所有学生的信息,完整sql为
select * from student s
left join class c
on s.class_id = c.id and s.student_name = '学生1';
查询结果为
原因是,a left join b on xxx and
xxx这样的sql,on后的条件,仅用于,从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条件,用于,从结果集筛选条件,因此正确的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后,要视需求而定,不能一概而论。