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

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.

#IF

IF(cond, x, y)

Returns x if cond is true, y otherwise.