singleValueOrNull

Introduced in: v21.9

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

singleValueOrNull(x)

Arguments

x — A column of any data type except Map, Array or Tuple which cannot be of type Nullable. Any

Returned value

Returns the unique value if there is only one unique non-NULL value in x. Returns NULL if there are zero or at least two distinct values. Any or NULL

Examples

Single unique value

CREATE TABLE test (x UInt8 NULL) ENGINE=Log;
INSERT INTO test (x) VALUES (NULL), (NULL), (5), (NULL), (NULL);
SELECT singleValueOrNull(x) FROM test;

┌─singleValueOrNull(x)─┐
│                    5 │
└──────────────────────┘

Multiple distinct values

INSERT INTO test (x) VALUES (10);
SELECT singleValueOrNull(x) FROM test;

┌─singleValueOrNull(x)─┐
│                 ᴺᵁᴸᴸ │
└──────────────────────┘

singleValueOrNull​

singleValueOrNull