singleValueOrNull

The aggregate function singleValueOrNull is used to implement subquery operators, such as x = ALL (SELECT ...). It checks if there is only one unique non-NULL value in the data. If there is only one unique value, it returns it. If there are zero or at least two distinct values, it returns NULL.

Syntax

Parameters

• x — Column of any data type (except Map, Array or Tuple which cannot be of type Nullable).

Returned values

• The unique value, if there is only one unique non-NULL value in x.
• NULL, if there are zero or at least two distinct values.

Examples

Query:

Result:

Query:

Result: