Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON?
left-join, sql, sql-server, t-sql
Solution
The `on` clause is used when the `join` is looking for matching rows. The `where` clause is used to filter rows after all the joining is done.
An example with Disney toons voting for president:
declare @candidates table (name varchar(50));
insert @candidates values
('Obama'),
('Romney');
declare @votes table (voter varchar(50), voted_for varchar(50));
insert @votes values
('Mickey Mouse', 'Romney'),
('Donald Duck', 'Obama');
select *
from @candidates c
left join
@votes v
on c.name = v.voted_for
and v.voter = 'Donald Duck'
This still returns `Romney` even though `Donald` didn't vote for him. If you move the condition from the `on` to the `where` clause:
select *
from @candidates c
left join
@votes v
on c.name = v.voted_for
where v.voter = 'Donald Duck'
`Romney` will no longer be in the result set.
Problem
I'm experiencing a very confusing situation that makes me question all my understanding of joins in SQL Server. ``` SELECT t1.f2 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 AND cond2 AND t2.f3 > something ``` Does not give the same results as : ``` SELECT t1.f2 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 AND cond2 WHERE t2.f3 > something ``` Can please someone help by telling if this two queries are supposed to be equivalent or not? Thx