Table Ordering for Joins

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

In today’s blog post I want to talk about a very interesting topic in SQL Server: whether or not it makes sense to rearrange the tables in a join in a specific order. Every time that I do a presentation about query and performance tuning, people ask me if they should arrange the tables in a join in a specific order to help the Query Optimizer to come up with a better performing execution plan. So let’s have a look at this very important and interesting question.

Inner Joins

Imagine you want to make an Inner Join between the tables Sales.SalesOrderHeader and Sales.SalesOrderDetail in the AdventureWorks database:

When we look at the resulting execution plan, we can see that the Query Optimizer has chosen a Merge Join as the physical Join operator, and that the table Sales.SalesOrderHeader is referenced as the outer table of the Merge Join. The tables are referenced in the execution plan in the same order as we have referenced them in the logical T-SQL query.

A Inner Join between 2 tables

The question is now, what happens to the execution plan when we swap both tables in the logical T-SQL query? Let’s try it:

When we look at the resulting execution plan now, we can see something very interesting:

The table ordering in the execution plan hasn't changed

Nothing has changed in the execution plan! The Query Optimizer has still chosen the same physical execution plan as it did with our previous query. But why? The answer is easy: the Query Optimizer always references the smallest table (based on our statistics!) as the outer table of every physical join operator (Nested Loop Join, Merge Join, Hash Match Join). Therefore the logical ordering of our tables in the T-SQL query doesn’t influence the Query Optimizer in any way. It’s the task of the Query Optimizer to access our tables in the correct order.

A Inner Join between a table A and B is the same as an Inner Join between the table B and A.

The table ordering in an Inner Join doesn't matter

Outer Joins

What happens to the table ordering in an Outer Join (Left Join or Right Join)? Let’s have a look at the following query, where I perform a Left Join between the table Sales.Customer and Sales.SalesOrderHeader.

When we have a more detailed look at the resulting execution plan, we can see that the Query Optimizer has preserved the ordering of our tables.

A Left Join between 2 tables

Of course this time we can’t change the ordering of our tables in the logical T-SQL query, because then the query would return the wrong result. But what happens when we swap the tables in the query, and instead of performing a Left Join, we perform a Right Join? Let’s try it:

When we look at the execution plan, we can see again that nothing has changed: the Query Optimizer has transformed the Right Join to a Left Join and has also rearranged our tables so that it is still able to return the correct logical result. The goal of the Query Optimizer was again to use the smallest table as the outer input of the physical join operator. Therefore the ordering of the tables in an Outer Join doesn’t matter either. As long as our statistics are correct, the Query Optimizer will choose the correct ordering.

A Left Join between table A and B is the same as a Right Join between the table B and A.

A Left Join is the same as a Right Join when we swap the tablesSummary

In this blog posting we have looked at whether the table ordering for a join influences the resulting execution plan. As we have seen it is completely up to the Query Optimizer to choose the optimal table ordering – based on our statistics. With an Inner Join the table ordering doesn’t matter at all, and with an Outer Join SQL Server can still rearrange the tables by swapping the Left/Right Joins.

Like or share to get the source code.

Thanks for your time,



  • Bernie Black

    Nice article. Quick to read and I was not %100 on what would happen. Thanks.

  • Paw Jershauge

    Once again a great article, Klaus. thumbs up 🙂

  • Thomas Franz

    Oh, forgot to write my comment in English:
    For the sake of completeness you should mention, that – if you force the join type for at least one table – it will use the join order in the statement.

    To enforce a join type just put the join type between the INNER and the JOIN (INNER HASH JOIN, INNER LOOP JOIN or INNER MERGE JOIN).

  • Lucky that SQL handles it this way. Else we would have a great time correcting auto generated queries 😀

  • Nice write keep it up up

  • Mangesh Bedarkar

    can you please let us know that when we are having inner join as well as outer join in a query, is there is any order to write the query means inner joins comes first and the outer joins comes later ..

    • Klaus Aschenbrenner

      Hello Mangesh,

      Thanks for your comment.

      That’s an interesting question!
      Normally you should always start with Inner Joins, and *finally* make your Outer Joins.
      If you do it in the other way (Outer Join, then Inner Join), the Inner Join *could* remove the Null-Extended Rows from the Outer Joins (depends on your ON clause), and the Query Optimizer translates the Outer Join to an Inner Join.



It`s your turn

Your email address will not be published. Required fields are marked *


Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top