This page covers conditional operators and functions in Rockset.
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.
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
.
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).
x [NOT] BETWEEN a AND b
This is equivalent to the expression below (without NOT
).
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.
expression
is not given, expressions following WHEN
are evaluated as boolean expressions.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 |
+-------------------+--------------+
[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
)
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:
(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')
x IS [NOT] DISTINCT FROM y
Returns true
if x
is not equal to y
, and false
otherwise.
x IS [NOT] NULL
Returns true
if x
is null
.
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
COALESCE(x, ...)
Returns the first non-null value in the argument list.
IF(cond, x, y)
Returns x
if cond
is true, y
otherwise.