INNER JOIN Where Clause

Question

Is there a difference between doing something like

SELECT * 
FROM table1 INNER JOIN table2 ON table2.ObjectId = table1.table2ObjectId 
WHERE table2.Value = 'Foo'

vs

SELECT * 
FROM table1 INNER JOIN table2 
ON table2.ObjectId = table1.table2ObjectId AND table2.Value = 'Foo'

Yes, I have a rather complex query with many inner joins and simply moving the where condition to be part of the inner join makes a difference. Which is strange because you would think they are the same. — Arizona1911, Aug 09 '13 at 18:56
If the query exclusively consists of inner joins it won't affect the result (except possibly if your query uses non deterministic constructs that can vary dependent on plan). Ask a new question about that containing the queries and results. — Martin Smith, Aug 09 '13 at 19:02
Even though it doesn't matter to the result set, table2.Value = 'Foo' should be in the WHERE clause since it is not really part of what is needed to know in order to join the tables. Not sure if it would help the query optimizer or not. Here is another good related question: http://stackoverflow.com/questions/2509987/which-sql-query-is-faster-filter-on-join-criteria-or-where-clause — Darrin Doherty, Aug 09 '13 at 19:21
@DarrinDoherty thats funny because the result in the first query returns records where both table2.Value are equal and not equal to 'Foo'. While the second query only returns records equal to 'Foo'. — Arizona1911, Aug 09 '13 at 19:27
@Arizona1911 You mentioned the actual problem is occurring in a query with many inner joins... does it also happen with the simple example you posted in the question? Post the actual problematic query if you can. — Darrin Doherty, Aug 09 '13 at 19:36

score 7 · Answer 1 · answered Aug 09 '13 at 18:53

7

Not with an inner join. An outer join is where placement will make a difference.

answered Aug 09 '13 at 18:53

J.T.

1 Answers1