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.

When performing math operations, Druid uses 64-bit integer (long) data type unless there are double or float values. If an operation uses float or double values, then the result is a double, which is a 64-bit float. The precision of float and double values is defined by Java and the IEEE standard.

Keep the following guidelines in mind to help you manage precision issues:

  • Long values can store up to 2^63 accurately with an additional bit used for the sign.
  • Float values use 32 bits, and doubles use 64 bits. Both types are impacted by floating point precision. If you need exact decimal values, consider storing the number in a non-decimal format as a long value (up to the limit for longs). For example, if you need three decimal places, store the number multiplied by 1000 and then divide by 1000 when querying.

Arithmetic operators

OperatorDescription
x + yAdd
x - ySubtract
x * yMultiply
x / yDivide

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.

OperatorDescription
timestamp_expr + interval_exprAdd an amount of time to a timestamp.
timestamp_expr - interval_exprSubtract an amount of time from a timestamp.

Concatenation operator

Also see the CONCAT function.

OperatorDescription
x || yConcatenate strings x and y.

Comparison operators

OperatorDescription
x = yEqual to
x <> yNot equal to
x > yGreater than
x >= yGreater than or equal to
x < yLess than
x <= yLess than or equal to

Logical operators

OperatorDescription
x AND yBoolean AND
x OR yBoolean OR
NOT xBoolean NOT
x IS NULLTrue if x is NULL or empty string
x IS NOT NULLTrue if x is neither NULL nor empty string
x IS TRUETrue if x is true
x IS NOT TRUETrue if x is not true
x IS FALSETrue if x is false
x IS NOT FALSETrue if x is not false
x BETWEEN y AND zEquivalent to x >= y AND x <= z
x NOT BETWEEN y AND zEquivalent 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.