Comparison Functions - ClickHouse Documentation

Comparison rules

The comparison functions below return 0 or 1 with type UInt8. 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)

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.

equals

Introduced in: v1.1.0 Compares two values for equality. Syntax

equals(a, b)
        -- a = b
        -- a == b

Arguments

a — First value.^* - b — Second value.^*

Returned value Returns 1 if a is equal to b, otherwise 0 UInt8 Examples Usage example

Query

SELECT 1 = 1, 1 = 2;

Response

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

globalIn

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

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 Examples Basic usage

Query

SELECT 1 IN (1, 2, 3)

Response

globalInIgnoreSet

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

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 Examples Basic usage

Query

SELECT 1 IN (1, 2, 3)

Response

globalNotIn

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

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 Examples Basic usage

Query

SELECT 4 NOT IN (1, 2, 3)

Response

globalNotInIgnoreSet

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

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 Examples Basic usage

Query

SELECT 4 NOT IN (1, 2, 3)

Response

globalNotNullIn

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

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 Examples Basic usage

Query

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

Response

globalNotNullInIgnoreSet

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

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 Examples Basic usage

Query

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

Response

globalNullIn

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

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 Examples Basic usage

Query

SELECT nullIn(NULL, tuple(1, NULL))

Response

globalNullInIgnoreSet

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

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 Examples Basic usage

Query

SELECT nullIn(NULL, tuple(1, NULL))

Response

greater

Introduced in: v1.1.0 Compares two values for greater-than relation. Syntax

greater(a, b)
    -- a > b

Arguments

a — First value.^* - b — Second value.^*

Returned value Returns 1 if a is greater than b, otherwise 0 UInt8 Examples Usage example

Query

SELECT 2 > 1, 1 > 2;

Response

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

greaterOrEquals

Introduced in: v1.1.0 Compares two values for greater-than-or-equal-to relation. Syntax

greaterOrEquals(a, b)
    -- a >= b

Arguments

a — First value.^* - b — Second value.^*

Returned value Returns 1 if a is greater than or equal to b, otherwise 0 UInt8 Examples Usage example

Query

SELECT 2 >= 1, 2 >= 2, 1 >= 2;

Response

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

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 Examples Basic usage

Query

SELECT 1 IN (1, 2, 3)

Response

inIgnoreSet

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 Examples Basic usage

Query

SELECT 1 IN (1, 2, 3)

Response

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
y — Second value to compare. Can be any ClickHouse data type. Any

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

Examples Basic usage with numbers and NULLs

Query

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

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

isNotDistinctFrom

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

isNotDistinctFrom(x, y)

Arguments

x — First value to compare. Can be any ClickHouse data type. Any
y — Second value to compare. Can be any ClickHouse data type. Any

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

Examples Basic usage with numbers and NULLs

Query

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

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

less

Introduced in: v1.1.0 Compares two values for less-than relation. Syntax

less(a, b)
    -- a < b

Arguments

a — First value.^* - b — Second value.^*

Returned value Returns 1 if a is less than b, otherwise 0 UInt8 Examples Usage example

Query

SELECT 1 < 2, 2 < 1;

Response

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

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.^* - b — Second value.^*

Returned value Returns 1 if a is less than or equal to b, otherwise 0 UInt8 Examples Usage example

Query

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

Response

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

notEquals

Introduced in: v1.1.0 Compares two values for inequality. Syntax

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

Arguments

a — First value.^* - b — Second value.^*

Returned value Returns 1 if a is not equal to b, otherwise 0. UInt8 Examples Usage example

Query

SELECT 1 != 2, 1 != 1;

Response

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

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 Examples Basic usage

Query

SELECT 4 NOT IN (1, 2, 3)

Response

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 Examples Basic usage

Query

SELECT 4 NOT IN (1, 2, 3)

Response

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 Examples Basic usage

Query

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

Response

notNullInIgnoreSet

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 Examples Basic usage

Query

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

Response

nullIn

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

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 Examples Basic usage

Query

SELECT nullIn(NULL, tuple(1, NULL))

Response

nullInIgnoreSet

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 Examples Basic usage

Query

SELECT nullIn(NULL, tuple(1, NULL))

Response

​Comparison rules

​equals

​globalIn

​globalInIgnoreSet

​globalNotIn

​globalNotInIgnoreSet

​globalNotNullIn

​globalNotNullInIgnoreSet

​globalNullIn

​globalNullInIgnoreSet

​greater

​greaterOrEquals

​in

​inIgnoreSet

​isDistinctFrom

​isNotDistinctFrom

​less

​lessOrEquals

​notEquals

​notIn

​notInIgnoreSet

​notNullIn

​notNullInIgnoreSet

​nullIn

​nullInIgnoreSet

Comparison rules

equals

globalIn

globalInIgnoreSet

globalNotIn

globalNotInIgnoreSet

globalNotNullIn

globalNotNullInIgnoreSet

globalNullIn

globalNullInIgnoreSet

greater

greaterOrEquals

in

inIgnoreSet

isDistinctFrom

isNotDistinctFrom

less

lessOrEquals

notEquals

notIn

notInIgnoreSet

notNullIn

notNullInIgnoreSet

nullIn

nullInIgnoreSet