Does a constraint move to join more efficiently than join and where clause?

Question

I tried to test this, but I have doubts in my tests, since the timings vary greatly.

-- Scenario 1 SELECT * FROM Foo f INNER JOIN Bar b ON f.id = b.id WHERE b.flag = true; -- Scenario 2 SELECT * FROM Foo f INNER JOIN Bar b ON b.flag = true AND f.id = b.id; 

Logically, it seems that scenario 2 will be more efficient, but I was not sure that the SQL server is smart enough to optimize it or not.

Answer 1

Not sure why you think Scenario 2 is “logical” more efficient . On an INNER JOIN everything is basically a filter, so SQL Server can reduce the logic to the same basic plan form. Here is an example from AdventureWorks2012 ( click to enlarge ):

I prefer to separate the connection criteria from the filter criteria, so I will always write the query in the format on the left. However, @HLGEM makes a good point, these sentences are interchangeable in this case only because it is a INNER JOIN . For OUTER JOIN it is very important to put filters in an external table in the join criteria, otherwise you unwittingly end with an INNER JOIN and radically change the semantics of the query. Therefore, my advice on how the plan can be curtailed is valid only for internal associations.

If you're worried about performance, I'll start by getting rid of SELECT * and pulling out the columns you need (and make sure there is a coverage index).

Four months later, another answer appeared, stating that there would usually be a difference in performance and that the filter criteria in the ON clause would be better. Although I will not argue that it is certainly believable that this can happen, I argue that this, of course, is not the norm and should not be something that you use as an excuse to always put all the filter criteria in sentence ON .

Answer 2

The accepted answer is correct only for your test case.

The answer to the title of the question, as indicated, yes , moving the constraint into a join condition can significantly improve the request and provide. I saw forms like this (but maybe not quite) ...

 select * from A inner join B on B.id = a.id inner join C on C.id = A.id where Bz = 1 and Cz = 2; 

... do not optimize the same plan as the on join equivalents, so I prefer to use the on join constraints as best practice even for simpler cases that could be optimally resolved anyway.