2

Possible Duplicate:
Condition within JOIN or WHERE

Are the below 2 queries the same?

SELECT u.*
FROM User u
    INNER JOIN Sales s ON (u.userId = s.userId)
WHERE
u.active = 1 AND
s.amount > 0 AND
s.status = 1

versus:

SELECT u.*
FROM User u
    INNER JOIN Sales s ON 
        (u.userId = s.userId AND s.amount > 0 and s.status=1)
WHERE
u.active = 1

Are these 2 queries the same in terms of result set always? Performance considerations?

Community
  • 1
  • 1
loyalflow
  • 14,275
  • 27
  • 107
  • 168
  • They are the same, but with INNER JOIN syntax you should keep join criteria in the ON and filter criteria in the WHERE. Following this convention will make troubleshooting much easier. – Aaron Bertrand Oct 30 '12 at 18:35
  • Of course the logic changes with outer joins, and depends on what you are trying to accomplish. – Aaron Bertrand Oct 30 '12 at 18:35
  • 1
    Possible duplicate of [Condition within JOIN or WHERE](http://stackoverflow.com/questions/1018952/condition-within-join-or-where) or [Filtering using the JOIN instead of WHERE](http://stackoverflow.com/questions/1907335/filtering-using-the-join-instead-of-where) – Aaron Bertrand Oct 30 '12 at 18:38

1 Answers1

2

The SQL Server Query Optimizer is smart enough to understand that this will do the same, so he will generate the same execution plan. That means you will get the same performance. To make it sure you can look at the Execution plan and compare them.

Check out Execution Plan Basics

dknaack
  • 60,192
  • 27
  • 155
  • 202