Does it matter where I check part of a composite key when joining two tables in SQL Server?

Question

I'm joining two tables on a composite key, and I'm wondering if it matters where I compare the corresponding columns when I do the join.

Say I have a table, TableA, with columns ColAFoo, ColAFoo2, and ColABar. TableA has a composite primary key comprising ColAFoo and ColAFoo2 (PK_TableA).

I also have TableB, with ColBFoo, ColBFoo2, and ColBOther. TableB's columns ColBFoo and ColBFoo2 comprise a foreign key to TableA's primary key (FK_TableA_TableB).

I need to join the two tables on the key. Is there a difference between the following three (extraordinarily contrived) statements in terms of performance?

SELECT *
  FROM TableA a
  JOIN TableB b
    ON  a.ColAFoo = b.ColBFoo
        AND a.ColAFoo2 = b.ColBFoo2

SELECT *
  FROM TableA a
  JOIN TableB b
    ON  a.ColAFoo = b.ColBFoo
  WHERE a.ColAFoo2 = b.ColBFoo2

-- this one is a little /too/ contrived, apparently (see comments)
SELECT *
  FROM TableA a
  JOIN TableB b
  WHERE a.ColAFoo = b.ColBFoo
        AND a.ColAFoo2 = b.ColBFoo2

Answer 1

if you have only an INNER JOIN then the 3 are all the same.
for LEFT/RIGHT joins they are quite different.

Answer 2

It makes no difference. The optimizer will parse them all the same way. My personal preference would be your first example.

SELECT *
  FROM TableA a
    JOIN TableB b
      ON  a.ColAFoo = b.ColBFoo
        AND a.ColAFoo2 = b.ColBFoo2

Answer 3

In theory, the first way is best:

SELECT *
  FROM TableA a
  JOIN TableB b
    ON  a.ColAFoo = b.ColBFoo
        AND a.ColAFoo2 = b.ColBFoo2

as you are specifying the relationship between TableA & TableB which might help the optimizer. In practice, the database already knows that (especially if you have a foreign key relationship established), so it really doesn't matter.

Answer 4

In case of 2 tables it doesn't matter. If you join many tables, don't forget that where clause is processed after joins, so you may have a significant difference in performance if you filter records, which can be filtered in ON section of INNER JOIN, in WHERE.

Answer 5

For your contrived version, no it won't matter. But I've definitely seen when there's additional JOIN or WHERE clauses.

Answer 6

For an inner join the following are equivelent in results, and will probably produce the same query plan:

SELECT *
  FROM TableA a
  JOIN TableB b
    ON  a.ColAFoo = b.ColBFoo
        AND a.ColAFoo2 = b.ColBFoo2

SELECT *
  FROM TableA a
  JOIN TableB b
    ON  a.ColAFoo = b.ColBFoo
  WHERE a.ColAFoo2 = b.ColBFoo2

-- SQL89 inner join:
SELECT *
  FROM TableA a, TableB b
 WHERE a.ColAFoo = b.ColBFoo
       AND a.ColAFoo2 = b.ColBFoo2

However Putting the join criteria in the ON clause will communicate to other programmers, "Hey! This is the criteria to relate the tables together." Versus stuff in the where clause that is, "criteria to limit the results, after the joins are done."

Also, the placement of the criteria makes a big difference in results when using an outer join, so it is a good habit to get into to put the join criteria in the on in all cases.