Operators

Jan 14, 2016

You can use various types of operators in your Drill queries to perform operations on your data.

Logical Operators

You can use the following logical operators in your Drill queries:

  • AND
  • BETWEEN
  • IN
  • LIKE
  • NOT
  • OR

Comparison Operators

You can use the following comparison operators in your Drill queries:

  • <
  • >
  • <=
  • >=
  • =
  • <>
  • IS NULL
  • IS NOT NULL
  • IS FALSE
  • IS NOT FALSE
  • IS TRUE
  • IS NOT TRUE

Pattern Matching Operators

You can use the LIKE pattern matching operator in your Drill queries.

Math Operators

You can use the following math operators in your Drill queries:

Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division

Subquery Operators

You can use the following subquery operators in your Drill queries:

  • EXISTS
  • IN

See SELECT Statements.

String Concatenate Operator

You can use the following string operator in your Drill queries to concatenate strings:

  • string || string

The concatenate operator is an alternative to the concat function and will concatenate input if Drill can implicitly convert the input to a string.

The concat function treats NULL as an empty string. The concatenate operator (||) returns NULL if any input is NULL.

Operator Precedence

The following table shows the precedence of operators in decreasing order:

Operator/Element Associativity Description
. left dot notation used, for example, to drill down in a JSON map
[ ] left array-style notation to drill down into a JSON array
- right unary minus
E left exponentiation
* / % left multiplication, division, modulo
+ - left addition, subtraction
IS IS TRUE, IS FALSE, IS UNKNOWN, IS NULL
IS NULL test for null
IS NOT NULL test for not null
(any other) left all other native and user-defined operators
IN set membership
BETWEEN range containment
OVERLAPS time interval overlap
LIKE ILIKE SIMILAR TO NOT SIMILAR TO string pattern matching
< > less than, greater than
= right equality, assignment
NOT right logical negation
AND left logical conjunction
OR left logical disjunction