ReferenceSQL ReferenceFunctions

Comparison

Comparison functions reference.

equals

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 │
└──────────────┴──────────────┘

Introduced in version 1.1.0.

globalIn

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

SELECT 1 IN (1, 2, 3)
1

Introduced in version 1.1.0.

globalInIgnoreSet

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

SELECT 1 IN (1, 2, 3)
1

Introduced in version 1.1.0.

globalNotIn

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

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

Introduced in version 1.1.0.

globalNotInIgnoreSet

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

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

Introduced in version 1.1.0.

globalNotNullIn

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

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

Introduced in version 1.1.0.

globalNotNullInIgnoreSet

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

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

Introduced in version 1.1.0.

globalNullIn

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

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

Introduced in version 1.1.0.

globalNullInIgnoreSet

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

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

Introduced in version 1.1.0.

greater

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 │
└───────────────┴───────────────┘

Introduced in version 1.1.0.

greaterOrEquals

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 │
└───────────────────────┴───────────────────────┴───────────────────────┘

Introduced in version 1.1.0.

in

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

SELECT 1 IN (1, 2, 3)
1

Introduced in version 1.1.0.

inIgnoreSet

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

Examples

Basic usage

SELECT 1 IN (1, 2, 3)
1

Introduced in version 1.1.0.

isDistinctFrom

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 RawTree data type. Any
  • y — Second value to compare. Can be any RawTree 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 │
└──────────┴──────────┴──────────┴──────────┘

Introduced in version 25.11.0.

isNotDistinctFrom

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 RawTree data type. Any
  • y — Second value to compare. Can be any RawTree 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 │
└──────────┴──────────┴──────────┴──────────┘

Introduced in version 23.8.0.

less

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 │
└────────────┴────────────┘

Introduced in version 1.1.0.

lessOrEquals

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 │
└────────────────────┴────────────────────┴────────────────────┘

Introduced in version 1.1.0.

notEquals

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 │
└─────────────────┴─────────────────┘

Introduced in version 1.1.0.

notIn

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

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

Introduced in version 1.1.0.

notInIgnoreSet

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

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

Introduced in version 1.1.0.

notNullIn

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

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

Introduced in version 1.1.0.

notNullInIgnoreSet

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

Examples

Basic usage

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

Introduced in version 1.1.0.

nullIn

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

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

Introduced in version 1.1.0.

nullInIgnoreSet

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

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

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

Introduced in version 1.1.0.

Bitmap

Bitmap functions reference.

Conditional

Conditional functions reference.

On this page

equalsglobalInglobalInIgnoreSetglobalNotInglobalNotInIgnoreSetglobalNotNullInglobalNotNullInIgnoreSetglobalNullInglobalNullInIgnoreSetgreatergreaterOrEqualsininIgnoreSetisDistinctFromisNotDistinctFromlesslessOrEqualsnotEqualsnotInnotInIgnoreSetnotNullInnotNullInIgnoreSetnullInnullInIgnoreSet