Putting a condition related to a table in JOIN is better?

Question

I have seen this a lot that people are putting table related condition on JOIN clause. See this example,

SELECT i.Name
  FROM sys.TABLES AS tbl
  INNER JOIN sys.indexes AS i
    ON (i.index_id > 0 AND i.is_hypothetical = 0) 
    AND (i.object_id = tbl.object_id)
  WHERE (i.is_unique = 1 AND i.is_disabled = 0) 
    AND (tbl.Name = 'Warehouse')


SELECT i.Name
  FROM sys.TABLES AS tbl
  INNER JOIN sys.indexes AS i
    ON (i.object_id = tbl.object_id)
  WHERE (i.index_id > 0 AND i.is_hypothetical = 0) 
  AND (i.is_unique = 1 AND i.is_disabled = 0) 
  AND (tbl.Name = 'Warehouse')

The query is same but the difference is that, First one is using (i.index_id > 0 AND i.is_hypothetical = 0) in JOIN clause where second one using the same in WHERE clause. Is this impact on query or performance?

Answer 1

As JNK said, for an INNER JOIN, these are going to be the same. You can prove it by comparing actual (don't bother with estimated) execution plans. In this case, they are exactly the same (click to enlarge):

Personally, I like to keep the join conditions and the filter conditions separate. Join conditions go in the ON clause, and filter conditions go in the WHERE clause. This is one of the primary benefits of having explicit INNER JOIN syntax in the first place, which helps to reduce the risk of returning too many rows because of not enough (or even no) join criteria inherent in old-style joins (where filter and join criteria are thrown together) - see this blog post for more details.

JNK is also right though that you need to be careful when you're talking about OUTER JOIN. A simple example:

CREATE TABLE dbo.a(id INT);

CREATE TABLE dbo.b(id INT, name SYSNAME);

INSERT dbo.a(id) VALUES(1),(2),(3);

INSERT dbo.b(id) VALUES(2,N'a'),(3,N'b');

SELECT a.id, b.name 
  FROM dbo.a 
  LEFT OUTER JOIN dbo.b
  ON a.id = b.id 
  AND b.name LIKE N'b%';

SELECT a.id, b.name
  FROM dbo.a 
  LEFT OUTER JOIN dbo.b
  ON a.id = b.id 
  WHERE b.name LIKE N'b%';
--^^^^^ only difference

DROP TABLE dbo.a, dbo.b;

Results:

id    name
----  ----
1     NULL
2     NULL
3     b

id    name
----  ----
3     b

As you can see from the results, the first query still returns all three rows from a (as you would expect), however the second query turns the OUTER JOIN into an INNER JOIN, and only returns the rows from a with a match from b on all conditions. You may want one behavior or the other, so neither of these is "worse" or "wrong," it's just important to understand the different functionality so you know to write the query to get the results you are after.