Join Elimination is a powerful feature in SQL Server that allows the optimizer to build better execution plans by removing unnecessary joins from the query. In this article, we will explore how join elimination works and when it can be applied.

Let’s start with a simple example. Consider the following query:

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

When we analyze the execution plan for this query, we notice that even though the table “Sales.Orders” is included in the join, none of its columns are selected in the query. As a result, SQL Server optimizer eliminates this join from the execution plan to avoid unnecessary overhead.

This join elimination can be confirmed by checking the statistics IO, where we observe that the table “OrderLines” is scanned once with a logical read count of 407, while the table “Orders” is not mentioned at all.

It’s important to note that join elimination doesn’t occur in every scenario. There are certain conditions that need to be met for join elimination to take place. In some cases, even if no columns are selected from a table, it may still be included in the execution plan.

Join elimination is a valuable optimization technique that can significantly improve query performance. By removing unnecessary joins, the optimizer can reduce the amount of data that needs to be processed, resulting in faster query execution.

If you have any questions or would like to learn more about join elimination, feel free to reach out to me on Twitter.