SQL SERVER – JOIN Elimination for Not Selected Column

Recently I had a very interesting conversation with my client when we were working together on Comprehensive Database Performance Health Check. The question is about JOIN Elimination for Not Selected Column. Let us learn about it today.

My client asked me what will be the behavior of the SELECT query when we have many joins but in the select statement, we have only had columns from only selected few tables. Well, the answer is pretty simple – there is a good chance that the join elimination happens.

Join Elimination is a very effective method for SQL Server optimizer to build better execution plans. Let us see a very simple example of the same.

Run the following query for the sample database WideWorldImporters.

SELECT ol.PackageTypeID
FROM [Sales].[Orders] o
INNER JOIN [Sales].[OrderLines] ol
ON o.OrderID = ol.OrderID

Now check the execution plan.

Additionally, check the STATISTICS IO.

Table ‘OrderLines’. Scan count 1, logical reads 407

You will notice that in the query even though we have the table sales.orders, there is no column from this table used in the select statement and hence it is not visible in the execution plan as well as in the statistics IO. SQL Server Optimizer has removed it from the execution plan to avoid unnecessary overhead.

Now you should remember that not every single time this behavior is possible. There are many scenarios I have experienced when even though the select does not retrieve any column the table is used in the execution plan. The event of join elimination happens only when certain conditions are matched.

If you have any questions, please do not hesitate to reach out to me on Twitter.

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