# Operators

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

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

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

For tuple negation: tupleNegate.

## Multiplication and Division Operators​

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

For multiplying tuple by number: tupleMultiplyByNumber, for scalar profuct: dotProduct.

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

For dividing tuple by number: tupleDivideByNumber.

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

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

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

For tuple subtraction: tupleMinus.

## Comparison Operators​

### equals function​

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

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

### notEquals function​

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

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

### lessOrEquals function​

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

### greaterOrEquals function​

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

### less function​

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

### greater function​

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

### like function​

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

### notLike function​

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

### ilike function​

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

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

## Operators for Working with Data Sets​

See IN operators and EXISTS operator.

### in function​

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

### notIn function​

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

### globalIn function​

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

### globalNotIn function​

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

### in subquery function​

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

### notIn subquery function​

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

### in subquery function​

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

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

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

• `DAY` — The day of the month. Possible values: 1–31.
• `MONTH` — The number of a month. Possible values: 1–12.
• `YEAR` — The year.
• `SECOND` — The second. Possible values: 0–59.
• `MINUTE` — The minute. Possible values: 0–59.
• `HOUR` — The hour. Possible values: 0–23.

The `part` parameter is case-insensitive.

The `date` parameter specifies the date or the time to process. Either Date or DateTime type is 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'));``

In the following example we create a table and insert into it a value with the `DateTime` type.

``CREATE TABLE test.Orders(    OrderId UInt64,    OrderName String,    OrderDate DateTime)ENGINE = Log;``
``INSERT INTO test.Orders VALUES (1, 'Jarlsberg Cheese', toDateTime('2008-10-11 13:23:44'));``
``SELECT    toYear(OrderDate) AS OrderYear,    toMonth(OrderDate) AS OrderMonth,    toDayOfMonth(OrderDate) AS OrderDay,    toHour(OrderDate) AS OrderHour,    toMinute(OrderDate) AS OrderMinute,    toSecond(OrderDate) AS OrderSecondFROM test.Orders;``
``┌─OrderYear─┬─OrderMonth─┬─OrderDay─┬─OrderHour─┬─OrderMinute─┬─OrderSecond─┐│      2008 │         10 │       11 │        13 │          23 │          44 │└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘``

You can see more examples in tests.

### INTERVAL​

Creates an Interval-type value that should be used in arithmetical operations with Date and DateTime-type values.

Types of intervals:

• `SECOND`
• `MINUTE`
• `HOUR`
• `DAY`
• `WEEK`
• `MONTH`
• `QUARTER`
• `YEAR`

You can also use a string literal when setting the `INTERVAL` value. For example, `INTERVAL 1 HOUR` is identical to the `INTERVAL '1 hour'` or `INTERVAL '1' hour`.

##### warning

Intervals with different types can’t be combined. You can’t use expressions like `INTERVAL 4 DAY 1 HOUR`. Specify intervals in units that are smaller or equal to the smallest unit of the interval, for example, `INTERVAL 25 HOUR`. You can use consecutive operations, like in the example below.

Examples:

``SELECT now() AS current_date_time, current_date_time + INTERVAL 4 DAY + INTERVAL 3 HOUR;``
``┌───current_date_time─┬─plus(plus(now(), toIntervalDay(4)), toIntervalHour(3))─┐│ 2020-11-03 22:09:50 │                                    2020-11-08 01:09:50 │└─────────────────────┴────────────────────────────────────────────────────────┘``
``SELECT now() AS current_date_time, current_date_time + INTERVAL '4 day' + INTERVAL '3 hour';``
``┌───current_date_time─┬─plus(plus(now(), toIntervalDay(4)), toIntervalHour(3))─┐│ 2020-11-03 22:12:10 │                                    2020-11-08 01:12:10 │└─────────────────────┴────────────────────────────────────────────────────────┘``
``SELECT now() AS current_date_time, current_date_time + INTERVAL '4' day + INTERVAL '3' hour;``
``┌───current_date_time─┬─plus(plus(now(), toIntervalDay('4')), toIntervalHour('3'))─┐│ 2020-11-03 22:33:19 │                                        2020-11-08 01:33:19 │└─────────────────────┴────────────────────────────────────────────────────────────┘``

