Rockset

    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 the basic comparison operators shown 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. 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 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

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

    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.

    SQL commandResult
    SELECT COALESCE(null, 10, 'xyz')10
    SELECT COALESCE(null, 'xyz', 10)xyz
    SELECT COALESCE('xyz', null, 10.5)xyz
    SELECT COALESCE(undefined, 10, 'xyz')10

    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.

    SQL commandResult
    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.

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