Nullable NOT IN: Read the Row Count Spool Carefully

Nullable NOT IN needs an explicit rule for unknown values before any plan tuning. An inner NULL can remove otherwise unmatched candidates. A rewrite to NOT EXISTS changes that decision, even when its plan looks cleaner.

Settle the exclusion rule first

I start with the returned identifiers, before examining the spool. A WHERE condition keeps rows when its result is true. An unknown comparison does not qualify. A matching exclusion already makes NOT IN false; a remaining NULL prevents a definite unmatched decision.

NOT EXISTS asks whether the correlated comparison finds a matching row. An inner NULL does not match an ordinary identifier. A nullable outer identifier needs its own policy. Add IS NOT NULL when unknown candidates must be excluded.

Test inner NULLs, duplicate keys and an empty set

The small demo table has outer values 1, 2, 3 and NULL. Its first exclusion set contains 2, another 2 and NULL. Then I remove the inner NULL and finally empty the set. Duplicate known keys leave the logical exclusion result unchanged.

Run the blocks below in order in one query window. The first block creates the small tables and shows the central comparisons.

DROP TABLE IF EXISTS #OuterValues, #InnerValues, #Truth;
CREATE TABLE #OuterValues(RowId int NOT NULL PRIMARY KEY, Id int NULL);
CREATE TABLE #InnerValues(Id int NULL);
CREATE TABLE #Truth
(Stage varchar(32) NOT NULL PRIMARY KEY, NotInRows bigint NOT NULL,
 NotExistsRows bigint NOT NULL, KnownOuterRows bigint NOT NULL);
INSERT #OuterValues VALUES(1,1),(2,2),(3,3),(4,NULL);
INSERT #InnerValues VALUES(2),(2),(NULL);

