Thursday, 4 December 2014

Difference between queries using "where clause" and "on clause"

While writing queries we should always have clear picture of its format , for example some people may familiar with old join conditions using "where clause" while the rest may familiar with "on clause" .

My point is we need to understand the behavior of both the format :

Inner join :

Select * from table1 inner join table2 on table1.col1=table2.col1 is equivalent to select * from table1,table2 where table1.col1=table2.col1 


Other Join types other than Inner Join:

Select * from table1 left outer join table2 on table1.col1=table2.col1  is equivalent to select * from table1,table2 where table1.col1=table2.col1 (+)

but 

Select * from table1 left outer join table2 on table1.col1=table2.col1  
and table2.col2 like 'A%' is not equivalent to 

select * from table1,table2 where table1.col1=table2.col1 (+)
and  table2.col2 like 'A%'  .

In the statement using "where clause" , the second condition table2.col2 will be applied as filter rather than the left outer join condition .

This fashion follows for the Full Outer Join and Right Outer Join.



No comments:

Post a Comment