MySQL查询语句中where与on的用法

on的用法

on在mysql查询语句中,是用在连接语句中的,例如:左外(left join),右外(right join).
当使用on时,无论on后面的判断条件是非为真,都会显示左边表的信息

例:

select *
from Student s left join (select SId s_id from SC group by SId having count(CId)=3) sc_3
on s.SId=sc_3.s_id ;

运行结果

where的用法

where是mysql查询语句中,用来过滤条件的,当where后的条件为真,则返回过滤后的信息

select *
from (select *
      from Student s left join (select SId s_id from SC group by SId having count(CId)=3) sc_3
      on s.SId=sc_3.s_id) sc_left_stu
where sc_left_stu.s_id is null ;

运行结果

细节

当在on条件下使用sc_3.s_id is null 时,不会筛选出s_id为空的数据,而是显示所有,并将s_id 全为空

select *
from Student s left join (select SId s_id from SC group by SId having count(CId)=3) sc_3
on s.SId=sc_3.s_id and sc_3.s_id is null ;