This page covers conditional operators and functions in Rockset.
These operators are often useful in the
WHERE clause of a
Any operator that optionally includes
NOT will return the opposite boolean when
NOT is included.
Rockset supports the basic logical operators shown below.
Expressions are implicitly cast as boolean (
, etc. become
false, truth-y values become
true). If any expression is
null, the cast will fail and the result will also be
Rockset supports basic comparison operators show below.
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).
x [NOT] BETWEEN a AND b
This is equivalent to the expression below (without
x >= a AND x <= b
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.
expressionis not given, expressions following
WHENare evaluated as boolean expressions.
expressionis given, expressions following
WHENare checked for equality against
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 | |-------------------+--------------| | email@example.com | User 1 | | firstname.lastname@example.org | User 2 | | email@example.com | Unknown User | | firstname.lastname@example.org | User 1 | | email@example.com | User 2 | +-------------------+--------------+
[NOT] EXISTS x
x, a subquery enclosed in parentheses, returns empty results. Otherwise, returns
SELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM payments p WHERE c.id=p.customer_id AND p.payment > 10000 )
x [NOT] IN y
IN predicate determines if the expression
x is equal to any values provided by
y, which can be one of:
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')
x IS [NOT] DISTINCT FROM y
x is not equal to
x IS [NOT] NULL
x [NOT] LIKE y
y (which must be of
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
Returns the first non-null value in the argument list.
IF(cond, x, y)
cond is true,