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?

Solution 1

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.

Solution 2

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.

Solution 3

SELECT * FROM Foo f
INNER JOIN Bar b ON b.BarId = f.BarId
WHERE 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.

Comments

[Mario S]

Here's a similar question: stackoverflow.com/questions/2509987/…

[KM.]

The machine will figure it out and optimize it properly. However, for the humans that will need to debug\modify\support your code years from now, keep the filtering conditions in the WHERE and join conditions in the ON.

[Conrad Frix]

@KM. I don't always know how to tell the difference between what's a join condition and a what's a filter. For example in this answer I think its better in the join so is that a "Join condition" then? Here's another example which I don't even know how to rewrite the equivalent where clause.

[KM.]

a join condition is: tableA.column = tableB.column a filter condition is tableA.Column=5. When doing outer joins (LEFT/RIGHT) you must put the filter conditions within the ON or code your WHERE in this manner (tableA.Column=5 OR tableA.Column IS NULL)

[Ste]

Beat me to it. Although as a matter of preference, I'd go with the JOIN as it's more descriptive.

[tugberk]

Thanks! Imagine a situation with 7 or 8 INNER JOINS. Is your answer applicable to those situations as well?

[tugberk]

In the my current situation, I favor the WHERE clause but couldn't avoid wondering if there is a perf diff. Thanks!

[Yuck]

@Ste IMO, it's actually more confusing to put everything in the JOIN. Use JOIN to relate to tables in a query. Use WHERE to filter results. It's when you mix the two and use only one or the other that queries become hard to read.

[Ste]

@Yuck. Fair point and I would agree on the mix becoming unmanageable.

[KeithS]

@Ste: I actually generally prefer the mix of JOIN and WHERE provided the purpose of each keyword is enforced in query writing. The JOIN clauses determine how tables are linked into a "wide" result set, and then the WHERE clause determines the filtering of said results. Given that, I feel it's easier to decipher a query with JOINs and a WHERE than it would be to decipher a query with only JOINs, just as it's easier to decipher JOINs than a query with only a WHERE clause defining both join and filter criteria.

[Ste]

@KeithS: I guess I've never really monitored my usage. I shall report back later and start off a chat about it. :)

[KeithS]

Very good point. If you put filter criteria testing data from an outer join into the outer join itself, you'll get more rows than you expect because all Foos will be returned regardless of the status or existence of Bar. When filtering is specified separately from joining, the rows from the two tables are first joined, and then the filter removes the entire row from the table where the criteria is not met.

[Conrad Frix]

@nonnb Ok but if you corrected the WHERE Clause on the 2nd query to WHERE b.IsApproved = 1 or b.BarId is Null it is the same. Now which one do you do?

[Conrad Frix]

@nonnn um you wouldn't need OR (b.BarId IS NULL) in the left join version only in the WHERE version and you wanted to make it same.