Scalar Subqueries: An Empty Input Produces NULL

Scalar subqueries return a single value, and an empty result contributes SQL NULL to that expression. I check row existence separately when absence matters. A NULL scalar result alone cannot explain why no value appeared.

Request a value without removing the outer row

The outer input requests identifiers one, two and three. The available input contains identifier one with reading ten and identifier two with a missing reading. Identifier three has no available row.

The scalar subquery selects Reading for the current requested identifier. Each present identifier is unique in this example. That gives the expression at most one inner row.

The first expected scalar result is ten. The second expected scalar result is NULL from an existing source row. The third is NULL because the selected inner result is empty.

All three requested outer rows remain in the output. An empty scalar expression does not remove its containing row. It supplies a missing value for that expression.

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

Separate an absent row from a missing source value

SourceRowExists independently tests whether the requested identifier has an available row. Its expected values are one, one and zero. Those flags explain the two visually similar NULL scalar outputs.

At identifier two, the source row exists but its reading is missing. At identifier three, no source row exists. The scalar result alone cannot distinguish these conditions.

I keep the requested identifier beside both columns. A default value substituted into ScalarReading would merge the two missing outcomes. That might suit a display rule, but it would lose the explanation.

An application may need different actions for those states. It could request a missing measurement for an existing item. It could instead investigate an identifier with no matching item at all.

The scalar contract requires at most one selected row

This example’s available identifiers are unique. That is why the scalar lookup can return one value safely. A matching predicate does not itself enforce uniqueness.

If the inner query returns several rows, a scalar subquery does not choose one arbitrarily. It raises an error. Adding another matching row would therefore violate the demonstrated expression contract.

Do not add TOP without a meaningful ordering merely to hide that violation. Choosing one row is a separate business rule. It should explain which value is authoritative and how ties are resolved.

A real source can enforce an appropriate unique key or use a query with a deliberate one-value rule. Which choice fits depends on the data’s grain. The example does not claim one lookup pattern fits every relationship.

Keep the lookup meaning separate from execution strategy

The alias qualifications show which columns belong to the outer and inner inputs. That makes the correlation explicit. An accidentally resolved outer-column reference could otherwise change the lookup condition.

The example describes result semantics rather than a guaranteed physical plan. It does not claim the engine must execute the subquery literally once for every row. Optimization can produce a different implementation.

Keep the unique lookup identifier separate from its selected reading. A present missing reading and an absent lookup can produce the same scalar result. The existence indicator preserves the distinction without inventing a fallback value.

When adapting the lookup, keep all three cases in the test data. Check the uniqueness requirement independently. Then decide whether the receiver needs the scalar value alone or an accompanying existence indicator.

A fallback can be useful after those decisions are explicit. Preserve the original status if later processing needs it. Once several missing states become one default, the scalar display cannot reconstruct their origins.

Keep the existence flag, and every NULL keeps its story.

A NULL scalar value is not proof of an absent row, it is sometimes a present missing value.

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