SQL Query JOIN Performance

Question

Hey all,

I wanted to know what is better performance-wise: to put the conditions in the JOIN? (x JOIN y on x.hi = y.hi AND ....) or maybe to put it under the WHERE, and leave the JOIN only with the crossing condition.

thanks folks

Answer 1

This will depend on the query optimisation engine and how it breaks the query down into its logical query operators.

With MS SQL Server you can view the execution plan but normally there is no difference as the optimiser will see both ways as equivalent.

Answer 2

There is a similar question on StackOverflow here.

It's largely just a matter of which syntax you prefer. My understanding is that the SQL optimizer should be able to evaluate them with little difference in performance.

Answer 3

I don't think either should make a performance difference (Most databases can optimize this sort of thing) but they can make a difference in the results if you are using left joins.

SELECT  a.field1
        , a.field2
        , b.field3
FROM  table1 a
LEFT JOIN table2 b
    ON a.id = b.id
WHERE    a.field5 = 'test'
        AND b.field3 = 1

SELECT  a.field1
        , a.field2
        , b.field3
FROM table1 a
LEFT JOIN table2 b
    ON a.id = b.id AND b.field3 = 1
WHERE a.field5 = 'test'

These two queries do not return the same results as the first query, by putting the conditions in the where clause instead of the join, turns the left join into an inner join.

Answer 4

Well you can not use *= and =* OUTER JOIN syntax in the WHERE clause with SQL Server 2008 R2

I'd hate to see a query mixing the 2 styles