WHERE Clause vs ON when using JOIN
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
.
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.
SELECT * FROM Foo fINNER JOIN Bar b ON b.BarId = f.BarIdWHERE b.IsApproved = 1;
This is the better form to go. It is easy to read and easy to modify. In the business world this is what you would want to go with. As far as performance they are the same though.