Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Where should i put the filters on a tsql query

SELECT a.colum FROM A 
JOIN B  ON A.ID =  B.ID AND B.STATUS  = 1

OR

SELECT a.colum FROM A 
JOIN B  ON A.ID =  B.ID 
WHERE  B.STATUS  = 1

Im looking for performance issues or good practices.

share|improve this question
    
    
@Marian: I thought that too, but it is different. Both examples use explicit JOINs already –  gbn Jul 7 '11 at 8:47
    
@gbn: I think you're somewhat right. He's asking where to put the filter, in the JOIN or in the WHERE, while the previous question is what kind of JOIN to use (straight or WHERE). Anyway, the answers in the questions you pointed out should be similar :-). @Goows: Please check the execution plan of both statements, you should have your answer there. –  Marian Jul 7 '11 at 15:27
    
I would suggest you pick up one of Itziks books, like this one: SQL Server 2008 T-SQL Fundamentals He does a great job of explaining how to write queries and how query plans are built. HTH –  SQLRockstar Jul 8 '11 at 0:34

2 Answers 2

up vote 4 down vote accepted
  • There is no performance difference
  • Best practice is explicit JOIN and separate filters (your 2nd example)

Note:

This changes for OUTER JOINs because you'd change it to a inner join

The quick way:

SELECT a.colum
FROM
   A 
   LEFT JOIN 
   B  ON A.ID =  B.ID AND B.STATUS  = 1
WHERE
   a.foo = 'bar'

With separate filters:

SELECT a.colum
FROM
   A 
   LEFT JOIN 
   (SELECT * FROM B WHERE STATUS  = 1) B ON A.ID =  B.ID
WHERE
   a.foo = 'bar'
share|improve this answer
    
I appreciate your answer, this is what I needed. –  Goows Jul 12 '11 at 6:05

It depends. You need to look at the execution plan for your specific queries and see which query is using the more efficient plan. With queries as simple as your example the plans will probably look pretty similar. The more complex the query the more difference you will see in the plan.

share|improve this answer
    
I put only a simple query since the only difference at the end of a report I'm building was this. Execution Plans were exactly the same. Anyway , thanks for the opinion. –  Goows Jul 12 '11 at 6:09

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.