February 27, 2008 at 8:16 am
Which one is better do you think, limiting records in ON conditions or in WHERE clause;
[font="Courier New"]SELECT * FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id AND t2.aField > 10[/font]
or
[font="Courier New"]SELECT * FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.aField > 10[/font]
in my opinion the second one is better. But I see lots of examples like the first one even in articles on this site.
February 27, 2008 at 8:24 am
Those two queries won't return the same data. You're asking SQL for two entirely different things.
The first query is equivalent to :
select * from T1
left outer join
(select * from T2 where afield>10) T2small on t1.id=t2small.id
the second one is equivalent to
SELECT * FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE t2.aField > 10
Entirely different results.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 27, 2008 at 8:34 am
I see the first one, but why the second query works as a INNER JOIN? I use a LEFT JOIN.
As I know (I'm not an expert btw) the difference is "join the tables, which already filtered (ON t2.field > 10)" and "join the tables then apply the WHERE filter"
February 27, 2008 at 8:49 am
On #2: you're not allowing for the fact that T2.afield might be null (when the OUTER part might kick in), so the optimizer will "overrule" your determination of OUTER, and turn it into a INNER.
The WHERE clause (as structured right now) "turns" the OUTER into an INNER.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 3:06 am
I understood your point, you're completly right, thanks a lot.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply