Which is faster join

Question

Which is faster

SELECT * FROM X INNER JOIN Y ON x.Record_ID = y.ForignKey_NotIndexed_NotUnique

or

SELECT * FROM X INNER JOIN Y ON y.ForignKey_NotIndexed_NotUnique = x.Record_ID

Answer 1

There will be no difference - the query engine in mssql (and in most database engines) will build the same query plan for both. You can verify this by viewing the estimated and actual query plan in SQL Workbench.

Answer 2

The optimiser will ignore this because it's an identical JOIN.

SQL is declarative, not procedural, and the query will be evaluated in toto, not line after line.

Answer 3

How about creating a test that uses your own data to answer your question?

If you only learn one thing about database programming, it that there's often never a one size fits all solution to anything. There are best practices of course, but overall it's always case by case.

The reason I say this is because with relation database queries there is often not a one size fits all approach to anything. Because there is a planner that decides how to go after your data the same query can be lighting fast or super slow depending on what the planner chooses to do with the information it has available at any given time. For example sometimes an index is your best friend one month and a month later your worst enemy depending if you are searching or inserting data and how others are using the system while you are running the query. So I always recommend creating a test that proves for your use case how this query will perform. And even then your results may vary over time and you will come back to optimize as data and usage changes.

I think the answers you will get from stackoverflow is going to be text book answers, not the real world answer that only your environment can provide. And in the end it's your real world result that matters.

Now your simple query that you are showing here should both be the same performance wise. But even then I say test it and prove it if you are asking the question. I would trust results before I would trust anyones answer including mine.