2

I was wondering about the difference : 

select * 
from Table1 T1 
left join Table1 T2 on T1.id = T2.id + 1 and (T2.id > 3)

vs

select * 
from Table1 T1 
left join Table1 T2 on T1.id = T2.id + 1 
where (T2.id > 3)
Royi Namir
  • 144,742
  • 138
  • 468
  • 792
  • 1
    @marc_s - I read it to be a typo and intended to be T2.id, if it means something else I'll have to hit edit :) – Andrew Nov 16 '11 at 15:38
  • You answer might be [here](http://stackoverflow.com/questions/1018822/inner-join-versus-where-clause-any-difference) – misha Nov 16 '11 at 15:33

2 Answers2

4

There is a significant difference.

select * from Table1 T1 left join  Table1 T2 on T1.id=T2.id+1     where    (T2>3

The where clause is changing the left join to an inner join, since it is not allowing for the null value to be returned from the left join. This means all the rows where the left join doesnt find a matching record would be excluded, since the null value returned is compared to 3 and it discards the row (this is the same effect as making it an inner join)

The first statement is applying the filter within the join:

select * from Table1 T1 left join  Table1 T2 on T1.id=T2.id+1     and     (T2>3)

This means it will take effect and filter the rows that can be joined to using the left join, but this will not cause rows to be discarded when the left join fails to find a matching row.

Andrew
  • 26,629
  • 5
  • 63
  • 86
2

In an INNER JOIN it doesn't matter where yuo put the condition.
It only makes a difference in a LEFT JOIN or RIGHT JOIN.

I wrote a detailed answer to a similar question some time ago, explaining what SQL Server does different in each of your two cases.

You can check it out here:
What is the difference in these two queries as getting two different result set?

Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182