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.

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

Comparison Operators

Rockset supports the basic comparison operators shown below.

Expressions can be of any type. The result will be null if the two sides are different types, unless they are both numeric types.

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 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

EXISTS

[NOT] EXISTS x

EXISTS evaluates to true if the specified subquery returns at least one row. If the subquery returns zero rows, it 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:

• A subquery, producing exactly one column, enclosed in parentheses
• A list of values, enclosed in parentheses () and separated by commas (such as (1, 'a')). An
  example of each form is shown below.
• See below for how this works using the WITH command

💡

Note on y value for IN predicate

The value for y cannot be an array. If y is an array, use 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')

WITH Statement: To use IN with the results of a WITH statement, wrap that statement in a subquery that fetches all rows. Instead of
writing WITH cryptographers () ... WHERE x in cryptographers (which won't work), instead write where x in (SELECT y from cryptographers)

IS DISTINCT FROM

x IS [NOT] DISTINCT FROM y

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

IS NULL

x IS [NOT] NULL

IS NULL Returns true if x is null or undefined.

IS UNDEFINED

x IS [NOT] UNDEFINED

IS UNDEFINED Returns true if x is undefined.

LIKE

x [NOT] LIKE y

string LIKE pattern Returns true if string matches the pattern, false otherwise. You can optionally use string NOT LIKE pattern which returns true if string does not match the pattern, false otherwise.

SELECT 'abc' LIKE 'ab'

SELECT 'abc' NOT LIKE 'ab'

You can use two wildcard values with LIKE:

% - Represents zero, one, or multiple characters
_ - Represents a single character

SELECT 'abc' LIKE 'ab_'

SELECT 'abc' LIKE 'a_'

SELECT 'abc' LIKE 'a%'

SELECT 'abc' LIKE 'a_c'

If you have a literal instance of these wildcard values, be sure to escape them.

SELECT 'ab_c' LIKE 'ab\_c'

TRY

TRY(expression)

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

SELECT TRY(1/0)

SELECT TRY(CAST('foo' AS int))

SELECT TRY(CONCAT('rock', 'set'))

Functions

COALESCE

COALESCE(x, ...) Returns the first non-null value in the argument list.

Note: undefined behaves the same as null in almost all functions, so it is ignored in
COALESCE as well.

IF

IF(cond, x, y) Returns x if cond is true, y otherwise.

NULLIF

NULLIF(value1, value2) Returns null if value1 equals value2, otherwise returns value1.