> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8a08bda2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for Comparison Functions

# Comparison Functions

<h2 id="comparison-rules">
  Comparison rules
</h2>

The comparison functions below return `0` or `1` with type [UInt8](/reference/data-types/int-uint). Only values within the same group can be
compared (e.g. `UInt16` and `UInt64`) but not across groups (e.g. `UInt16` and `DateTime`).
Comparison of numbers and strings are possible, as is comparison of strings with dates and dates with times.
For tuples and arrays, the comparison is lexicographic meaning that the comparison is made for each corresponding
element of the left side and right side tuple/array.

The following types can be compared:

* numbers and decimals
* strings and fixed strings
* dates
* dates with times
* tuples (lexicographic comparison)
* arrays (lexicographic comparison)

<Note>
  Strings are compared byte-by-byte. This may lead to unexpected results if one of the strings contains UTF-8 encoded multi-byte characters.
  A string S1 which has another string S2 as prefix is considered longer than S2.
</Note>

{/*AUTOGENERATED_START*/}

<h2 id="equals">
  equals
</h2>

Introduced in: v1.1.0

Compares two values for equality.

**Syntax**

```sql theme={null}
equals(a, b)
        -- a = b
        -- a == b
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT 1 = 1, 1 = 2;
```

```response title=Response theme={null}
┌─equals(1, 1)─┬─equals(1, 2)─┐
│            1 │            0 │
└──────────────┴──────────────┘
```

<h2 id="globalIn">
  globalIn
</h2>

Introduced in: v1.1.0

Same as `in`, but uses global set distribution in distributed queries. The set is sent to all remote servers.

**Syntax**

```sql theme={null}
globalIn(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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT 1 IN (1, 2, 3)
```

```response title=Response theme={null}
1
```

<h2 id="globalInIgnoreSet">
  globalInIgnoreSet
</h2>

Introduced in: v1.1.0

Same as `in`, but uses global set distribution in distributed queries. The set is sent to all remote servers.
This is the IgnoreSet variant used for type analysis without creating the set.

**Syntax**

```sql theme={null}
globalIn(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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT 1 IN (1, 2, 3)
```

```response title=Response theme={null}
1
```

<h2 id="globalNotIn">
  globalNotIn
</h2>

Introduced in: v1.1.0

Same as `notIn`, but uses global set distribution in distributed queries. The set is sent to all remote servers.

**Syntax**

```sql theme={null}
globalNotIn(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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT 4 NOT IN (1, 2, 3)
```

```response title=Response theme={null}
1
```

<h2 id="globalNotInIgnoreSet">
  globalNotInIgnoreSet
</h2>

Introduced in: v1.1.0

Same as `notIn`, but uses global set distribution in distributed queries. The set is sent to all remote servers.
This is the IgnoreSet variant used for type analysis without creating the set.

**Syntax**

```sql theme={null}
globalNotIn(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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT 4 NOT IN (1, 2, 3)
```

```response title=Response theme={null}
1
```

<h2 id="globalNotNullIn">
  globalNotNullIn
</h2>

Introduced in: v1.1.0

Same as `notNullIn`, but uses global set distribution in distributed queries. The set is sent to all remote servers.

**Syntax**

```sql theme={null}
globalNotNullIn(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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT notNullIn(NULL, tuple(1, NULL))
```

```response title=Response theme={null}
0
```

<h2 id="globalNotNullInIgnoreSet">
  globalNotNullInIgnoreSet
</h2>

Introduced in: v1.1.0

Same as `notNullIn`, but uses global set distribution in distributed queries. The set is sent to all remote servers.
This is the IgnoreSet variant used for type analysis without creating the set.

**Syntax**

```sql theme={null}
globalNotNullIn(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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT notNullIn(NULL, tuple(1, NULL))
```

```response title=Response theme={null}
0
```

<h2 id="globalNullIn">
  globalNullIn
</h2>

Introduced in: v1.1.0

Same as `nullIn`, but uses global set distribution in distributed queries. The set is sent to all remote servers.

**Syntax**

```sql theme={null}
globalNullIn(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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT nullIn(NULL, tuple(1, NULL))
```

```response title=Response theme={null}
1
```

<h2 id="globalNullInIgnoreSet">
  globalNullInIgnoreSet
</h2>

Introduced in: v1.1.0

Same as `nullIn`, but uses global set distribution in distributed queries. The set is sent to all remote servers.
This is the IgnoreSet variant used for type analysis without creating the set.

**Syntax**

```sql theme={null}
globalNullIn(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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT nullIn(NULL, tuple(1, NULL))
```

```response title=Response theme={null}
1
```

<h2 id="greater">
  greater
</h2>

Introduced in: v1.1.0

Compares two values for greater-than relation.

**Syntax**

```sql theme={null}
greater(a, b)
    -- a > b
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT 2 > 1, 1 > 2;
```

```response title=Response theme={null}
┌─greater(2, 1)─┬─greater(1, 2)─┐
│             1 │             0 │
└───────────────┴───────────────┘
```

<h2 id="greaterOrEquals">
  greaterOrEquals
</h2>

Introduced in: v1.1.0

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

**Syntax**

