Comparison functions

## in[​](#in "Direct link to in")

Introduced in: v1.1.0

Checks if the left operand is a member of the right operand set. Returns 1 if it is, 0 otherwise. NULL values in the left operand are skipped (treated as not in the set).

**Syntax**

```
in(x, set)
```

**Arguments**

- `x` — The value to check. - `set` — The set of values.

**Returned value**

Returns 1 if x is in the set, 0 otherwise. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

```
SELECT 1 IN (1, 2, 3)
```

```
1
```

## inIgnoreSet[​](#inIgnoreSet "Direct link to inIgnoreSet")

Introduced in: v1.1.0

Checks if the left operand is a member of the right operand set. Returns 1 if it is, 0 otherwise. NULL values in the left operand are skipped (treated as not in the set).
This is the IgnoreSet variant used for type analysis without creating the set.

**Syntax**

```
in(x, set)
```

**Arguments**

- `x` — The value to check. - `set` — The set of values.

**Returned value**

Returns 1 if x is in the set, 0 otherwise. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

```
SELECT 1 IN (1, 2, 3)
```

```
1
```

## isDistinctFrom[​](#isDistinctFrom "Direct link to isDistinctFrom")

Introduced in: v25.11.0

Performs a null-safe "not equals" comparison between two values.
Returns `true` if the values are distinct (not equal), including when one value is NULL and the other is not.
Returns `false` if the values are equal, or if both are NULL.

**Syntax**

```
isDistinctFrom(x, y)
```

**Arguments**

- `x` — First value to compare. Can be any ClickHouse data type. [`Any`](/docs/sql-reference/data-types)
- `y` — Second value to compare. Can be any ClickHouse data type. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns `true` if the two values are different, treating NULLs as comparable:

- Returns `true` if x != y.
- Returns `true` if exactly one of x or y is NULL.
- Returns `false` if x = y, or both x and y are NULL. [`Bool`](/docs/sql-reference/data-types/boolean)

**Examples**

**Basic usage with numbers and NULLs**

```
SELECT
    isDistinctFrom(1, 2) AS result_1,
    isDistinctFrom(1, 1) AS result_2,
    isDistinctFrom(NULL, 1) AS result_3,
    isDistinctFrom(NULL, NULL) AS result_4
```

```
┌─result_1─┬─result_2─┬─result_3─┬─result_4─┐
│        1 │        0 │        1 │        0 │
└──────────┴──────────┴──────────┴──────────┘
```

## isNotDistinctFrom[​](#isNotDistinctFrom "Direct link to isNotDistinctFrom")

Introduced in: v25.10.0

Performs a null-safe "equals" comparison between two values.
Returns `true` if the values are equal, including when both are NULL.
Returns `false` if the values are different, or if exactly one of them is NULL.

**Syntax**

```
isNotDistinctFrom(x, y)
```

**Arguments**

- `x` — First value to compare. Can be any ClickHouse data type. [`Any`](/docs/sql-reference/data-types)
- `y` — Second value to compare. Can be any ClickHouse data type. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns `true` if the two values are equal, treating NULLs as comparable:

- Returns `true` if x = y.
- Returns `true` if both x and y are NULL.
- Returns `false` if x != y, or exactly one of x or y is NULL. [`Bool`](/docs/sql-reference/data-types/boolean)

**Examples**

**Basic usage with numbers and NULLs**

```
SELECT
    isNotDistinctFrom(1, 1) AS result_1,
    isNotDistinctFrom(1, 2) AS result_2,
    isNotDistinctFrom(NULL, NULL) AS result_3,
    isNotDistinctFrom(NULL, 1) AS result_4
```

```
┌─result_1─┬─result_2─┬─result_3─┬─result_4─┐
│        1 │        0 │        1 │        0 │
└──────────┴──────────┴──────────┴──────────┘
```

## less[​](#less "Direct link to less")

Introduced in: v1.1.0

Compares two values for less-than relation.

**Syntax**

```
less(a, b)
    -- a < b
```

**Arguments**

