Define what should happen to candidates whose own ID is missing. That business rule is separate from the nullable exclusion-list problem and can change whether a proposed rewrite is equivalent.
Keep an unmatched candidate in the fixture
This authored T-SQL exercise uses nonnull candidate IDs 1, 2 and 3, with exclusion values 2 and NULL:
DECLARE @candidate TABLE (id int NOT NULL);
DECLARE @excluded TABLE (id int NULL);
INSERT INTO @candidate VALUES (1), (2), (3);
INSERT INTO @excluded VALUES (2), (NULL);
SELECT id
FROM @candidate
WHERE id NOT IN (SELECT id FROM @excluded)
ORDER BY id;
The expected result is no rows. ID 2 has a matching exclusion. For IDs 1 and 3, comparison with the missing exclusion value leaves the negative-membership predicate UNKNOWN. WHERE retains rows for which its predicate is TRUE, so these unmatched IDs do not survive either.
Microsoft's IN reference, checked October 24, 2026, warns that NULL comparisons with IN or NOT IN yield UNKNOWN. This example supplies expected outcomes from that documented rule; it is not an executed SQL Server transcript.
Express the intended exclusion as absence of a match
If the approved rule is “retain nonnull candidates with no equal exclusion ID,” compare a correlated NOT EXISTS:
SELECT c.id
FROM @candidate AS c
WHERE NOT EXISTS (
SELECT 1 FROM @excluded AS e WHERE e.id = c.id
)
ORDER BY c.id;
The expected IDs are 1 and 3. For each candidate, the inner query asks whether an equal exclusion exists. An exclusion row with NULL does not establish equality with any of these nonnull candidate IDs.
Another candidate for this specific nonnull-input contract filters NULL out of the subquery before NOT IN. Do not present either form as a universal performance winner. Check the required rows first; the SQL optimization workflow addresses a separate plan-and-measurement comparison.
Test the nullable-candidate policy separately
| Exclusion rows | Expected NOT IN IDs | Expected NOT EXISTS IDs |
|---|---|---|
| 2 and NULL | None | 1, 3 |
| 2 only | 1, 3 | 1, 3 |
| Empty | 1, 2, 3 | 1, 2, 3 |
These rows assume the nonnull candidate table above. If candidates can contain NULL, the equality-based NOT EXISTS can retain that candidate because no equal match is found. Decide whether that is allowed. Add c.id IS NOT NULL when the maintained reporting rule requires it, rather than silently changing the meaning of a missing candidate.
Give ChatGPT the membership contract
Explain this SQL Server negative-membership query.
Engine/version and exact query: [ENTER/PASTE]
Schema, relevant types and declared nullability: [PASTE]
Small permitted candidate and exclusion rows: [PASTE]
Expected retained IDs, including missing candidate-ID policy: [ENTER]
Trace IN/NOT IN with TRUE, FALSE and UNKNOWN before rewriting.
Compare correlated NOT EXISTS against the supplied contract.
Include exclusion NULL, matching value, unmatched value and empty list.
Treat nullable candidate IDs as a separate policy decision.
Return a minimal SELECT and explicit expected rows. Do not alter
tables, invent execution results or claim a performance improvement.
That request follows OpenAI's prompting guidance by supplying the actual question and its constraints. Run the reviewed fixture in your approved SQL Server development environment and retain the actual result with the selected NULL policy. A correct-looking row count is insufficient; compare the surviving IDs.