Menu
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
306
When you use a Left Outer join without an On or Where clause, there is no difference between the On and Where clause. Both produce the same result as in the following. First we see the result of the left join using neither an On nor a Where clause.
You'll use INNER JOIN when you want to return only records having pair on both sides, and you'll use LEFT JOIN when you need all records from the “left” table, no matter if they have pair in the “right” table or not.
You can indeed implement this using LEFT JOIN . For LEFT JOIN you must have ON but you can use ON TRUE .
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.
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