- `a` — First value.[\*](#comparison-rules) - `b` — Second value.[\*](#comparison-rules)

**Returned value**

Returns `1` if `a` is less than `b`, otherwise `0` [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT 1 < 2, 2 < 1;
```

```
┌─less(1, 2)─┬─less(2, 1)─┐
│          1 │          0 │
└────────────┴────────────┘
```

## lessOrEquals[​](#lessOrEquals "Direct link to lessOrEquals")

Introduced in: v1.1.0

Compares two values for less-than-or-equal-to relation.

**Syntax**

```
lessOrEquals(a, b)
-- a <= b
```

**Arguments**

- `a` — First value.[\*](#comparison-rules) - `b` — Second value.[\*](#comparison-rules)

**Returned value**

Returns `1` if `a` is less than or equal to `b`, otherwise `0` [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT 1 <= 2, 2 <= 2, 3 <= 2;
```

```
┌─lessOrEquals(1, 2)─┬─lessOrEquals(2, 2)─┬─lessOrEquals(3, 2)─┐
│                  1 │                  1 │                  0 │
└────────────────────┴────────────────────┴────────────────────┘
```

## notEquals[​](#notEquals "Direct link to notEquals")

Introduced in: v1.1.0

Compares two values for inequality.

**Syntax**

```
notEquals(a, b)
    -- a != b
    -- a <> b
```

**Arguments**

- `a` — First value.[\*](#comparison-rules) - `b` — Second value.[\*](#comparison-rules)

**Returned value**

Returns `1` if `a` is not equal to `b`, otherwise `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT 1 != 2, 1 != 1;
```

```
┌─notEquals(1, 2)─┬─notEquals(1, 1)─┐
│               1 │               0 │
└─────────────────┴─────────────────┘
```

## notIn[​](#notIn "Direct link to notIn")

Introduced in: v1.1.0

Checks if the left operand is NOT a member of the right operand set. Returns 1 if it is not in the set, 0 otherwise. NULL values in the left operand are skipped.

**Syntax**

```
notIn(x, set)
```

**Arguments**

- `x` — The value to check. - `set` — The set of values.

**Returned value**

Returns 1 if x is not in the set, 0 otherwise. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

```
SELECT 4 NOT IN (1, 2, 3)
```

```
1
```

## notInIgnoreSet[​](#notInIgnoreSet "Direct link to notInIgnoreSet")

Introduced in: v1.1.0

Checks if the left operand is NOT a member of the right operand set. Returns 1 if it is not in the set, 0 otherwise. NULL values in the left operand are skipped.
This is the IgnoreSet variant used for type analysis without creating the set.

**Syntax**

```
notIn(x, set)
```

**Arguments**

- `x` — The value to check. - `set` — The set of values.

**Returned value**

Returns 1 if x is not in the set, 0 otherwise. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

```
SELECT 4 NOT IN (1, 2, 3)
```

```
1
```

## notNullIn[​](#notNullIn "Direct link to notNullIn")

Introduced in: v1.1.0

Checks if the left operand is NOT a member of the right operand set. Unlike `notIn`, NULL values are not skipped: NULL is compared with set elements, and NULL = NULL evaluates to true.

**Syntax**

```
notNullIn(x, set)
```

**Arguments**

- `x` — The value to check. - `set` — The set of values.

**Returned value**

Returns 1 if x is not in the set, 0 otherwise. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

```
SELECT notNullIn(NULL, tuple(1, NULL))
```

```
0
```

## notNullInIgnoreSet[​](#notNullInIgnoreSet "Direct link to notNullInIgnoreSet")

Introduced in: v1.1.0

Checks if the left operand is NOT a member of the right operand set. Unlike `notIn`, NULL values are not skipped: NULL is compared with set elements, and NULL = NULL evaluates to true.
This is the IgnoreSet variant used for type analysis without creating the set.

**Syntax**

```
notNullIn(x, set)
```

**Arguments**

- `x` — The value to check. - `set` — The set of values.

**Returned value**

Returns 1 if x is not in the set, 0 otherwise. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

```
SELECT notNullIn(NULL, tuple(1, NULL))
```

```
0
```

## nullIn[​](#nullIn "Direct link to nullIn")

Introduced in: v1.1.0