View source for Relational Data - Operators

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

<span id="access-operators"></span>
== Access Operators ==

<code>a[N]</code> – Access to an element of an array. The <code>arrayElement(a, N)</code> function.

<code>a.N</code> – Access to a tuple element. The <code>tupleElement(a, N)</code> function.

<span id="numeric-negation-operator"></span>
== Numeric Negation Operator ==

<code>-a</code> – The <code>negate (a)</code> function.

For tuple negation: [[Relational_Data_-_Functions_-_Tuple_Functions#tuplenegate|tupleNegate]].

<span id="multiplication-and-division-operators"></span>
== Multiplication and Division Operators ==

<code>a * b</code> – The <code>multiply (a, b)</code> function.

For multiplying tuple by number: [[Relational_Data_-_Functions_-_Tuple_Functions#tuplemultiplybynumber|tupleMultiplyByNumber]], for scalar product: [[Relational_Data_-_Functions_-_Tuple_Functions#dotproduct|dotProduct]].

<code>a / b</code> – The <code>divide(a, b)</code> function.

For dividing tuple by number: [[Relational_Data_-_Functions_-_Tuple_Functions#tupledividebynumber|tupleDivideByNumber]].

<code>a % b</code> – The <code>modulo(a, b)</code> function.

<span id="addition-and-subtraction-operators"></span>
== Addition and Subtraction Operators ==

<code>a + b</code> – The <code>plus(a, b)</code> function.

For tuple addiction: [[Relational_Data_-_Functions_-_Tuple_Functions#tupleplus|tuplePlus]].

<code>a - b</code> – The <code>minus(a, b)</code> function.

For tuple subtraction: [[Relational_Data_-_Functions_-_Tuple_Functions#tupleminus|tupleMinus]].

<span id="comparison-operators"></span>
== Comparison Operators ==

<span id="equals-function"></span>
=== equals function ===

<code>a = b</code> – The <code>equals(a, b)</code> function.

<code>a == b</code> – The <code>equals(a, b)</code> function.

<span id="notequals-function"></span>
=== notEquals function ===

<code>a != b</code> – The <code>notEquals(a, b)</code> function.

<code>a &lt;&gt; b</code> – The <code>notEquals(a, b)</code> function.

<span id="lessorequals-function"></span>
=== lessOrEquals function ===

<code>a &lt;= b</code> – The <code>lessOrEquals(a, b)</code> function.

<span id="greaterorequals-function"></span>
=== greaterOrEquals function ===

<code>a &gt;= b</code> – The <code>greaterOrEquals(a, b)</code> function.

<span id="less-function"></span>
=== less function ===

<code>a &lt; b</code> – The <code>less(a, b)</code> function.

<span id="greater-function"></span>
=== greater function ===

<code>a &gt; b</code> – The <code>greater(a, b)</code> function.

<span id="like-function"></span>
=== like function ===

<code>a LIKE s</code> – The <code>like(a, b)</code> function.

<span id="notlike-function"></span>
=== notLike function ===

<code>a NOT LIKE s</code> – The <code>notLike(a, b)</code> function.

<span id="ilike-function"></span>
=== ilike function ===

<code>a ILIKE s</code> – The <code>ilike(a, b)</code> function.

<span id="between-function"></span>
=== BETWEEN function ===

<code>a BETWEEN b AND c</code> – The same as <code>a &gt;= b AND a &lt;= c</code>.

<code>a NOT BETWEEN b AND c</code> – The same as <code>a &lt; b OR a &gt; c</code>.

<span id="operators-for-working-with-data-sets"></span>
== Operators for Working with Data Sets ==

See [[Relational_Data_-_Operators_-_In|IN operators]] and [[Relational_Data_-_Operators_-_Exists|EXISTS]] operator.

<span id="in-function"></span>
=== in function ===

<code>a IN ...</code> – The <code>in(a, b)</code> function.

<span id="notin-function"></span>
=== notIn function ===

<code>a NOT IN ...</code> – The <code>notIn(a, b)</code> function.

<span id="globalin-function"></span>
=== globalIn function ===

<code>a GLOBAL IN ...</code> – The <code>globalIn(a, b)</code> function.

<span id="globalnotin-function"></span>
=== globalNotIn function ===

<code>a GLOBAL NOT IN ...</code> – The <code>globalNotIn(a, b)</code> function.

<span id="in-subquery-function"></span>
=== in subquery function ===

<code>a = ANY (subquery)</code> – The <code>in(a, subquery)</code> function.

<span id="notin-subquery-function"></span>
=== notIn subquery function ===

<code>a != ANY (subquery)</code> – The same as <code>a NOT IN (SELECT singleValueOrNull(*) FROM subquery)</code>.

<span id="in-subquery-function-1"></span>
=== in subquery function ===

<code>a = ALL (subquery)</code> – The same as <code>a IN (SELECT singleValueOrNull(*) FROM subquery)</code>.

<span id="notin-subquery-function-1"></span>
=== notIn subquery function ===

<code>a != ALL (subquery)</code> – The <code>notIn(a, subquery)</code> function.

'''Examples'''

Query with ALL:

<syntaxhighlight lang="sql">SELECT number AS a FROM numbers(10) WHERE a > ALL (SELECT number FROM numbers(3, 3));</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─a─┐
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└───┘</syntaxhighlight>
Query with ANY:

<syntaxhighlight lang="sql">SELECT number AS a FROM numbers(10) WHERE a > ANY (SELECT number FROM numbers(3, 3));</syntaxhighlight>
Result:

<syntaxhighlight lang="text">┌─a─┐
│ 4 │
│ 5 │
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└───┘</syntaxhighlight>
<span id="operators-for-working-with-dates-and-times"></span>
== Operators for Working with Dates and Times ==

<span id="extract"></span>
=== EXTRACT ===

<syntaxhighlight lang="sql">EXTRACT(part FROM date);</syntaxhighlight>
Extract parts from a given date. For example, you can retrieve a month from a given date, or a second from a time.

The <code>part</code> parameter specifies which part of the date to retrieve. The following values are available:

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

The <code>part</code> parameter is case-insensitive.

The <code>date</code> parameter specifies the date or the time to process. Either [[Relational_Data_-_Data_Types_-_Date|Date]] or [[Relational_Data_-_Data_Types_-_Datetime|DateTime]] type is supported.

Examples:

<syntaxhighlight lang="sql">SELECT EXTRACT(DAY FROM toDate('2017-06-15'));
SELECT EXTRACT(MONTH FROM toDate('2017-06-15'));
SELECT EXTRACT(YEAR FROM toDate('2017-06-15'));</syntaxhighlight>
In the following example we create a table and insert into it a value with the <code>DateTime</code> type.

<syntaxhighlight lang="sql">CREATE TABLE test.Orders
(
    OrderId UInt64,
    OrderName String,
    OrderDate DateTime
)
ENGINE = Log;</syntaxhighlight>
<syntaxhighlight lang="sql">INSERT INTO test.Orders VALUES (1, 'Jarlsberg Cheese', toDateTime('2008-10-11 13:23:44'));</syntaxhighlight>
<syntaxhighlight lang="sql">SELECT
    toYear(OrderDate) AS OrderYear,
    toMonth(OrderDate) AS OrderMonth,
    toDayOfMonth(OrderDate) AS OrderDay,
    toHour(OrderDate) AS OrderHour,
    toMinute(OrderDate) AS OrderMinute,
    toSecond(OrderDate) AS OrderSecond
FROM test.Orders;</syntaxhighlight>
<syntaxhighlight lang="text">┌─OrderYear─┬─OrderMonth─┬─OrderDay─┬─OrderHour─┬─OrderMinute─┬─OrderSecond─┐
│      2008 │         10 │       11 │        13 │          23 │          44 │
└───────────┴────────────┴──────────┴───────────┴─────────────┴─────────────┘</syntaxhighlight>
You can see more examples in [https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/00619_extract.sql tests].

<span id="interval"></span>
=== INTERVAL ===

Creates an [https://clickhouse.com/docs/en/sql-reference/data-types/special-data-types/interval Interval]-type value that should be used in arithmetical operations with [[Relational_Data_-_Data_Types_-_Date|Date]] and [[Relational_Data_-_Data_Types_-_Datetime|DateTime]]-type values.

Types of intervals: - <code>SECOND</code> - <code>MINUTE</code> - <code>HOUR</code> - <code>DAY</code> - <code>WEEK</code> - <code>MONTH</code> - <code>QUARTER</code> - <code>YEAR</code>

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

<div class="tip">

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


</div>
Examples:

<syntaxhighlight lang="sql">SELECT now() AS current_date_time, current_date_time + INTERVAL 4 DAY + INTERVAL 3 HOUR;</syntaxhighlight>
<syntaxhighlight lang="text">┌───current_date_time─┬─plus(plus(now(), toIntervalDay(4)), toIntervalHour(3))─┐
│ 2020-11-03 22:09:50 │                                    2020-11-08 01:09:50 │
└─────────────────────┴────────────────────────────────────────────────────────┘</syntaxhighlight>
<syntaxhighlight lang="sql">SELECT now() AS current_date_time, current_date_time + INTERVAL '4 day' + INTERVAL '3 hour';</syntaxhighlight>
<syntaxhighlight lang="text">┌───current_date_time─┬─plus(plus(now(), toIntervalDay(4)), toIntervalHour(3))─┐
│ 2020-11-03 22:12:10 │                                    2020-11-08 01:12:10 │
└─────────────────────┴────────────────────────────────────────────────────────┘</syntaxhighlight>
<syntaxhighlight lang="sql">SELECT now() AS current_date_time, current_date_time + INTERVAL '4' day + INTERVAL '3' hour;</syntaxhighlight>
<syntaxhighlight lang="text">┌───current_date_time─┬─plus(plus(now(), toIntervalDay('4')), toIntervalHour('3'))─┐
│ 2020-11-03 22:33:19 │                                        2020-11-08 01:33:19 │
└─────────────────────┴────────────────────────────────────────────────────────────┘</syntaxhighlight>
You can work with dates without using <code>INTERVAL</code>, just by adding or subtracting seconds, minutes, and hours. For example, an interval of one day can be set by adding <code>60*60*24</code>.

<div class="note">

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


</div>
Examples:

<syntaxhighlight lang="sql">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;</syntaxhighlight>
<syntaxhighlight lang="text">┌────────────────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 │
└─────────────────────┴─────────────────────┴─────────────────────┘</syntaxhighlight>
'''See Also'''

* [https://clickhouse.com/docs/en/sql-reference/data-types/special-data-types/interval Interval] data type
* [[Relational_Data_-_Functions_-_Type_Conversion_Functions#function-tointerval|toInterval]] type conversion functions

<span id="logical-and-operator"></span>
== Logical AND Operator ==

Syntax <code>SELECT a AND b</code> — calculates logical conjunction of <code>a</code> and <code>b</code> with the function [[Relational_Data_-_Functions_-_Logical_Functions#logical-and-function|and]].

<span id="logical-or-operator"></span>
== Logical OR Operator ==

Syntax <code>SELECT a OR b</code> — calculates logical disjunction of <code>a</code> and <code>b</code> with the function [[Relational_Data_-_Functions_-_Logical_Functions#logical-or-function|or]].

<span id="logical-negation-operator"></span>
== Logical Negation Operator ==

Syntax <code>SELECT NOT a</code> — calculates logical negation of <code>a</code> with the function [[Relational_Data_-_Functions_-_Logical_Functions#logical-not-function|not]].

<span id="conditional-operator"></span>
== Conditional Operator ==

<code>a ? b : c</code> – The <code>if(a, b, c)</code> 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 <code>b</code> or <code>C</code> is an [[Relational_Data_-_Functions_-_Array_Join#functions_arrayjoin|arrayJoin()]] function, each row will be replicated regardless of the “a” condition.

<span id="conditional-expression"></span>
== Conditional Expression ==

<syntaxhighlight lang="sql">CASE [x]
    WHEN a THEN b
    [WHEN ... THEN ...]
    [ELSE c]
END</syntaxhighlight>
If <code>x</code> is specified, then <code>transform(x, [a, ...], [b, ...], c)</code> function is used. Otherwise – <code>multiIf(a, b, ..., c)</code>.

If there is no <code>ELSE c</code> clause in the expression, the default value is <code>NULL</code>.

The <code>transform</code> function does not work with <code>NULL</code>.

<span id="concatenation-operator"></span>
== Concatenation Operator ==

<code>s1 || s2</code> – The <code>concat(s1, s2) function.</code>

<span id="lambda-creation-operator"></span>
== Lambda Creation Operator ==

<code>x -&gt; expr</code> – The <code>lambda(x, expr) function.</code>

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

<span id="array-creation-operator"></span>
== Array Creation Operator ==

<code>[x1, ...]</code> – The <code>array(x1, ...) function.</code>

<span id="tuple-creation-operator"></span>
== Tuple Creation Operator ==

<code>(x1, x2, ...)</code> – The <code>tuple(x2, x2, ...) function.</code>

<span id="associativity"></span>
== Associativity ==

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

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

<span id="checking-for-null"></span>
== Checking for <code>NULL</code> ==

ClickHouse supports the <code>IS NULL</code> and <code>IS NOT NULL</code> operators.

<span id="is_null"></span>
=== IS NULL ===

* For [[Relational_Data_-_Data_Types_-_Nullable|Nullable]] type values, the <code>IS NULL</code> operator returns:
** <code>1</code>, if the value is <code>NULL</code>.
** <code>0</code> otherwise.
* For other values, the <code>IS NULL</code> operator always returns <code>0</code>.

Can be optimized by enabling the [https://clickhouse.com/docs/en/operations/settings/settings#optimize-functions-to-subcolumns optimize_functions_to_subcolumns] setting. With <code>optimize_functions_to_subcolumns = 1</code> the function reads only [[Relational_Data_-_Data_Types_-_Nullable#finding-null|null]] subcolumn instead of reading and processing the whole column data. The query <code>SELECT n IS NULL FROM table</code> transforms to <code>SELECT n.null FROM TABLE</code>.

<!-- -->
<syntaxhighlight lang="sql">SELECT x+100 FROM t_null WHERE y IS NULL</syntaxhighlight>
<syntaxhighlight lang="text">┌─plus(x, 100)─┐
│          101 │
└──────────────┘</syntaxhighlight>
<span id="is_not_null"></span>
=== IS NOT NULL ===

* For [[Relational_Data_-_Data_Types_-_Nullable|Nullable]] type values, the <code>IS NOT NULL</code> operator returns:
** <code>0</code>, if the value is <code>NULL</code>.
** <code>1</code> otherwise.
* For other values, the <code>IS NOT NULL</code> operator always returns <code>1</code>.

<!-- -->
<syntaxhighlight lang="sql">SELECT * FROM t_null WHERE y IS NOT NULL</syntaxhighlight>
<syntaxhighlight lang="text">┌─x─┬─y─┐
│ 2 │ 3 │
└───┴───┘</syntaxhighlight>
Can be optimized by enabling the [https://clickhouse.com/docs/en/operations/settings/settings#optimize-functions-to-subcolumns optimize_functions_to_subcolumns] setting. With <code>optimize_functions_to_subcolumns = 1</code> the function reads only [[Relational_Data_-_Data_Types_-_Nullable#finding-null|null]] subcolumn instead of reading and processing the whole column data. The query <code>SELECT n IS NOT NULL FROM table</code> transforms to <code>SELECT NOT n.null FROM TABLE</code>.
[[Category:Relational_Data]]