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.

Operator	Description
NOT x	true if x is false, and vice versa
x AND y	true if and only if both x and y are true
x OR y	true if either x or y is true

Expressions are implicitly cast as boolean (0, '', [], etc. become false, truth-y values become true). If any expression is null, the cast will fail and the result will also be null.

#Comparison Operators

Rockset supports basic comparison operators show below.

Operator	Description
x = y	x equal to y
x <> y	x not equal to y
x != y	x not equal to y
x < y	x less than y
x <= y	x less than or equal to y
x > y	x greater than y
x >= y	x 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).

xxxxxxxxxx

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

Returns false if x, a subquery enclosed in parentheses, returns empty results. Otherwise, returns true.

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.

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.

#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

#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 null_value is JSON null which is different from SQL null and hence
-- is considered non-null in the COALESCE operation.
SELECT
    COALESCE(null_value, 10, 'xyz')

null

#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')

10

SELECT
    IF('', 10, 'xyz')

'xyz'