Skip to content

Expressions

An expression is a combination of values, operators and functions. Expressions are highly composable, and range from very simple to arbitrarily complex. They can be found in many different parts of SQL statements. In this section, we provide the different types of operators that can be used within expressions.

Logical Operators

Logical Operators are used within Expressions to express how predicates combine.

The following logical operators are available: AND, OR, XOR, and NOT.

a b a AND b a OR b a XOR b NOT a
true true true true false false
true false false true true false
false false false false false true
null true null null null null
null false null null null null

The operators AND, OR, and XOR are commutative, that is, you can switch the left and right operand without affecting the result.

Comparison Operators

Comparison Operators are used within Expressions to compare values, usually involving comparing a field within the datasets against a literal value - although comparisons can be used against two fields, or two literals.

Usually when one of the values involved in the comparison is null, the result is null.

Operator Description
= equal to
<> not equal to
< less than
> greater than
<= less than or equal to
>= greater than or equal to
IN value in list
NOT IN value not in list
LIKE pattern match
NOT LIKE inverse results of LIKE
ILIKE case-insensitive pattern match
NOT ILIKE inverse results of ILIKE
RLIKE regular expression match (also ~ and SIMILAR TO)
NOT RLIKE inverse results of RLIKE (also !~ and NOT SIMILAR TO)
~* case insensitive regular expression match
IS special comparison for true, false and null
| Bitwise OR, or IP containment
& Bitwise AND
^ Bitwise XOR

Other Comparisons

BETWEEN

Predicate Description
a BETWEEN x AND y equivalent to a >= x AND a <= y
a NOT BETWEEN x AND y equivalent to a < x OR a > y

Warning

Using BETWEEN with other predicates, especially when used with an AND conjunction, can cause the query parser to fail.

CASE

The CASE expression has two forms. The 'simple' form searches each value expression from top to bottom until it finds one that equals expression:

CASE expression
    WHEN value THEN result
    [ WHEN ... ]
    [ ELSE result ]
END

The result for the matching value is returned. If no match is found, the result from the ELSE clause is returned if it exists, otherwise null is returned. Example:

SELECT name, 
       CASE numberOfMoons 
            WHEN 0 THEN 'none' 
            WHEN 1 THEN 'one' 
            ELSE 'lots' 
       END as how_many_moons
  FROM $planets;

The 'searched' form evaluates each boolean condition from top to bottom until one is true and returns the matching result:

CASE
    WHEN condition THEN result
    [ WHEN ... ]
    [ ELSE result ]
END

If no conditions are true, the result from the ELSE clause is returned if it exists, otherwise null is returned. Example:

SELECT name, 
       CASE
           WHEN numberOfMoons = 0 THEN 'none' 
           WHEN numberOfMoons = 1 THEN 'one' 
           ELSE 'lots' 
       END as how_many_moons
  FROM $planets;