LEAD Defaults: Missing Rows Differ From NULL Values

LEAD defaults apply when the requested following row does not exist, not whenever its value is NULL. I keep those two conditions separate. A present next row can carry a missing measurement.

Keep a missing value inside a present row

The sample supplies four rows in unique RowId order. Their readings are ten, NULL, thirty and forty. The second row exists even though its reading is missing.

The LEAD expression requests one row forward and supplies negative one as a boundary default. At the first row, the requested second row exists. Its expected NextReading is therefore NULL rather than the default.

At the second and third rows, the expected following readings are thirty and forty. At the last row, no fifth row exists. Only that boundary is expected to receive the supplied negative-one default.

I return the original RowId and Reading beside NextReading. That makes the difference between row existence and value presence visible. A next-reading column alone would hide why the result is absent.

WITH Readings AS
(
    SELECT RowId,Reading
    FROM (VALUES (1,CAST(10 AS int)),(2,CAST(NULL AS int)),(3,30),(4,40)) AS v(RowId,Reading)
), Following AS
(
    SELECT *,LEAD(Reading,1,CAST(-1 AS int)) OVER (ORDER BY RowId) AS NextReading
    FROM Readings
)
SELECT RowId,Reading,NextReading,COALESCE(NextReading,CAST(-1 AS int)) AS AfterNullReplacement
FROM Following
ORDER BY RowId;

Compare a later NULL replacement deliberately

AfterNullReplacement applies COALESCE to the completed NextReading result. Its expected first-row value is negative one. That later replacement changes the meaning of the first row’s missing measurement.

The final row also reports negative one in that column. The output now merges a present missing reading with an absent following row. Such merging may be a chosen display policy, but it is not LEAD’s default behavior.

I keep both columns in the demonstration. Their only expected difference occurs at row one. That isolates the boundary default from a general missing-value replacement.

The sentinel negative one is only a convenient sample value. A real measurement domain might permit negative readings. Do not treat this demonstration as proof that negative one universally identifies an absent row.

Define the following row with a complete order

LEAD follows the analytic ORDER BY specification. This example uses a unique row key. A timestamp with ties would need another ordering key if the identity of the next row matters.

The final ORDER BY controls display order separately. It cannot repair an incomplete ordering inside LEAD. Both specifications are explicit here so the expected row correspondence is reviewable.

The offset counts rows rather than elapsed seconds or missing calendar dates. The next supplied row need not represent the next day. A workflow requiring temporal continuity must validate that separate relationship.

Partitioning can restrict following rows to a particular account or device. The default applies when the offset leaves that partition. It does not authorize reading the next row from a different group.

Keep boundary policy separate from data quality

A missing next measurement can indicate an incomplete collection process. An absent next row can simply mean the sequence ended. Those explanations deserve separate handling when the application needs them.

I’d expose a next-row key as well when existence must remain unambiguous. The measurement and its source-row identity answer different questions. Replacing both with one sentinel makes that distinction harder to recover later.

The sample uses the ordinary default NULL-respecting behavior. It does not request skipping missing readings. Finding the next present measurement would be a different operation with a different row relationship.

The boundary default must be type-compatible with the reading expression. Keep its meaning separate from a missing measurement inside a present row. An incompatible sentinel type would introduce a conversion question before the row relationship is understood.

When adapting this example, keep a present row with a missing reading before the sequence ends. Test the final boundary separately. Those two cases prevent an all-present sample from hiding an incorrect interpretation of the default argument.

Check the row first, then the value inside it.

A missing next value is not a missing row, it is a NULL inside an existing row.

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