Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 27 down vote favorite
5

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?

share|improve this question
1  
Here's a similar question: stackoverflow.com/questions/2509987/… – Mario Apr 24 '12 at 11:52
8  
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. – KM. Apr 24 '12 at 11:58
@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. – Conrad Frix Apr 24 '12 at 17:55
2  
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) – KM. Apr 24 '12 at 18:07

6 Answers

up vote 22 down vote accepted

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.

share|improve this answer
2  
Beat me to it. Although as a matter of preference, I'd go with the JOIN as it's more descriptive. – Ste Apr 24 '12 at 11:52
Thanks! Imagine a situation with 7 or 8 INNER JOINS. Is your answer applicable to those situations as well? – tugberk Apr 24 '12 at 11:52
8  
@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. – Yuck Apr 24 '12 at 11:56
@tugberk yes! :) – aF. Apr 24 '12 at 12:17
@Yuck I'm with you buddy :) – aF. Apr 24 '12 at 12:17
show 3 more comments
up vote 31 down vote

Just be careful of the difference with outer joins

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

Is not the same as

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

Since for 'failed' outer joins on Bar, b.IsApproved will always be NULL and hence filtered out.

Another way of looking at this is that for LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId), that the additional filter (b.IsApproved = 1) will be ignored if the join (b.BarId = f.BarId) fails, because LEFT/RIGHT OUTER JOIN guarantees the LEFT / RIGHT table rows will be retained even if the join fails.

Because of these pitfalls we have a rules in our organisation such as

  • Use simple primary keys only (i.e. no composite keys) - i.e. 1:1 column joining
  • Only JOIN should be used for join conditions, i.e. Don't join in the WHERE clause
  • No additional FILTERS are permitted in the JOIN criteria

This should guarantee that INNER and OUTER joins are always of the form

FROM lhs INNER/LEFT JOIN rhs ON lhs.FK = rhs.PK WHERE OtherFiltersHere

Which avoids a ton of issues. As others have pointed out, this improves readability as well.

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)

share|improve this answer
+1, good point! – KM. Apr 24 '12 at 12:03
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. – KeithS Apr 24 '12 at 15:11
1  
@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 Apr 24 '12 at 16:51
2  
@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. – Conrad Frix Apr 24 '12 at 17:20
Yup - has been a long day. But would still use WHERE ;) – StuartLC Apr 24 '12 at 19:07
up vote 5 down vote 
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.

share|improve this answer
In the my current situation, I favor the WHERE clause but couldn't avoid wondering if there is a perf diff. Thanks! – tugberk Apr 24 '12 at 11:54
up vote 0 down vote

no, should produce the same exec plane, thus the same result

share|improve this answer
up vote 0 down vote

I think performance will rely on indexes more than the SQL.

You can compare them side by side using query analyzer.

share|improve this answer
up vote 0 down vote

I've seem some cases where the optimizer was not smart enough even on recent versions of MSSQL - and the performance difference was monster.

But this is a exception, most of time SQL Server optimizer will solve the problem and get the right plan.

So mantain the policy of using filters on WHERE clause and optimize when needed.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.