For SQL Server 2012 where is better way to restrict rows (WHERE or JOIN)?

Question

I still don't get it - is it better for performance to restrict data inside WHERE clause or inside JOIN clause. Which is the better way? I've read a long time ago that there is no difference, but I am still not sure. I am speaking about INNER JOIN - in which case the result is the same.

EXAMPLE:

SELECT * FROM A JOIN B ON (A.X = B.Y) WHERE (A.Z = 5);

vs

SELECT * FROM A JOIN B ON (A.X = B.Y) AND (A.Z = 5);

I prefer to have the joins on the foreign keys. If a column is not part of the foreign key, then I would put the those columns in the WHERE clause. — John, Nov 13 '17 at 21:27

score 3 · Accepted Answer · answered Nov 14 '17 at 00:01

The best way is the one that matches your team's coding style. If you work alone then the best way is the one that you find to be the most readable. There is currently no performance difference for the queries in the question.

You can use the undocumented trace flag 8606 to see this for yourself. Here's the code that I'm testing with:

CREATE TABLE #A (X INT NOT NULL, Z INT NOT NULL, PRIMARY KEY (X));

CREATE TABLE #B (Y INT NOT NULL, Z INT NOT NULL, PRIMARY KEY (Y));

SELECT *
FROM #A A
JOIN #B B ON (A.X = B.Y)
WHERE (A.Z = 5)
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

SELECT *
FROM #A A
JOIN #B B ON (A.X = B.Y) AND (A.Z = 5)
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Not only do the queries have the exact same plan:

They also have the same internal input tree:

In other words, SQL Server rewrites the queries to be the same before optimization occurs. Therefore, there will not be a performance difference.

For SQL Server 2012 where is better way to restrict rows (WHERE or JOIN)?

1 Answers1