Druid SQL Operators

Druid SQL Operators

Apache Druid supports two query languages: Druid SQL and native queries. This document describes the SQL language.

Operators in Druid SQL typically operate on one or two values and return a result based on the values. Types of operators in Druid SQL include arithmetic, comparison, logical, and more, as described here.

Arithmetic operators

Operator	Description
`x + y`	Add
`x - y`	Subtract
`x * y`	Multiply
`x / y`	Divide

Datetime arithmetic operators

For the datetime arithmetic operators, interval_expr can include interval literals like INTERVAL '2' HOUR. This operator treats days as uniformly 86400 seconds long, and does not take into account daylight savings time. To account for daylight savings time, use the TIME_SHIFT function. Also see TIMESTAMPADD for datetime arithmetic.

Operator	Description
`timestamp_expr + interval_expr`	Add an amount of time to a timestamp.
`timestamp_expr - interval_expr`	Subtract an amount of time from a timestamp.

Concatenation operator

Also see the CONCAT function.

Operator	Description
`x \|\| y`	Concatenate strings `x` and `y`.

Comparison operators

Operator	Description
`x = y`	Equal to
`x <> y`	Not equal to
`x > y`	Greater than
`x >= y`	Greater than or equal to
`x < y`	Less than
`x <= y`	Less than or equal to

Logical operators

Operator	Description
`x AND y`	Boolean AND
`x OR y`	Boolean OR
`NOT x`	Boolean NOT
`x IS NULL`	True if x is NULL or empty string
`x IS NOT NULL`	True if x is neither NULL nor empty string
`x IS TRUE`	True if x is true
`x IS NOT TRUE`	True if x is not true
`x IS FALSE`	True if x is false
`x IS NOT FALSE`	True if x is not false
`x BETWEEN y AND z`	Equivalent to `x >= y AND x <= z`
`x NOT BETWEEN y AND z`	Equivalent to `x < y OR x > z`
`x LIKE pattern [ESCAPE esc]`	True if x matches a SQL LIKE pattern (with an optional escape)
`x NOT LIKE pattern [ESCAPE esc]`	True if x does not match a SQL LIKE pattern (with an optional escape)
`x IN (values)`	True if x is one of the listed values
`x NOT IN (values)`	True if x is not one of the listed values
`x IN (subquery)`	True if x is returned by the subquery. This will be translated into a join; see Query translation for details.
`x NOT IN (subquery)`	True if x is not returned by the subquery. This will be translated into a join; see Query translation for details.