Query Plan Join: Why a Query With No Join Shows One

A query plan join can appear even when the query has no join at all. SQL Server uses one to stitch an index and its table back together, and the plan shows it.

Logical and Physical Operators

An execution plan is a list of operators. Each operator has a logical name and a physical name. The logical name says what the operator does in relational terms, such as Inner Join. The physical name says how SQL Server does it, such as Nested Loops. A query plan join on a one table query is a physical operator that performs a logical join.

The join does not come from your SQL text. It comes from the way the data is stored. A table lives once, in its clustered index or its heap. A nonclustered index is a second, smaller copy of a few columns. A query that needs columns from both structures needs a join to combine them.

A Table With One Index

The demo uses a table of order lines. It has 100,000 rows, five lines for each order, and a wide Notes column. The primary key is the clustered index, so the table itself is ordered by OrderLineID. A nonclustered index sits on OrderID. The script creates a database named JoinInPlanDemo for this post only, so run it on a test server.

IF DB_ID(N'JoinInPlanDemo') IS NULL CREATE DATABASE JoinInPlanDemo;
GO
USE JoinInPlanDemo;
GO
DROP TABLE IF EXISTS dbo.OrderLines;
GO
CREATE TABLE dbo.OrderLines (
    OrderLineID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    OrderID     int NOT NULL,
    ItemName    nvarchar(60) NOT NULL,
    Quantity    int NOT NULL,
    Notes       nvarchar(200) NOT NULL
);
INSERT INTO dbo.OrderLines (OrderID, ItemName, Quantity, Notes)
SELECT (n - 1) / 5 + 1, N'Item ' + CAST(n % 40 AS nvarchar(10)), n % 9 + 1, REPLICATE(N'x', 100)
FROM (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS t;
CREATE INDEX IX_OrderLines_OrderID ON dbo.OrderLines (OrderID);

The next query counts the pages of each structure. Index 1 is the clustered index, which is the table. Index 2 is the nonclustered index.

SELECT index_id, in_row_data_page_count AS Pages
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.OrderLines')
ORDER BY index_id;

The table is about seventeen times larger than its index. That gap is why the index exists, and why a join appears when the query needs both.

Read the Plan of a One Table Query

Now run a query with no join keyword. SET SHOWPLAN_ALL returns the plan as rows and does not run the query. The second query asks only for columns that the index holds, so you can compare the two plans. To see the same plans as pictures, follow Actual Execution Plan in SQL Server: Graphical, Text and XML.

SET SHOWPLAN_ALL ON;
GO
SELECT * FROM dbo.OrderLines WHERE OrderID = 3982;
GO
SELECT OrderLineID, OrderID FROM dbo.OrderLines WHERE OrderID = 3982;
GO
SET SHOWPLAN_ALL OFF;

The result has eighteen columns. These three tell the story. The first query returns three operators. The table drops the statement row and shortens the names.

Read it from the inside out. The Index Seek finds the five rows of order 3982 in IX_OrderLines_OrderID. That index holds only OrderID and OrderLineID, so it cannot supply ItemName, Quantity or Notes. For each of the five rows, SQL Server runs a lookup in the clustered index by OrderLineID. SSMS draws it as Key Lookup. The lookup returns the three missing columns. The Nested Loops operator is the query plan join that pairs each index row with its table row.

The second query returns one operator, an Index Seek. It needs no other structure, so it has no join. You can confirm the cost with SET STATISTICS IO. The line to read is logical reads.

SET STATISTICS IO ON;
SELECT * FROM dbo.OrderLines WHERE OrderID = 3982;
SELECT OrderLineID, OrderID FROM dbo.OrderLines WHERE OrderID = 3982;
SET STATISTICS IO OFF;

The first query reads 17 pages. The second reads 2. Both return five rows, and the difference is the lookups.

Remove the Join With a Covering Index

A join exists only when one structure is not enough. The fix is an index that holds every column the query needs. A nonclustered index can carry extra columns at its leaf level without making them part of the key. The INCLUDE clause does that.

CREATE INDEX IX_OrderLines_OrderID_Cover ON dbo.OrderLines (OrderID) INCLUDE (ItemName, Quantity);
GO
SET SHOWPLAN_ALL ON;
GO
SELECT OrderID, ItemName, Quantity FROM dbo.OrderLines WHERE OrderID = 3982;
GO
SET SHOWPLAN_ALL OFF;
GO
SET STATISTICS IO ON;
SELECT OrderID, ItemName, Quantity FROM dbo.OrderLines WHERE OrderID = 3982;
SET STATISTICS IO OFF;

The plan is a single Index Seek on the new index, and the query reads 2 pages. The join is gone. A SELECT * would still need the lookups, because the Notes column is not in the index. List the columns you need instead of using SELECT *. The covering index helps only a query that asks for nothing else.

When SQL Server Chooses a Scan Instead

Seek plus lookup is not always the plan. The choice has no fixed row count. The optimizer compares the cost of the lookups with the cost of reading the whole table. This loop finds the switch on the demo table. It asks for MAX(Notes), a column no index holds, and it raises the OrderID limit each time. Each statement reads five rows per order and returns one value. After every statement, the loop reads the cached plan and notes whether it contains a lookup.

DROP TABLE IF EXISTS #Tip;
CREATE TABLE #Tip (RowsRead int, PlanShape nvarchar(30));
DECLARE @n int = 100, @sql nvarchar(400);
WHILE @n <= 300
BEGIN
    SET @sql = N'DECLARE @x nvarchar(200); SELECT @x = MAX(Notes) FROM dbo.OrderLines WHERE OrderID <= ' + CAST(@n AS nvarchar(10)) + N';';
    EXEC (@sql);
    INSERT #Tip (RowsRead, PlanShape)
    SELECT TOP (1) @n * 5,
           CASE WHEN CAST(qp.query_plan AS nvarchar(max)) LIKE N'%Lookup="1"%' THEN N'Seek and lookup' ELSE N'Clustered index scan' END
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
    WHERE st.text = @sql;
    SET @n += 20;
END;
SELECT RowsRead, PlanShape FROM #Tip ORDER BY RowsRead;

On this table, 800 rows still use lookups and 900 rows use a scan. The switch falls between 800 and 900 rows. That is 26 to 30 percent of the 3,031 pages, a rough rule that compares rows with pages. The optimizer compares estimated costs, so no fixed percentage applies. The figure belongs to this table. A table with a different page count switches at a different row count. The practical sign is a plan that changes shape when the filter value changes.

Is the Join a Problem?

You could argue that every join in a one table plan is a warning. It is not. For a few rows, seek plus lookup is the cheapest plan available. The cost grows with the row count, because each row adds its own lookup. It also grows with the number of runs. A query that returns five rows but runs ten thousand times a minute is a good candidate.

A covering index is not free. An insert or a delete changes every index of the table. An update changes each index that holds a changed column. Add INCLUDE columns only for a query that matters.

What to Remember

A query plan join in a single table query means one index did not hold every column. The Nested Loops operator pairs index rows with table rows through a lookup. Check the DefinedValues of the lookup. It lists the columns your index is missing.

Remove the join by listing fewer columns or by adding them to the index with INCLUDE. Expect a scan when the filter matches a large share of the table. When you finish, run the cleanup script.

USE master;
GO
ALTER DATABASE JoinInPlanDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE JoinInPlanDemo;

A join in the plan is not a mistake, it is the price of a missing column.

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