Operators

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 (Includes end points. For example, if a query states WHERE age BETWEEN 10 AND 20, Drill returns both 10 and 20 in the result.)
  • 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, includes end points
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