CODEWITHSUNDEEP
sqljoinwhere-clause
logixologist
logixologist

Reputation: 3834

Where criteria in a Join

Is there any performance difference in adding additional criteria to the JOIN clause as opposed to putting it in the WHERE clause? Is there any actual functional difference (getting different results)? Is one better than the other for other reasons?

For example:

SELECT * 
FROM MyTable1 mto
JOIN MyTable2 mto2 ON mto1.key = mto2.key
WHERE somefield = somevalue

VS

SELECT * 
FROM MyTable1 mto
JOIN MyTable2 mto2 ON mto1.key = mto2.key
                   AND somefield = somevalue

Upvotes: 0

Views: 67

Answers (1)

Siyual
Siyual

Reputation: 16917

For INNER JOIN, they are logically equivalent and will produce the same results. I would expect that the query planner would generate identical plans for both queries, but there is a possibility that it could do something wonky with a complex enough query. I, personally, have not encountered this situation, but I can't rule out the optimizer doing weird things.

So, for INNER JOINs the difference is largely readability. But if you suspect the query planner is generating a weird plan, try changing the query and comparing the query plans.

For OUTER JOINs though, that's a different story. Consider you have the following queries instead:

SELECT * FROM MyTable1 mto
LEFT JOIN MyTable2 mto2 on mto1.key = mto2.key
WHERE mto2.somefield = somevalue

VS

SELECT * FROM MyTable1 mto
LEFT JOIN MyTable2 mto2 on mto1.key = mto2.key
AND mto2.somefield = somevalue

In this case, your WHERE clause is acting over the right-hand table of a LEFT OUTER JOIN. The first query will pull back all of the results from the LEFT OUTER JOIN and then filter those via the WHERE clause. This is effectively transforming the OUTER JOIN into an INNER JOIN.

On the other hand, the second query will treat both of the conditions as part of the LEFT OUTER JOIN and return NULL for records that don't meet both conditions of the ON clause, thus retaining its OUTER JOIN nature.

This will also apply to any WHERE condition over a column on the left-hand table of a RIGHT OUTER JOIN.

If the WHERE condition had been over a column on the left-hand table on a LEFT OUTER JOIN, or on the right-hand table on a RIGHT OUTER JOIN, the results would not have been impacted.

Upvotes: 3

Related Questions