What is Join Elimination?

SQL Server can be pretty smart when it comes to avoiding work it’s not required to do. One instance of this is the concept of join elimination. Join elimination occurs when queries involving joins may only need to check one table instead of two (or more) based on foreign key constraints.

Let’s take a look at join elimination in action.

Just the Essential

For this example, I’m going to use the AdventureWorks2019 database. It’s what I had available that already fit into that “Goldilocks” amount of data. Not too little, not too much, just right. Plus, it already had constraints to meet the needs of our example.

We’ll run a query against the Person.BusinessEntity table and join with the Person.Person table. to retrieve BusinessEntityID values.

The Person.Person table conveniently has a foreign key called FK_Person_BusinessEntity_BusinessEntityID. If we script the foreign key into a new query window, we’ll see it defined as:

ALTER TABLE [Person].[Person] 
WITH CHECK ADD CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID]
FOREIGN KEY([BusinessEntityID]) 
REFERENCES [Person].[BusinessEntity] ([BusinessEntityID])
GO

This foreign key will save us some work. Let’s run our query:

SELECT p.BusinessEntityID
FROM Person.BusinessEntity b
JOIN Person.Person p ON b.BusinessEntityID = p.BusinessEntityID;
GO

To confirm our join elimination, let’s see what the execution plan looks like:

Even though our query involved joining two tables, we only have a single index scan involving an index on the Person.Person table. SQL Server understands that based on the foreign key constraint involving BusinessEntityID, a match is always going to exist between the tables. Why scan two indexes when one index does the trick?

Without the Foreign Key Constraint

If you were expecting two scans and a join, that is what we see if there is no foreign key.

Let’s drop the foreign key:

ALTER TABLE [Person].[Person] 
DROP CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID]
GO

Now when we run the SELECT statement, a join is required:

SQL Server had to scan an index from both Person.Person and Person.BusinessIdentity.

Keeping it Simple

If you’re ever joining tables and don’t see them all referenced in an execution plan, now you have a good guess as to why.

Thanks for reading!