Difference between ON and WHERE clauses in SQL
Posted: Tue Mar 15, 2005 2:28 am
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!
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!