大神能解释oracle on和where的区别吗？

[AT152S]

# 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select s1.score,s2.score from student st left join sc s1 on st.sid=s1.sid and s1.cid='01'  left join sc s2 on st.sid=s2.sid and s2.cid='02' where s1.score>ifnull(s2.score ,0);
select t2.score,t3.score from student t1 left join sc t2 on t1.sid=t2.sid  left join sc t3 on t1.sid=t3.sid  where t2.score>ifnull(t3.score ,0) and t3.cid='02 ' and t2.cid='01';

这两句查的结果不一样，on和where有什么区别吗？

[AT152S]

引用 2 楼 wildwolv 的回复:

条件写在on和where后面当然有区别，写在where后面等于全局限制，限制前面的所有表；写在left join的on后面等于部分限制，只会限制你写的sql里面的sc表。上面你的第二段sql实际效果相当于把left join变成了inner join，结果和第一段当然不一样。 查询"01"课程比"02"课程成绩高课程分数： select s1.sid,s1.score as s1score,ifnull(s2.score ,0) as s2score from (select * from sc where s1.cid='01') s1 left join (select * from sc where s1.cid='02') s2 on S1.sid= S2.sid where s1.score>ifnull(s2.score ,0)

你的第二段 运行不了 根据你的思路 我想到了这个 with t1 as (select * from sc where cid = '01'), t2 as (select * from sc where cid = '02') select t1.score,ifnull(t2.score, 0) from t1 left join t2 on t1.sid = t2.sid where t1.score > ifnull(t2.score, 0);

[AT152S]

如果改成 查询"01"课程比"02"课程成绩高课程分数 也就是没有stunent表 该怎么查？

[wildwolv]

条件写在on和where后面当然有区别，写在where后面等于全局限制，限制前面的所有表；写在left join的on后面等于部分限制，只会限制你写的sql里面的sc表。上面你的第二段sql实际效果相当于把left join变成了inner join，结果和第一段当然不一样。 查询"01"课程比"02"课程成绩高课程分数： select s1.sid,s1.score as s1score,ifnull(s2.score ,0) as s2score from (select * from sc where s1.cid='01') s1 left join (select * from sc where s1.cid='02') s2 on S1.sid= S2.sid where s1.score>ifnull(s2.score ,0)