IGNORE NULLS

In the Ultra Transcenders books

DBX-DEP

Each book explains IGNORE NULLS in context, with comparison tables and the common traps.

Terms in this definition

• Window functions

  Calculate across rows related to whichever row is current, e.g. rankings or running totals. DAX provides OFFSET, WINDOW, INDEX, RANK and ROWNUMBER, partitioned and ordered through PARTITIONBY and ORDERBY; T-SQL uses OVER, applied once GROUP BY and HAVING finish.

• LAG

  Lets a query read a value from an earlier row of its own results, avoiding the need to join the table to itself. With IGNORE NULLS, NULLs are skipped over.

• LEAD

  Lets a query read a value from a later row of its own results, avoiding the need to join the table to itself.

• FIRST

  A DAX function available only inside visual calculations. It fetches the value at the start of one axis of the visual's matrix, which makes it handy for comparing each point with the first; its opposite is LAST.

• OVER

  Gives a T-SQL window function its window: PARTITION BY, ORDER BY and, if wanted, a ROWS or RANGE frame. Rankings and running totals can then be worked out while every row is kept.

• RESPECT NULLS

  The default behaviour of LAG, LEAD, FIRST_VALUE and LAST_VALUE, in which NULLs are counted. Its opposite, IGNORE NULLS, skips them, which is handy for carrying the last known value forward.

Related terms

• first_value

  Gives the first value of a group or window, as an aggregate or analytic function, and first means the same. Nulls count unless you add IGNORE NULLS.

• RESPECT NULLS

  What window functions like lag and lead do by default: treat nulls as ordinary rows. Specify IGNORE NULLS to step over them.