singleValueOrNull

Introduced in: v21.9.0 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

Query

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

Response

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

Multiple distinct values

Query

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

Response

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

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

singleValueOrNull

singleValueOrNull

​singleValueOrNull

singleValueOrNull