SQL Query JOIN Performance

Question

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

Exact duplicate: http://stackoverflow.com/questions/1018822/inner-join-versus-where-clause-any-difference and about `100` other questions. — Quassnoi, Jan 28 '10 at 14:35

Johnno Nolan · Accepted Answer · 2010-01-28T16:39:06.183

2

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.

edited Jan 28 '10 at 16:39

answered Jan 28 '10 at 14:33

Johnno Nolan

29,228
19
111
160

score 1 · Answer 2 · edited May 23 '17 at 12:03

1

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.

edited May 23 '17 at 12:03

Community

1
1

answered Jan 28 '10 at 14:33

Bernard Dy

1,982
2
29
38

It certainly does make a performance difference for some DBs. Unfortunately, he doesn't say what he's using. – Craig Stuntz Jan 28 '10 at 15:18
You both have good points...my answer comes from exposure to Oracle and SQL Server – Bernard Dy Jan 30 '10 at 02:33

score 0 · Answer 3 · answered Jan 28 '10 at 14:37

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.

I mean on the assumption that the result is the same, which one is faster? — Himberjack, Jan 28 '10 at 15:25

score 0 · Answer 4 · answered Jan 28 '10 at 19:39

0

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

answered Jan 28 '10 at 19:39

gbn

422,506
82
585
676

Heck you shouldn't use those even in SQL Server 2000 as they can be misinterpreted as a cross join on occasion. – HLGEM Jan 28 '10 at 21:10
@HLGEM: We know that of course, but now we have a good reason to justify why we say "no" – gbn Jan 28 '10 at 21:44
1

incorrect results wasn't good enough as a justification? Sure was here. – HLGEM Jan 28 '10 at 23:04

SQL Query JOIN Performance

4 Answers4