ON or WHERE in JOINS

  • 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.

  • 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?

  • 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"

  • 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?

  • 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