Operators

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

ClickHouse transforms operators to their corresponding functions at the query parsing stage according to their priority, precedence, and associativity.

## Access Operators[​](#access-operators "Direct link to Access Operators")

`a[N]` – Access to an element of an array. The `arrayElement(a, N)` function.

`a.N` – Access to a tuple element. The `tupleElement(a, N)` function.

## Numeric Negation Operator[​](#numeric-negation-operator "Direct link to Numeric Negation Operator")

`-a` – The `negate (a)` function.

For tuple negation: [tupleNegate](/docs/sql-reference/functions/tuple-functions#tupleNegate).

## Multiplication and Division Operators[​](#multiplication-and-division-operators "Direct link to Multiplication and Division Operators")

`a * b` – The `multiply (a, b)` function.

For multiplying tuple by number: [tupleMultiplyByNumber](/docs/sql-reference/functions/tuple-functions#tupleMultiplyByNumber), for scalar product: [dotProduct](/docs/sql-reference/functions/array-functions#arrayDotProduct).

`a / b` – The `divide(a, b)` function.

For dividing tuple by number: [tupleDivideByNumber](/docs/sql-reference/functions/tuple-functions#tupleDivideByNumber).

`a % b` – The `modulo(a, b)` function.

## Addition and Subtraction Operators[​](#addition-and-subtraction-operators "Direct link to Addition and Subtraction Operators")

`a + b` – The `plus(a, b)` function.

For tuple addiction: [tuplePlus](/docs/sql-reference/functions/tuple-functions#tuplePlus).

`a - b` – The `minus(a, b)` function.

For tuple subtraction: [tupleMinus](/docs/sql-reference/functions/tuple-functions#tupleMinus).

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

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

`a = b` – The `equals(a, b)` function.

`a == b` – The `equals(a, b)` function.

### notEquals function[​](#notequals-function "Direct link to notEquals function")

`a != b` – The `notEquals(a, b)` function.

`a <> b` – The `notEquals(a, b)` function.

### lessOrEquals function[​](#lessorequals-function "Direct link to lessOrEquals function")

`a <= b` – The `lessOrEquals(a, b)` function.

### greaterOrEquals function[​](#greaterorequals-function "Direct link to greaterOrEquals function")

`a >= b` – The `greaterOrEquals(a, b)` function.

### less function[​](#less-function "Direct link to less function")

`a < b` – The `less(a, b)` function.

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

`a > b` – The `greater(a, b)` function.

### like function[​](#like-function "Direct link to like function")

`a LIKE b` – The `like(a, b)` function.

### notLike function[​](#notlike-function "Direct link to notLike function")

`a NOT LIKE b` – The `notLike(a, b)` function.

### ilike function[​](#ilike-function "Direct link to ilike function")

`a ILIKE b` – The `ilike(a, b)` function.

### BETWEEN function[​](#between-function "Direct link to BETWEEN function")

`a BETWEEN b AND c` – The same as `a >= b AND a <= c`.

`a NOT BETWEEN b AND c` – The same as `a < b OR a > c`.

### is not distinct from operator (`<=>`)[​](#is-not-distinct-from "Direct link to is-not-distinct-from")

Note

From 25.10 you can use `<=>` in the same way as any other operator.
Before 25.10 it could only be used in JOIN expressions, for example:

```
CREATE TABLE a (x String) ENGINE = Memory;
INSERT INTO a VALUES ('ClickHouse');

SELECT * FROM a AS a1 JOIN a AS a2 ON a1.x <=> a2.x;

┌─x──────────┬─a2.x───────┐
│ ClickHouse │ ClickHouse │
└────────────┴────────────┘
```

The `<=>` operator is the `NULL`-safe equality operator, equivalent to `IS NOT DISTINCT FROM`.
It works like the regular equality operator (`=`), but it treats `NULL` values as comparable.
Two `NULL` values are considered equal, and a `NULL` compared to any non-`NULL` value returns 0 (false) rather than `NULL`.

```
SELECT
  'ClickHouse' <=> NULL,
  NULL <=> NULL
```

```
┌─isNotDistinc⋯use', NULL)─┬─isNotDistinc⋯NULL, NULL)─┐
│                        0 │                        1 │
└──────────────────────────┴──────────────────────────┘
```

## Operators for Working with Data Sets[​](#operators-for-working-with-data-sets "Direct link to Operators for Working with Data Sets")

See [IN operators](/docs/sql-reference/operators/in) and [EXISTS](/docs/sql-reference/operators/exists) operator.

### in function[​](#in-function "Direct link to in function")

`a IN ...` – The `in(a, b)` function.

### notIn function[​](#notin-function "Direct link to notIn function")

`a NOT IN ...` – The `notIn(a, b)` function.

### globalIn function[​](#globalin-function "Direct link to globalIn function")

`a GLOBAL IN ...` – The `globalIn(a, b)` function.

### globalNotIn function[​](#globalnotin-function "Direct link to globalNotIn function")

`a GLOBAL NOT IN ...` – The `globalNotIn(a, b)` function.

### in subquery function[​](#in-subquery-function "Direct link to in subquery function")

`a = ANY (subquery)` – The `in(a, subquery)` function.

### notIn subquery function[​](#notin-subquery-function "Direct link to notIn subquery function")

`a != ANY (subquery)` – The same as `a NOT IN (SELECT singleValueOrNull(*) FROM subquery)`.

### in subquery function[​](#in-subquery-function-1 "Direct link to in subquery function")

`a = ALL (subquery)` – The same as `a IN (SELECT singleValueOrNull(*) FROM subquery)`.

### notIn subquery function[​](#notin-subquery-function-1 "Direct link to notIn subquery function")

`a != ALL (subquery)` – The `notIn(a, subquery)` function.

**Examples**

Query with ALL:

```
SELECT number AS a FROM numbers(10) WHERE a > ALL (SELECT number FROM numbers(3, 3));
```

Result:

```
┌─a─┐
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└───┘
```

Query with ANY:

```
SELECT number AS a FROM numbers(10) WHERE a > ANY (SELECT number FROM numbers(3, 3));
```

Result:

```
┌─a─┐
│ 4 │
│ 5 │
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└───┘
```

## Operators for Working with Dates and Times[​](#operators-for-working-with-dates-and-times "Direct link to Operators for Working with Dates and Times")

### EXTRACT[​](#extract "Direct link to EXTRACT")

```
EXTRACT(part FROM date);
```

Extract parts from a given date. For example, you can retrieve a month from a given date, or a second from a time.

The `part` parameter specifies which part of the date to retrieve. The following values are available:

- `SECOND` — The second. Possible values: 0–59.
- `MINUTE` — The minute. Possible values: 0–59.
- `HOUR` — The hour. Possible values: 0–23.
- `DAY` — The day of the month. Possible values: 1–31.
- `WEEK` — The ISO 8601 week number. Possible values: 1–53.
- `MONTH` — The number of a month. Possible values: 1–12.
- `QUARTER` — The quarter. Possible values: 1–4.
- `YEAR` — The year.
- `EPOCH` — The Unix timestamp (seconds since 1970-01-01 00:00:00 UTC). Note: for `DateTime64`, the subsecond part is truncated.
- `DOW` — The day of the week (PostgreSQL-compatible). 0 = Sunday, 6 = Saturday.
- `DOY` — The day of the year. Possible values: 1–366.
- `ISODOW` — The ISO day of the week. 1 = Monday, 7 = Sunday.
- `ISOYEAR` — The ISO 8601 week-numbering year.
- `CENTURY` — The century. For example, the year 2024 is in the 21st century.
- `DECADE` — The decade (year divided by 10). For example, the year 2024 has decade 202.
- `MILLENNIUM` — The millennium. For example, the year 2024 is in the 3rd millennium.

The `part` parameter is case-insensitive.

The `date` parameter specifies the date or the time to process. The [Date](/docs/sql-reference/data-types/date), [Date32](/docs/sql-reference/data-types/date32), [DateTime](/docs/sql-reference/data-types/datetime), and [DateTime64](/docs/sql-reference/data-types/datetime64) types are supported.

Examples:

```
SELECT EXTRACT(DAY FROM toDate('2017-06-15'));
SELECT EXTRACT(MONTH FROM toDate('2017-06-15'));
SELECT EXTRACT(YEAR FROM toDate('2017-06-15'));
SELECT EXTRACT(EPOCH FROM toDateTime('2024-01-15 12:30:45', 'UTC'));
SELECT EXTRACT(DOW FROM toDate('2024-01-15'));
SELECT EXTRACT(CENTURY FROM toDate('2024-01-01'));
```