Difference between ON and WHERE clauses in SQL

[vigneshra]

Hi

If the join is an inner join, the condition we put in ON clause is equivalent to putting the same condition in WHERE clause but when the condition is left-outer join or right-outer join it differs based on the condition we give. Am I right?

For example,
if the SQL is like this,

Select T1.*

from table1 T1 LEFT OUTER JOIN table2 T2

ON T1.col1=T2.col1
AND T1.col2 is NULL

is equivalent to writing

Select T1.*

from table1 T1 LEFT OUTER JOIN table2 T2

ON T1.col1=T2.col1

WHERE T1.col2 is NULL

But in case, if the condition is T2.col is NULL then the output will differ!

Is my understanding right on this? Please help!

[roy]

Hi,
using ANSI syntax
when performing an outer join you need to give a join criteria which upon failiour of join will result in the columns comming from the "weaker" table/s having no values in them (NULLs)
this criteria is specified via the ON clause.

now the WHERE clause will determin what rows will be returned from the query at hand.

there for what fails to comply with the where part will not be a part of the result set.

now this, as you found out, only effect outer joins, since in inner joins the 2 are identical in result.

IHTH,