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.
Addition and Subtraction Operatorsβ
a + b
β The plus(a, b)
function.
For tuple addiction: tuplePlus.
a - b
β The minus(a, b)
function.
For tuple subtraction: tupleMinus.
Comparison Operatorsβ
a = b
β The equals(a, b)
function.
a == b
β The equals(a, b)
function.
a != b
β The notEquals(a, b)
function.
a <> b
β The notEquals(a, b)
function.
a <= b
β The lessOrEquals(a, b)
function.
a >= b
β The greaterOrEquals(a, b)
function.
a < b
β The less(a, b)
function.
a > b
β The greater(a, b)
function.
a LIKE s
β The like(a, b)
function.
a NOT LIKE s
β The notLike(a, b)
function.
a ILIKE s
β The ilike(a, b)
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.
a IN ...
β The in(a, b)
function.
a NOT IN ...
β The notIn(a, b)
function.
a GLOBAL IN ...
β The globalIn(a, b)
function.
a GLOBAL NOT IN ...
β The globalNotIn(a, b)
function.
a = ANY (subquery)
β The in(a, subquery)
function.
a != ANY (subquery)
β The same as a NOT IN (SELECT singleValueOrNull(*) FROM subquery)
.
a = ALL (subquery)
β The same as a IN (SELECT singleValueOrNull(*) FROM subquery)
.
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 OrderSecond
FROM 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 β
βββββββββββββββββββββββ΄ββββββββββββββββββββββ΄ββββββββββββββββββββββ
See Also
- Interval data type
- toInterval type conversion functions
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 isNULL
.0
otherwise.
- For other values, the
IS NULL
operator always returns0
.
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 isNULL
.1
otherwise.
- For other values, the
IS NOT NULL
operator always returns1
.
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
.