Filters Joins or Where clauses TSQL

Question

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.

Answer 1

• 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'

Answer 2

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.