Is moving a constraint into a join more efficient than join and a where clause?

Question

I have been trying to test this, but I have doubts about my tests as the timings vary so much.

-- 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 like scenario 2 would be more efficient, but I wasn't sure if SQL server is smart enough to optimize this or not.

Answer 1

Not sure why you think scenario 2 would "logically" be more efficient. On an INNER JOIN everything is basically a filter so SQL Server can collapse the logic to the exact same underlying plan shape. Here's an example from AdventureWorks2012 (click to enlarge):

I prefer separating the join criteria from the filter criteria, so will always write the query in the format on the left. However @HLGEM makes a good point, these clauses are interchangeable in this case only because it's an INNER JOIN. For an OUTER JOIN, it is very important to place the filters on the outer table in the join criteria, else you unwittingly end up with an INNER JOIN and drastically change the semantics of the query. So my advice about how the plan can be collapsed only holds true for inner joins.

If you're worried about performance, I'd start by getting rid of SELECT * and only pulling the columns you actually need (and make sure there's a covering index).

Four months later, another answer has emerged claiming that there usually will be a difference in performance, and that putting filter criteria in the ON clause will be better. While I won't dispute that it is certainly plausible that this could happen, I contend that it certainly isn't the norm and shouldn't be something you use as an excuse to always put all filter criteria in the ON clause.