# singleValueOrNull (/docs/reference/sql/aggregate-functions/singlevalueornull)



This page describes the RawTree engine binary used to generate this reference. It does not define Query API support. Descriptions retain upstream ClickHouse terminology. See [SQL interface scope](/docs/reference/clickhouse-compatibility#service-interfaces).

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**

```sql
singleValueOrNull(x)
```

**Arguments**

* `x` — A column of any data type except Map, Array or Tuple which cannot be of type Nullable. [`Any`](/reference/data-types)

**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`](/reference/data-types) or [`NULL`](/reference/syntax#null)

**Examples**

**Single unique value**

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

```text title=Response
┌─singleValueOrNull(x)─┐
│                    5 │
└──────────────────────┘
```

**Multiple distinct values**

```sql title=Query
INSERT INTO test (x) VALUES (10);
SELECT singleValueOrNull(x) FROM test;
```

```text title=Response
┌─singleValueOrNull(x)─┐
│                 ᴺᵁᴸᴸ │
└──────────────────────┘
```

*Introduced in version 21.9.0.*
