Chris.Hansen: Import ClickHouse Docs: Wed Aug 28 2024 14:52:43 GMT-0400 (Eastern Daylight Time)

2024-08-28T18:52:43Z

Import ClickHouse Docs: Wed Aug 28 2024 14:52:43 GMT-0400 (Eastern Daylight Time)

New page

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


== 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.


== Numeric Negation Operator ==

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

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


== 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.


== 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]].


== Comparison Operators ==


=== equals function ===

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

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


=== notEquals function ===

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

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


=== lessOrEquals function ===

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


=== greaterOrEquals function ===

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


=== less function ===

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


=== greater function ===

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


=== like function ===

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


=== notLike function ===

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


=== ilike function ===

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


=== BETWEEN function ===

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

<code>a NOT BETWEEN b AND c</code> – The same as <code>a c</code>.


== Operators for Working with Data Sets ==

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


=== in function ===

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


=== notIn function ===

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


=== globalIn function ===

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


=== globalNotIn function ===

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


=== in subquery function ===

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


=== notIn subquery function ===

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


=== in subquery function ===

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


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

== Operators for Working with Dates and Times ==


=== 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].


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


== 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]].


== 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]].


== 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]].


== 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.


== 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>.


== Concatenation Operator ==

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


== Lambda Creation Operator ==

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

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


== Array Creation Operator ==

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


== Tuple Creation Operator ==

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


== 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 > 2 > 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.


== Checking for <code>NULL</code> ==

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


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

=== 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]]

Relational Data - Operators - Revision history

Chris.Hansen: Import ClickHouse Docs: Wed Aug 28 2024 14:52:43 GMT-0400 (Eastern Daylight Time)