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 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 () or square brackets [] and separated by commas (e.g. (1, 'a') or [1, 'a'] )
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 () or square brackets [] and separated by commas (e.g. (1, 'a') or [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 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

#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')
10
SELECT
    IF('', 10, 'xyz')
'xyz'

#NULLIF

NULLIF(value1, value2)

Returns null if value1 equals value2, otherwise returns value1.

SELECT
    NULLIF('blue', 'blue')
null
SELECT
    NULLIF('red', 'blue')
'red'
Join us on Slack!
Building on Rockset? Come chat with us!