left join on、where后面的条件的区别

left join on 即左连接，把left join左边的表的记录全部找出来。

select a.* from a left join b on a.id=b.id where b.end_date=to_date('99991231','yyyymmdd')

 

写在不同的位置，得到的结果可能会不同，这一点要注意。

 

两个表关联条件写在on后面，限制条件写在where后面。－这句话时错误的！

 

SELECT * FROM A LEFT JOIN B ON A.ID=B.ID WHERE B.OTHERKEY=XXXX
与
SELECT * FROM A LEFT JOIN B ON A.ID=B.ID AND B.OTHERKEY=XXXX
是不一样的

后者相当于（出来的结果一样）
SELECT * FROM A LEFT JOIN B ON A.ID=B.ID WHERE B.OTHERKEY=XXXX OR B.OTHERKEY IS NULL

 

进行左连接时，就有涉及到主表、辅表，这时主表条件写在WHERE之后，辅表条件写在ON后面！！！

 

评论

1 楼 iceman1952 2012-06-21  

There can be predicates that involve only one of the joined tables in the ON clause. Such predicates also can be in the WHERE clause in the query. Although the placement of such predicates does not make a difference for INNER[/b] joins, they might [b]cause a different result when OUTER joins are involved.

This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.