Comparison functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/comparison-functions.md)

## Comparison rules[​](#comparison-rules "Direct link to Comparison rules")

The comparison functions below return `0` or `1` with type [UInt8](/docs/sql-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.

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

Introduced in: v1.1.0

Compares two values for equality.

**Syntax**

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

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

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

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

## globalIn[​](#globalIn "Direct link to 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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

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

```
1
```

## globalInIgnoreSet[​](#globalInIgnoreSet "Direct link to 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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

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

```
1
```

## globalNotIn[​](#globalNotIn "Direct link to 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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

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

```
1
```

## globalNotInIgnoreSet[​](#globalNotInIgnoreSet "Direct link to 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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

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

```
1
```

## globalNotNullIn[​](#globalNotNullIn "Direct link to 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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

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

```
0
```

## globalNotNullInIgnoreSet[​](#globalNotNullInIgnoreSet "Direct link to 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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

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

```
0
```

## globalNullIn[​](#globalNullIn "Direct link to 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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

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

```
1
```

## globalNullInIgnoreSet[​](#globalNullInIgnoreSet "Direct link to 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`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Basic usage**

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

```
1
```

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

Introduced in: v1.1.0

Compares two values for greater-than relation.

**Syntax**

```
greater(a, b)
    -- a > b
```

**Arguments**

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

**Returned value**

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

**Examples**

**Usage example**

```
SELECT 2 > 1, 1 > 2;
```

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

## greaterOrEquals[​](#greaterOrEquals "Direct link to 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.[\*](#comparison-rules) - `b` — Second value.[\*](#comparison-rules)

**Returned value**

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

**Examples**

**Usage example**

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

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

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