Table Ordering for Joins

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:

SELECT
	h.SalesOrderID,
	h.CustomerID,
	d.SalesOrderDetailID,
	d.ProductID,
	d.LineTotal
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesOrderDetail d
ON h.SalesOrderID = d.SalesOrderID
ORDER BY SalesOrderID
GO

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:

-- The logical ordering of the tables during an Inner Join
-- doesn't matter. It's up to the Query Optimnizer to arrange
-- the tables in the best order.
-- This query produces the same execution plan as the previous one.
SELECT
	h.SalesOrderID,
	h.CustomerID,
	d.SalesOrderDetailID,
	d.ProductID,
	d.LineTotal
FROM Sales.SalesOrderDetail d
JOIN Sales.SalesOrderHeader h
ON d.SalesOrderID = h.SalesOrderID
ORDER BY SalesOrderID
GO

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.

-- Execute the query with an Outer Join.
-- Now we are also getting back customers that haven't placed orders.
-- The left table is the preserving one, and missing rows from the right table are added with NULL values.
-- SQL Server performs a "Merge Join (Left Outer Join)" in the execution plan.
SELECT
	c.CustomerID,
	h.SalesOrderID
FROM Sales.Customer c
LEFT JOIN Sales.SalesOrderHeader h
ON c.CustomerID = h.CustomerID
GO

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:

-- You can rewrite the query from above with a Right Outer Join when you swap the order
-- of the tables.
SELECT
	c.CustomerID,
	h.SalesOrderID
FROM Sales.SalesOrderHeader h
RIGHT JOIN Sales.Customer c
ON c.CustomerID = h.CustomerID
GO

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,

-Klaus

7 thoughts on “Table Ordering for Joins”

  1. 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).

  2. 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 ..

    1. 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.

      Thanks,

      -Klaus

Leave a Reply to Mangesh Bedarkar Cancel Reply

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