Skip to main content
Skip to main content
Edit this page

Comparison functions

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)
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.

equals​

Introduced in: v1.1

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

SELECT 1 = 1, 1 = 2;

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

greater​

Introduced in: v1.1

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

SELECT 2 > 1, 1 > 2;

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

greaterOrEquals​

Introduced in: v1.1

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

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

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

isDistinctFrom​

Introduced in: v25.9

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

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​

Introduced in: v25.9

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

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​

Introduced in: v1.1

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

SELECT 1 < 2, 2 < 1;

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

lessOrEquals​

Introduced in: v1.1

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

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

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

notEquals​

Introduced in: v1.1

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

SELECT 1 != 2, 1 != 1;

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