JOIN Elimination in SQL Server: When a Table Drops Out

JOIN elimination happens when SQL Server drops a joined table from the plan because the query does not need it. You see it in STATISTICS IO, where the table never appears. The optimizer does it only when it can prove that the join changes nothing.

What JOIN Elimination Means

A view can join a dozen tables while a report picks three columns. JOIN elimination is the optimizer’s answer to that gap. If a table adds no columns and cannot change the number of rows, reading it is wasted work. SQL Server notices and drops it.

The proof comes from a trusted foreign key. If every child row has exactly one parent row, an inner join to the parent returns each child row once. Without any parent column in the query, the parent adds nothing. The demo builds that case and breaks it in several ways.

The Demo Tables

The database is JoinElimDemo. Orders is the parent table with 2,000 rows. OrderLines is the child with 20,000 rows and a foreign key on OrderID. OrderNotes is a second child whose foreign key column allows NULL, which we use later. Run it on a test server.

IF DB_ID(N'JoinElimDemo') IS NULL CREATE DATABASE JoinElimDemo;
GO
USE JoinElimDemo;
GO
DROP TABLE IF EXISTS dbo.OrderNotes;
DROP TABLE IF EXISTS dbo.OrderLines;
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders (
    OrderID   int NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
    OrderDate date NOT NULL,
    Note      varchar(40) NOT NULL
);
CREATE TABLE dbo.OrderLines (
    LineID  int NOT NULL CONSTRAINT PK_OrderLines PRIMARY KEY,
    OrderID int NOT NULL CONSTRAINT FK_OrderLines_Orders FOREIGN KEY REFERENCES dbo.Orders (OrderID),
    Product varchar(30) NOT NULL,
    Qty     int NOT NULL
);
CREATE TABLE dbo.OrderNotes (
    NoteID   int NOT NULL CONSTRAINT PK_OrderNotes PRIMARY KEY,
    OrderID  int NULL CONSTRAINT FK_OrderNotes_Orders FOREIGN KEY REFERENCES dbo.Orders (OrderID),
    NoteText varchar(30) NOT NULL
);
INSERT INTO dbo.Orders (OrderID, OrderDate, Note)
SELECT n, DATEADD(day, n % 365, '2026-01-01'), 'Order ' + CAST(n AS varchar(10))
FROM (SELECT TOP (2000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
INSERT INTO dbo.OrderLines (LineID, OrderID, Product, Qty)
SELECT n, n % 2000 + 1, CHOOSE(n % 3 + 1, 'Seeds', 'Soil', 'Pots'), n % 5 + 1
FROM (SELECT TOP (20000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
INSERT INTO dbo.OrderNotes (NoteID, OrderID, NoteText)
SELECT n, CASE WHEN n % 4 = 0 THEN NULL ELSE n % 2000 + 1 END, 'Note'
FROM (SELECT TOP (5000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;

See It in STATISTICS IO

Two queries follow. Both join Orders to OrderLines. The first selects only a column of OrderLines. The second selects a column of Orders as well. Each assigns the value to a variable, so the Messages tab holds only the I/O lines.

SET STATISTICS IO ON;
DECLARE @p varchar(40);
PRINT 'Child column only';
SELECT @p = ol.Product FROM dbo.Orders AS o INNER JOIN dbo.OrderLines AS ol ON o.OrderID = ol.OrderID;
PRINT 'Parent column too';
SELECT @p = o.Note FROM dbo.Orders AS o INNER JOIN dbo.OrderLines AS ol ON o.OrderID = ol.OrderID;
SET STATISTICS IO OFF;

In the first query, Orders never appears. The optimizer removed it. In the second, Orders has to be read, because its column is selected. The table with 2,000 rows costs only 10 reads here. The second query also prints Workfile and Worktable lines with zero reads, because it uses a hash join. Elimination matters when the dropped table is large, or when a view hides many joins.

Nine Cases, Tested

Reading reads is slow. A faster check lists the tables in the cached plan. The next script runs seven variations through sp_executesql with a tag in a comment. The tags run from A to G. The query after them lists the tables each plan uses.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
DECLARE @p varchar(40);
EXEC sp_executesql N'/*JA*/ SELECT @p = ol.Product FROM dbo.Orders AS o INNER JOIN dbo.OrderLines AS ol ON o.OrderID = ol.OrderID;', N'@p varchar(40) OUTPUT', @p OUTPUT;
EXEC sp_executesql N'/*JB*/ SELECT @p = o.Note FROM dbo.Orders AS o INNER JOIN dbo.OrderLines AS ol ON o.OrderID = ol.OrderID;', N'@p varchar(40) OUTPUT', @p OUTPUT;
EXEC sp_executesql N'/*JC*/ SELECT @p = ol.Product FROM dbo.Orders AS o INNER JOIN dbo.OrderLines AS ol ON o.OrderID = ol.OrderID WHERE o.OrderDate = ''2026-03-01'';', N'@p varchar(40) OUTPUT', @p OUTPUT;
EXEC sp_executesql N'/*JD*/ SELECT @p = ol.Product FROM dbo.OrderLines AS ol LEFT JOIN dbo.Orders AS o ON o.OrderID = ol.OrderID;', N'@p varchar(40) OUTPUT', @p OUTPUT;
EXEC sp_executesql N'/*JE*/ SELECT @p = o.Note FROM dbo.Orders AS o LEFT JOIN dbo.OrderLines AS ol ON o.OrderID = ol.OrderID;', N'@p varchar(40) OUTPUT', @p OUTPUT;
EXEC sp_executesql N'/*JF*/ SELECT @p = n.NoteText FROM dbo.Orders AS o INNER JOIN dbo.OrderNotes AS n ON o.OrderID = n.OrderID;', N'@p varchar(40) OUTPUT', @p OUTPUT;
EXEC sp_executesql N'/*JG*/ SELECT @p = ol.Product FROM dbo.OrderLines AS ol INNER JOIN dbo.Orders AS o ON o.OrderID = ol.LineID;', N'@p varchar(40) OUTPUT', @p OUTPUT;

Two more variants test the trust of the foreign key. A disabled constraint cannot prove anything. Re-enabling it with the words WITH CHECK makes SQL Server verify every row and restores the trust.

ALTER TABLE dbo.OrderLines NOCHECK CONSTRAINT FK_OrderLines_Orders;
SELECT is_disabled, is_not_trusted FROM sys.foreign_keys WHERE name = N'FK_OrderLines_Orders';
GO
DECLARE @p varchar(40);
EXEC sp_executesql N'/*JH*/ SELECT @p = ol.Product FROM dbo.Orders AS o INNER JOIN dbo.OrderLines AS ol ON o.OrderID = ol.OrderID;', N'@p varchar(40) OUTPUT', @p OUTPUT;
GO
ALTER TABLE dbo.OrderLines WITH CHECK CHECK CONSTRAINT FK_OrderLines_Orders;
SELECT is_disabled, is_not_trusted FROM sys.foreign_keys WHERE name = N'FK_OrderLines_Orders';
GO
DECLARE @p varchar(40);
EXEC sp_executesql N'/*JI*/ SELECT @p = ol.Product FROM dbo.Orders AS o INNER JOIN dbo.OrderLines AS ol ON o.OrderID = ol.OrderID;', N'@p varchar(40) OUTPUT', @p OUTPUT;

Now list the tables in every cached plan. The query reads the object names from the plan XML.

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT v.Variant, STRING_AGG(v.TableName, N', ') WITHIN GROUP (ORDER BY v.TableName) AS TablesInPlan
FROM (SELECT DISTINCT SUBSTRING(st.text, CHARINDEX(N'/*J', st.text) + 3, 1) AS Variant,
             o.x.value('@Table', 'nvarchar(128)') AS TableName
      FROM sys.dm_exec_cached_plans AS cp
      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
      CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
      CROSS APPLY qp.query_plan.nodes('//p:Object') AS o(x)
      WHERE st.text LIKE N'%/*J[A-I]*/ SELECT @p = %') AS v
GROUP BY v.Variant
ORDER BY v.Variant;

If your server has optimize for ad hoc workloads on, run each statement twice before you read the plans.

How to Read the Table

JOIN elimination applies to variants A and D. A is the inner join through a trusted foreign key. D is a LEFT JOIN to a unique key where no parent column is used. In both, the parent cannot add or remove rows.

Variants B and C fail because the query needs the parent. A selected column or a filter makes the join necessary. Variant E joins the other way. Orders to OrderLines can return one parent row many times, so the child table cannot be dropped.

Variant F is the surprise. The foreign key column allows NULL. An inner join drops the rows with NULL, so the join does change the result. Mark the column NOT NULL when the data allows it, and elimination becomes possible. Variant G joins on columns that the foreign key does not cover, so there is nothing to prove.

Variants H and I teach trust. A constraint that was disabled is not trusted, and the optimizer ignores it. Re-enabling with a plain CHECK CONSTRAINT leaves it untrusted. Use WITH CHECK, as in variant I, and the elimination returns.

The Price of Relying on It

You could argue that you should never write a join you do not need. That is right, and elimination is not a license for sloppy queries. It is a safety net for views that join many tables, where each caller uses a few columns. The related post Query Plan Join: Why a Query With No Join Shows One shows the opposite surprise.

What to Remember

JOIN elimination needs a trusted foreign key, or a LEFT JOIN to a unique key. The query must also use no column of the dropped table. Check the key with is_not_trusted in sys.foreign_keys. When you finish the demo, drop the database.

USE master;
GO
DROP DATABASE JoinElimDemo;

A join is not free because you wrote it, it is free when the optimizer can prove it changes nothing.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.