WHERE Clause vs ON when using JOIN

Question

Assuming that I have the following T-SQL code:

SELECT * FROM Foo f INNER JOIN Bar b ON b.BarId = f.BarId; WHERE b.IsApproved = 1; 

The following one also returns the same set of rows:

SELECT * FROM Foo f INNER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId); 

This might not be the best case sample here but is there any performance difference between these two?

Answer 1

Just be careful of the difference with outer joins. A query where a filter of b.IsApproved (on the right table, Bar) is added to the ON condition of the JOIN:

SELECT *  FROM Foo f  LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId);  

Is NOT the same as placing the filter in the WHERE clause:

SELECT *  FROM Foo f  LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId) WHERE (b.IsApproved = 1);  

Since for 'failed' outer joins to Bar (i.e. where there is no b.BarId for a f.BarId), this will leave b.IsApproved as NULL for all such failed join rows, and these rows will then be filtered out.

Another way of looking at this is that for the first query, LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId) will always return the LEFT table rows, since LEFT OUTER JOIN guarantees the LEFT table rows will be returned even if the join fails. However, the effect of adding (b.IsApproved = 1) to the LEFT OUTER JOIN on condition is to NULL out any right table columns when (b.IsApproved = 1) is false, i.e. as per the same rules normally applied to a LEFT JOIN condition on (b.BarId = f.BarId).

Update: To complete the question asked by Conrad, the equivalent LOJ for an OPTIONAL filter would be:

SELECT *  FROM Foo f  LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId) WHERE (b.IsApproved IS NULL OR b.IsApproved = 1); 

i.e. The WHERE clause needs to consider both the condition whether the join fails (NULL) and the filter is to be ignored, and where the join succeeds and the filter must be applied. (b.IsApproved or b.BarId could be tested for NULL)

I've put a SqlFiddle together here which demonstrates the differences between the various placements of the b.IsApproved filter relative to the JOIN.

Answer 2

No, the query optimizer is smart enough to choose the same execution plan for both examples.

You can use SHOWPLAN to check the execution plan.

---

Nevertheless, you should put all join connection on the ON clause and all the restrictions on the WHERE clause.