You can work with dates without using `INTERVAL`, just by adding or subtracting seconds, minutes, and hours. For example, an interval of one day can be set by adding `60*60*24`.

##### note

The `INTERVAL` syntax or `addDays` function are always preferred. Simple addition or subtraction (syntax like `now() + ...`) doesn't consider time settings. For example, daylight saving time.

Examples:

``SELECT toDateTime('2014-10-26 00:00:00', 'Asia/Istanbul') AS time, time + 60 * 60 * 24 AS time_plus_24_hours, time + toIntervalDay(1) AS time_plus_1_day;``
``┌────────────────time─┬──time_plus_24_hours─┬─────time_plus_1_day─┐│ 2014-10-26 00:00:00 │ 2014-10-26 23:00:00 │ 2014-10-27 00:00:00 │└─────────────────────┴─────────────────────┴─────────────────────┘``

## Logical AND Operator​

Syntax `SELECT a AND b` — calculates logical conjunction of `a` and `b` with the function and.

## Logical OR Operator​

Syntax `SELECT a OR b` — calculates logical disjunction of `a` and `b` with the function or.

## Logical Negation Operator​

Syntax `SELECT NOT a` — calculates logical negation of `a` with the function not.

## Conditional Operator​

`a ? b : c` – The `if(a, b, c)` function.

Note:

The conditional operator calculates the values of b and c, then checks whether condition a is met, and then returns the corresponding value. If `b` or `C` is an arrayJoin() function, each row will be replicated regardless of the “a” condition.

## Conditional Expression​

``CASE [x]    WHEN a THEN b    [WHEN ... THEN ...]    [ELSE c]END``

If `x` is specified, then `transform(x, [a, ...], [b, ...], c)` function is used. Otherwise – `multiIf(a, b, ..., c)`.

If there is no `ELSE c` clause in the expression, the default value is `NULL`.

The `transform` function does not work with `NULL`.

## Concatenation Operator​

`s1 || s2` – The `concat(s1, s2) function.`

## Lambda Creation Operator​

`x -> expr` – The `lambda(x, expr) function.`

The following operators do not have a priority since they are brackets:

## Array Creation Operator​

`[x1, ...]` – The `array(x1, ...) function.`

## Tuple Creation Operator​

`(x1, x2, ...)` – The `tuple(x2, x2, ...) function.`

## Associativity​

All binary operators have left associativity. For example, `1 + 2 + 3` is transformed to `plus(plus(1, 2), 3)`. Sometimes this does not work the way you expect. For example, `SELECT 4 > 2 > 3` will result in 0.

For efficiency, the `and` and `or` functions accept any number of arguments. The corresponding chains of `AND` and `OR` operators are transformed into a single call of these functions.

## Checking for `NULL`​

ClickHouse supports the `IS NULL` and `IS NOT NULL` operators.

### IS NULL​

• For Nullable type values, the `IS NULL` operator returns:
• `1`, if the value is `NULL`.
• `0` otherwise.
• For other values, the `IS NULL` operator always returns `0`.

Can be optimized by enabling the optimize_functions_to_subcolumns setting. With `optimize_functions_to_subcolumns = 1` the function reads only null subcolumn instead of reading and processing the whole column data. The query `SELECT n IS NULL FROM table` transforms to `SELECT n.null FROM TABLE`.

``SELECT x+100 FROM t_null WHERE y IS NULL``
``┌─plus(x, 100)─┐│          101 │└──────────────┘``

### IS NOT NULL​

• For Nullable type values, the `IS NOT NULL` operator returns:
• `0`, if the value is `NULL`.
• `1` otherwise.
• For other values, the `IS NOT NULL` operator always returns `1`.
``SELECT * FROM t_null WHERE y IS NOT NULL``
``┌─x─┬─y─┐│ 2 │ 3 │└───┴───┘``

Can be optimized by enabling the optimize_functions_to_subcolumns setting. With `optimize_functions_to_subcolumns = 1` the function reads only null subcolumn instead of reading and processing the whole column data. The query `SELECT n IS NOT NULL FROM table` transforms to `SELECT NOT n.null FROM TABLE`.