```sql theme={null}
greaterOrEquals(a, b)
    -- a >= b
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT 2 >= 1, 2 >= 2, 1 >= 2;
```

```response title=Response theme={null}
┌─greaterOrEquals(2, 1)─┬─greaterOrEquals(2, 2)─┬─greaterOrEquals(1, 2)─┐
│                     1 │                     1 │                     0 │
└───────────────────────┴───────────────────────┴───────────────────────┘
```

<h2 id="in">
  in
</h2>

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

```sql theme={null}
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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT 1 IN (1, 2, 3)
```

```response title=Response theme={null}
1
```

<h2 id="inIgnoreSet">
  inIgnoreSet
</h2>

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

```sql theme={null}
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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT 1 IN (1, 2, 3)
```

```response title=Response theme={null}
1
```

<h2 id="isDistinctFrom">
  isDistinctFrom
</h2>

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

```sql theme={null}
isDistinctFrom(x, y)
```

**Arguments**

* `x` — First value to compare. Can be any ClickHouse data type. [`Any`](/reference/data-types)
* `y` — Second value to compare. Can be any ClickHouse data type. [`Any`](/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`](/reference/data-types/boolean)

**Examples**

**Basic usage with numbers and NULLs**

```sql title=Query theme={null}
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
```

```response title=Response theme={null}
┌─result_1─┬─result_2─┬─result_3─┬─result_4─┐
│        1 │        0 │        1 │        0 │
└──────────┴──────────┴──────────┴──────────┘
```

<h2 id="isNotDistinctFrom">
  isNotDistinctFrom
</h2>

Introduced in: v23.8.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**

```sql theme={null}
isNotDistinctFrom(x, y)
```

**Arguments**

* `x` — First value to compare. Can be any ClickHouse data type. [`Any`](/reference/data-types)
* `y` — Second value to compare. Can be any ClickHouse data type. [`Any`](/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`](/reference/data-types/boolean)

**Examples**

**Basic usage with numbers and NULLs**

```sql title=Query theme={null}
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
```

```response title=Response theme={null}
┌─result_1─┬─result_2─┬─result_3─┬─result_4─┐
│        1 │        0 │        1 │        0 │
└──────────┴──────────┴──────────┴──────────┘
```

<h2 id="less">
  less
</h2>

Introduced in: v1.1.0

Compares two values for less-than relation.

**Syntax**

```sql theme={null}
less(a, b)
    -- a < b
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT 1 < 2, 2 < 1;
```

```response title=Response theme={null}
┌─less(1, 2)─┬─less(2, 1)─┐
│          1 │          0 │
└────────────┴────────────┘
```

<h2 id="lessOrEquals">
  lessOrEquals
</h2>

Introduced in: v1.1.0

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

**Syntax**

```sql theme={null}
lessOrEquals(a, b)
-- a <= b
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT 1 <= 2, 2 <= 2, 3 <= 2;
```

```response title=Response theme={null}
┌─lessOrEquals(1, 2)─┬─lessOrEquals(2, 2)─┬─lessOrEquals(3, 2)─┐
│                  1 │                  1 │                  0 │
└────────────────────┴────────────────────┴────────────────────┘
```

<h2 id="notEquals">
  notEquals
</h2>

Introduced in: v1.1.0

Compares two values for inequality.

**Syntax**

```sql theme={null}
notEquals(a, b)
    -- a != b
    -- a <> b
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```sql title=Query theme={null}
SELECT 1 != 2, 1 != 1;
```

```response title=Response theme={null}
┌─notEquals(1, 2)─┬─notEquals(1, 1)─┐
│               1 │               0 │
└─────────────────┴─────────────────┘
```

<h2 id="notIn">
  notIn
</h2>

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

```sql theme={null}
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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT 4 NOT IN (1, 2, 3)
```

```response title=Response theme={null}
1
```

<h2 id="notInIgnoreSet">
  notInIgnoreSet
</h2>

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

```sql theme={null}
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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT 4 NOT IN (1, 2, 3)
```

```response title=Response theme={null}
1
```

<h2 id="notNullIn">
  notNullIn
</h2>

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

```sql theme={null}
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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT notNullIn(NULL, tuple(1, NULL))
```

```response title=Response theme={null}
0
```

<h2 id="notNullInIgnoreSet">
  notNullInIgnoreSet
</h2>

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

```sql theme={null}
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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT notNullIn(NULL, tuple(1, NULL))
```

```response title=Response theme={null}
0
```

<h2 id="nullIn">
  nullIn
</h2>

Introduced in: v1.1.0

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

**Syntax**

```sql theme={null}
nullIn(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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT nullIn(NULL, tuple(1, NULL))
```

```response title=Response theme={null}
1
```

<h2 id="nullInIgnoreSet">
  nullInIgnoreSet
</h2>

Introduced in: v1.1.0

Checks if the left operand is a member of the right operand set. Unlike `in`, 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**

```sql theme={null}
nullIn(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`](/reference/data-types/int-uint)

**Examples**

**Basic usage**

```sql title=Query theme={null}
SELECT nullIn(NULL, tuple(1, NULL))
```

```response title=Response theme={null}
1
```