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

OperatorDescription
`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` `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 | |-------------------+--------------| | [email protected] | User 1 | | [email protected] | User 2 | | [email protected] | Unknown User | | [email protected] | User 1 | | [email protected] | 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. If `y` is an array, use the `ARRAY_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
``````

`IS DISTINCT FROM` Returns `true` if `x` is not equal to `y`, and `false` otherwise.

### IS NULL

``````x IS [NOT] NULL
``````

`IS NULL` Returns `true` if `x` is `null` or `undefined`.

### IS UNDEFINED

``````x IS [NOT] UNDEFINED
``````

`IS UNDEFINED` Returns `true` if `x` is `undefined`.

### LIKE

``````x [NOT] LIKE y
``````

`string LIKE pattern` Returns `true` if `string` matches the `pattern`, `false` otherwise. You can optionally use `string NOT LIKE pattern` which returns `true` if `string` does not match the `pattern`, `false` otherwise.

``````SELECT 'abc' LIKE 'ab'
``````
false
``````SELECT 'abc' NOT LIKE 'ab'
``````
true

You can use two wildcard values with `LIKE`:

`%` - Represents zero, one, or multiple characters
`_` - Represents a single character

``````SELECT 'abc' LIKE 'ab_'
``````
true
``````SELECT 'abc' LIKE 'a_'
``````
false
``````SELECT 'abc' LIKE 'a%'
``````
true
``````SELECT 'abc' LIKE 'a_c'
``````
true

If you have a literal instance of these wildcard values, be sure to escape them.

``````SELECT 'ab_c' LIKE 'ab\_c'
``````
true

### TRY

``````TRY(expression)
``````

`TRY` 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 commandResult
`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 as `null` in almost all functions, so it is ignored in
`COALESCE` as well.

### IF

`IF(cond, x, y)` Returns `x` if `cond` is true, `y` otherwise.

SQL commandResult
`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 commandResult
`SELECT NULLIF('blue', 'blue')``null `
`SELECT NULLIF('red', 'blue')``red`