NUM_NULLS

Return the count of NULL arguments in a variadic argument list.

Category: miscReturns: INTDialect: PostgreSql

Syntax

NUM_NULLS(expr1, expr2, ...)

Description

## Overview Returns the number of arguments that evaluate to NULL. Accepts any number of expressions of any type. Use this function to measure data completeness, find rows where a configurable set of fields are missing, or build a per-row NULL ratio. ## Behavior - Requires at least one argument. - Returns an INT count in the range 0 to the number of arguments. - Never returns NULL, even when every argument is NULL (returns the argument count instead). - Arguments can be of different types; no type compatibility across arguments is required. - Each argument is evaluated exactly once. - Deterministic and side effect free. ## Compatibility - Matches the PG-compat NUM_NULLS function with variadic arguments.

Parameters

Name	Type	Description
`expr`		Specifies one or more expressions to examine. Each argument is evaluated exactly once; NULL values are counted, non-NULL values are ignored.

Examples

-- Mix of NULL and non-NULL
SELECT NUM_NULLS(1, NULL, 3, NULL, 5);  -- 2

-- No NULL arguments
SELECT NUM_NULLS('a', 'b', 'c');  -- 0

-- All arguments NULL
SELECT NUM_NULLS(NULL, NULL, NULL);  -- 3

-- Identify rows with at least one missing contact field
SELECT customer_id, NUM_NULLS(name, email, phone) AS missing_fields
FROM crm.catalog.customers
WHERE NUM_NULLS(name, email, phone) > 0;

-- Compute a completeness score per row
SELECT id, 1.0 - (NUM_NULLS(a, b, c, d) / 4.0) AS completeness
FROM crm.catalog.profile_fields;

Pitfalls

NUM_NULLS treats only SQL NULL as missing. Empty strings, zero values, and other sentinel representations are counted as non-NULL. Normalize first with NULLIFEMPTY if those should count as NULL.
Expressions like `SELECT NUM_NULLS(*)` do not expand `*` into individual columns. Pass each column explicitly, or use a structured approach like a row-level UDF.
All arguments are evaluated even if earlier ones are already NULL; there is no short-circuit behavior. Expensive expressions inside NUM_NULLS can dominate query cost.
The function is variadic and cannot be given zero arguments; at least one expression is required.