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 |
If any expression is null
or undefined
the result will be null
.
Comparison Operators
Rockset supports the basic comparison operators shown 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. 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 followingWHEN
are evaluated as boolean expressions. - if
expression
is given, expressions followingWHEN
are checked for equality againstexpression
.
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. Ify
is an array, use theARRAY_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 command | Result |
---|---|
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 asnull
in almost all functions, so it is ignored inCOALESCE
as well.
IF
IF(cond, x, y)
Returns x
if cond
is true, y
otherwise.
SQL command | Result |
---|---|
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 command | Result |
---|---|
SELECT NULLIF('blue', 'blue') | null |
SELECT NULLIF('red', 'blue') | red |