(this question is very similar to my previous question Does it matter where I check part of a composite key when joining two tables in SQL Server?)
I'm joining three tables on a composite key, with the value of one of the columns being determined by user input. I'm wondering if it matters where I compare the corresponding columns when I do the joins.
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).
Finally, I have TableC, with ColCFoo, ColCFoo2, ColCBlah, and ColCBlah2. TableC's columns ColCFoo and ColCFoo2 comprise a foreign key to TableA's primary key (FK_TableA_TableC). I'll also say that TableC has a composite primary key comprising ColCFoo, ColCFoo2, and ColCBlah.
I need to join the three tables on the key. Is there a difference between the following (contrived) statements in terms of performance?
SELECT *
FROM TableA a
JOIN TableB b
ON a.ColAFoo = b.ColBFoo
AND a.ColAFoo2 = b.ColBFoo2
JOIN TableC c
ON a.ColAFoo = c.ColCFoo
AND a.ColAFoo2 = c.ColCFoo2
WHERE a.ColAFoo2 = @userInput
SELECT *
FROM TableA a
JOIN TableB b
ON a.ColAFoo = b.ColBFoo
AND a.ColAFoo2 = b.ColBFoo2
JOIN TableC c
ON a.ColAFoo = c.ColCFoo
WHERE a.ColAFoo2 = @userInput
AND c.ColCFoo2 = @userInput
SELECT *
FROM TableA a
JOIN TableB b
ON a.ColAFoo = b.ColBFoo
JOIN TableC c
ON a.ColAFoo = c.ColCFoo
WHERE a.ColAFoo2 = @userInput
AND b.ColBFoo2 = @userInput
AND c.ColCFoo2 = @userInput