SQL SERVER – Does Use of CTE Change the Order of Join in Inner Join

I just had an interesting conversation the day before when I was discussing about Join Order in one of my recent presentations.

The comment which triggered all the conversation was “If I want to change the order of how tables are joined in SQL Server, I prefer to use CTE instead of Join Orders”.

During the conversation user was suggesting that he wanted his tables to be joined in certain ways in SQL Server but SQL Server Engine Optimizer was re-organizing everything to give the most optimal performance. His needs were to join tables in certain ways and did not care about the performance. To join tables as per his need he had to use a FORCE ORDER hint of the table. However, he recently learned about CTE and believed that CTE will help him to achieve his tasks without the help of FORCE ORDER hint.

He believed that as CTE syntax is constructed before the SELECT statement SQL Server will build a result set instead first and will use the same resultset to join the SELECT statement following the CTE. Well, the casual conversation converted to debate and it was getting very difficult as everybody started to express their opinion with very loud voice. Finally, we decided to run an example on my machine.

NOTE: The example uses INNER JOIN only and the conclusion applies to only INNER JOIN. In the case of OUTER JOIN there is an entire different story, we will cover that in future blog posts.

We created two examples and executed them one by one. Once we executed them, we compared their resultant – they were identical. Right after that we compared the order of the join for both the example.

Example 1: Without CTE

USE AdventureWorks2012
GO
SELECT c.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.PersonCreditCard pc ON pc.BusinessEntityID = p.BusinessEntityID
INNER JOIN Sales.Customer c ON c.CustomerID = p.BusinessEntityID;


Execution Plan:

Example 2: With CTE

USE AdventureWorks2012
GO
WITH MyCTE AS
(SELECT c.CustomerID
FROM Sales.PersonCreditCard pc
INNER JOIN Sales.Customer c ON c.CustomerID = pc.BusinessEntityID)
SELECT cte.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN MyCTE cte ON cte.CustomerID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID;

Execution Plan:

When we compare both the execution plan, it is very clear that there is no change in the order of the table orders. Both the statements have a very similar execution plan and tables were joined in exactly the same order. The matter of the fact in the case of CTE, it was interesting to see that SQL Server started to join very first one table from CTE and another from SELECT statements and continued to build the entire execution plan.

The reason for this is very simple – CTE is not temporary table kind of object or feature. It is just an expression to represent your SELECT statement in such a way that it increases your readability and usability. They do not execute before the regular SELECT statement or build a result set before hand. It actually executes just like regular SELECT statement all together.

In summary: CTE does not impact Table Join Order when all Joins in the query are INNER JOIN.

Here is one of the my favorite videos on this subject:

Reference: Pinal Dave (https://blog.sqlauthority.com)