EXISTS: A Row Containing NULL Still Exists

EXISTS tests whether a subquery produces a row, even when its selected value is NULL. I separate row existence from value availability. Changing the projection to NULL does not erase the selected rows.

Keep a present row with a missing value

The available input contains two rows for identifier one, both with missing readings. Identifier two has one reading of seven. Identifier three has no available row.

The first existence expression projects Reading from rows matching the current request. For identifier one, those readings are NULL. Its expected existence indicator is still one.

For identifier two, the expected indicator is also one. For identifier three, it is zero. The question is whether any qualifying row exists, not whether its projected value is filled.

I keep the outer requested identifiers in the result. They show which request each existence decision describes. A missing inner value cannot itself explain whether the corresponding source row exists.

WITH Requested AS
(
    SELECT WantedId FROM (VALUES (1),(2),(3)) AS v(WantedId)
), Available AS
(
    SELECT ItemId,Reading FROM (VALUES
        (1,CAST(NULL AS int)),(1,CAST(NULL AS int)),(2,7)
    ) AS v(ItemId,Reading)
)
SELECT r.WantedId,
    CASE WHEN EXISTS (SELECT a.Reading FROM Available AS a WHERE a.ItemId=r.WantedId) THEN 1 ELSE 0 END AS ExistsValueProjection,
    CASE WHEN EXISTS (SELECT NULL FROM Available AS a WHERE a.ItemId=r.WantedId) THEN 1 ELSE 0 END AS ExistsNullProjection,
    CASE WHEN NOT EXISTS (SELECT 1 FROM Available AS a WHERE a.ItemId=r.WantedId) THEN 1 ELSE 0 END AS NoRows
FROM Requested AS r ORDER BY r.WantedId;

Selecting NULL still leaves qualifying rows

The second existence expression selects a NULL literal from the same matching rows. Its expected indicators are again one, one and zero. The projection change does not change the matching row set.

A selected NULL is a value in a row. It is not an instruction to remove that row. The subquery’s FROM and WHERE clauses still determine whether rows qualify.

This contrast is useful when reviewing code that confuses a blank grid cell with no result. An existence predicate evaluates the rowset. It does not need a nonmissing displayed column to establish presence.

I use only a safe literal in the alternate projection. The example does not claim every possible projected expression has no other semantic restrictions. It isolates the difference between a missing value and an absent row.

Repeated matching rows do not multiply the outer result

Identifier one has two matching source rows. Both existence expressions still produce one decision for its outer request. The query does not return an extra outer row for the second match.

That is different from projecting all matching inner rows through a join. A join can expose several source pairs. An existence predicate answers a yes-or-no row-presence question instead.

If an application needs the number of matches, that is another requirement. EXISTS does not return that count. If it needs each matching reading, it needs a row-producing relationship.

Choose the predicate from the question rather than replace one query pattern with another mechanically. Row presence, multiplicity and selected values are different outputs. State which one the receiving operation requires.

A matching predicate still defines the existence question

NoRows uses NOT EXISTS with the same matching condition. Its expected values are zero, zero and one. It identifies the request whose source rowset is empty.

The equality predicate compares present integer identifiers in this example. Missing matching keys would need a separate policy. A NULL projected reading does not alter the key comparison.

The source aliases are explicit inside the correlated predicates. That makes the inner identifier and outer request unambiguous. An accidental column reference could change the qualifying row set.

A present missing reading still belongs to a qualifying row. An absent identifier produces no qualifying row. Keep both cases when checking whether the predicate answers presence rather than value availability.

When adapting the existence check, retain a qualifying row whose payload is missing. Add repeated qualifying rows and a request with no match. Those cases reveal whether the code tests presence or accidentally assumes value availability.

Put a NULL reading in your own test rows and watch EXISTS shrug.

A NULL value is not a missing row, it is a value inside a row that exists.

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