INSERT #Truth
SELECT 'Inner NULL',
 (SELECT COUNT_BIG(*) FROM #OuterValues WHERE Id NOT IN(SELECT Id FROM #InnerValues)),
 (SELECT COUNT_BIG(*) FROM #OuterValues AS o WHERE NOT EXISTS
  (SELECT 1 FROM #InnerValues AS i WHERE i.Id=o.Id)),
 (SELECT COUNT_BIG(*) FROM #OuterValues AS o WHERE o.Id IS NOT NULL AND NOT EXISTS
  (SELECT 1 FROM #InnerValues AS i WHERE i.Id=o.Id));

DELETE #InnerValues WHERE Id IS NULL;
INSERT #Truth
SELECT 'Duplicate known keys',
 (SELECT COUNT_BIG(*) FROM #OuterValues WHERE Id NOT IN(SELECT Id FROM #InnerValues)),
 (SELECT COUNT_BIG(*) FROM #OuterValues AS o WHERE NOT EXISTS
  (SELECT 1 FROM #InnerValues AS i WHERE i.Id=o.Id)),
 (SELECT COUNT_BIG(*) FROM #OuterValues AS o WHERE o.Id IS NOT NULL AND NOT EXISTS
  (SELECT 1 FROM #InnerValues AS i WHERE i.Id=o.Id));

DELETE #InnerValues;
INSERT #Truth
SELECT 'Empty inner set',
 (SELECT COUNT_BIG(*) FROM #OuterValues WHERE Id NOT IN(SELECT Id FROM #InnerValues)),
 (SELECT COUNT_BIG(*) FROM #OuterValues AS o WHERE NOT EXISTS
  (SELECT 1 FROM #InnerValues AS i WHERE i.Id=o.Id)),
 (SELECT COUNT_BIG(*) FROM #OuterValues AS o WHERE o.Id IS NOT NULL AND NOT EXISTS
  (SELECT 1 FROM #InnerValues AS i WHERE i.Id=o.Id));

SELECT Stage, NotInRows, NotExistsRows, KnownOuterRows FROM #Truth ORDER BY Stage;

The final column requires a non-NULL outer identifier before applying NOT EXISTS. With an empty inner set, NOT IN accepts all four candidates, including the outer NULL. That boundary matters when expressing a business rule. An explicit known-identifier condition still returns three.

Find the Row Count Spool’s actual job

A Row Count Spool can preserve evidence that rows exist without carrying their column values. Follow its input and associated predicates. In a nullable exclusion plan, examine any check for an inner NULL. Do not assume every spool has that purpose.

The larger example builds 10,000 non-NULL candidates and 3,000 even exclusion keys. It adds one inner NULL, then compares both predicates. The tested counts are zero for NOT IN and 7,000 for NOT EXISTS. Those different results are the central correctness finding.

DROP TABLE IF EXISTS #Candidate, #Excluded;
CREATE TABLE #Candidate(Id int NOT NULL PRIMARY KEY);
CREATE TABLE #Excluded(Id int NULL);
WITH Digits AS
(SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n))
INSERT #Candidate(Id)
SELECT 1+a.n+10*b.n+100*c.n+1000*d.n
FROM Digits AS a CROSS JOIN Digits AS b CROSS JOIN Digits AS c CROSS JOIN Digits AS d;
INSERT #Excluded(Id) SELECT Id*2 FROM #Candidate WHERE Id<=3000;
INSERT #Excluded(Id) VALUES(NULL);

-- Nullable exclusion input contains NULL.
SELECT COUNT_BIG(*) AS NullableNotInRows
FROM #Candidate WHERE Id NOT IN(SELECT Id FROM #Excluded);
SELECT COUNT_BIG(*) AS NullableNotExistsRows
FROM #Candidate AS c WHERE NOT EXISTS
(SELECT 1 FROM #Excluded AS e WHERE e.Id=c.Id);

Change nullability only when the data rule supports it

The next block removes the NULL I inserted and changes the exclusion column to NOT NULL. It also adds an index on that column. Both predicates returned 7,000 candidates in my test. Compare their actual plans under the stronger declared rule.

DELETE #Excluded WHERE Id IS NULL;
ALTER TABLE #Excluded ALTER COLUMN Id int NOT NULL;
CREATE INDEX IX_Excluded_Id ON #Excluded(Id);
-- The exclusion key is now declared NOT NULL.
SELECT COUNT_BIG(*) AS NonnullNotInRows
FROM #Candidate WHERE Id NOT IN(SELECT Id FROM #Excluded);
SELECT COUNT_BIG(*) AS NonnullNotExistsRows
FROM #Candidate AS c WHERE NOT EXISTS
(SELECT 1 FROM #Excluded AS e WHERE e.Id=c.Id);

DROP TABLE #OuterValues, #InnerValues, #Truth, #Candidate, #Excluded;

A missing spool or different join is an observed optimizer choice. I do not force either shape. Removing a spool alone does not establish lower elapsed time. Compare representative data, runtime work and complete results before reporting savings.

My objection to a quick rewrite is that an attractive plan can hide a changed NULL policy. A legitimate unknown-value rule belongs in the query contract. A NOT NULL constraint belongs where missing identifiers are invalid. Preserve the empty-set and nullable-outer tests when adapting the query.

What the actual plans showed

My nullable test returned zero NOT IN rows and 7,000 NOT EXISTS rows. Its NOT IN plan included a Row Count Spool. That spool executed 10,000 times, with one rebind and 9,999 rewinds. Its child NULL-check scan executed once and read 3,001 rows.

Those spool executions are not 10,000 rescans of the exclusion table. The child scan’s single execution is visible in the actual XML. Read each operator’s own counters. Do not treat a spool execution count as its child’s scan count.

After NULL removal, the NOT NULL change and the added index, both predicates returned 7,000. The captured NOT IN plan had no Row Count Spool. Both semantics and physical design changed between these stages. This is not a same-result elapsed-time benchmark.

The actual plans document this particular temporary table. Their cost percentages are optimizer estimates. Neither the operator change nor one sample establishes a universal speed improvement. Keep the declared data rule and full result checks central to the decision.

Run the small example first and watch the counts change as the NULL comes and goes.

An anti-join rewrite is not only a plan change, it is a decision about missing values.

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