• Querying Your Data
  • SQL Reference
  • Conditional Expressions

Conditional Expressions

This page covers conditional operators and functions in Rockset.

#Operators

These operators are often useful in the WHERE clause of a SELECT statement.

Any operator that optionally includes NOT will return the opposite boolean when NOT is included.

#Logical Operators

Rockset supports the basic logical operators shown below.

OperatorDescription
NOT xtrue if x is false, and vice versa
x AND ytrue if and only if both x and y are true
x OR ytrue if either x or y is true

If any expression is null or undefined the result will be null.

#Comparison Operators

Rockset supports basic comparison operators show below.

OperatorDescription
x = yx equal to y
x <> yx not equal to y
x != yx not equal to y
x < yx less than y
x <= yx less than or equal to y
x > yx greater than y
x >= yx greater than or equal to y

Expressions can be of any type, but the result will be null if the two sides are of different types (except two numeric types is allowed).

#BETWEEN

x [NOT] BETWEEN a AND b

This is equivalent to the expression below (without NOT).

x >= a
AND x <= b

#CASE

CASE
    [expression]
    WHEN expr1 THEN value1
    [...]
    [ ELSE default_value ]
END

CASE is like an IF-THEN-ELSE statement. It executes a series of conditions and returns the value corresponding to the condition that evaluated to true.

It can have two behaviors:

  • if expression is not given, expressions following WHEN are evaluated as boolean expressions.
  • if expression is given, expressions following WHEN are checked for equality against expression.

The two queries below are equivalent and return the same results.

SELECT
    userEmail,
    CASE
        WHEN REGEXP_LIKE(userEmail, 'user1@') THEN 'User 1'
        WHEN REGEXP_LIKE(userEmail, 'user2@') THEN 'User 2'
        ELSE 'Unknown User'
    END AS username
FROM
    _events
LIMIT
    5
SELECT
    userEmail,
    CASE
        REGEXP_EXTRACT(userEmail, '(.*?)@', 1)
        WHEN 'user1' THEN 'User 1'
        WHEN 'user2' THEN 'User 2'
        ELSE 'Unknown User'
    END AS username
FROM
    _events
LIMIT
    5
+-------------------+--------------+
| userEmail         | username     |
|-------------------+--------------|
| user1@rockset.com | User 1       |
| user2@rockset.com | User 2       |
| user5@rockset.com | Unknown User |
| user1@rockset.com | User 1       |
| user2@rockset.com | User 2       |
+-------------------+--------------+

#EXISTS

[NOT] EXISTS x

The EXISTS predicate evaluates to true if the specified subquery returns at least one row. Otherwise, if the subquery returns zero rows, the predicate evaluates to false.

SELECT
    *
FROM
    customers c
WHERE
    EXISTS (
        SELECT
            1
        FROM
            payments p
        WHERE
            c.id = p.customer_id
            AND p.payment > 10000
    )

#IN

x [NOT] IN y

The IN predicate determines if the expression x is equal to any values provided by y, which can be one of:

  • a subquery, producing exactly one column, enclosed in parentheses
  • a list of values, enclosed in parentheses () and separated by commas (e.g. (1, 'a')). An example of each form is shown below.

Note that y cannot be an array. If y is an array, this functionality can be achieved with the ARRAY_CONTAINS function.

SELECT
    *
FROM
    users
WHERE
    users.user_name IN (
        SELECT
            qualified_users.user_name
        FROM
            qualified_users
    )
SELECT
    *
FROM
    users
WHERE
    users.user_name IN ('admin', 'root')

#IS DISTINCT FROM

x IS [NOT] DISTINCT FROM y

Returns true if x is not equal to y, and false otherwise.

#IS NULL

x IS [NOT] NULL

Returns true if x is null or undefined.

#IS UNDEFINED

x IS [NOT] UNDEFINED

Returns true if x is undefined.

#LIKE

x [NOT] LIKE y

Returns true if x and y (which must be of string or bytes type) match. A match is the same as equality, except also accounting for the following wildcard characters:

  • % represents zero, one, or multiple characters.
  • _ represents exactly one character.
SELECT
    'abc' LIKE 'ab'
false
SELECT
    'abc' LIKE 'ab_'
true
SELECT
    'abc' LIKE 'a_'
false
SELECT
    'abc' LIKE 'a%'
true
SELECT
    'abc' LIKE 'a_c'
true

#TRY

TRY(expression)

Returns the result of evaluating expression. If an error is encountered while evaluating expression, returns null instead.

SELECT
    TRY(1 / 0)
null
SELECT
    TRY(CAST('foo' AS int))
null
SELECT
    TRY(CONCAT('rock', 'set'))
'rockset'

#Functions

#COALESCE

COALESCE(x, ...)

Returns the first non-null value in the argument list.

SELECT
    COALESCE(null, 10, 'xyz')
10
SELECT
    COALESCE(null, 'xyz', 10)
'xyz'
SELECT
    COALESCE('xyz', null, 10.5)
'xyz'
-- Note that `undefined` behaves the same as `null` in almost all functions, so it is
-- ignored in `COALESCE` as well:
SELECT
    COALESCE(undefined, 10, 'xyz')
10

#IF

IF(cond, x, y)

Returns x if cond is true, y otherwise.

SELECT
    IF(true, 10, 'xyz')
10
SELECT
    IF(false, 10, 'xyz')
'xyz'
SELECT
    IF('abc' LIKE 'a_c', 10, 'xyz')
10
SELECT
    IF('abc' = 'aac', 10, 'xyz')
'xyz'
SELECT
    IF('abc', 10, 'xyz')
Error: If condition must be boolean, not string.

#NULLIF

NULLIF(value1, value2)

Returns null if value1 equals value2, otherwise returns value1.

SELECT
    NULLIF('blue', 'blue')
null
SELECT
    NULLIF('red', 'blue')
'red'