Relational Data - Aggregate Functions - Reference - 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

singleValueOrNull(x)

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:

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

Result:

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

Query:

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

Result:

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