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.

- 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 |
+-------------------+--------------+
```

`[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:

- a subquery, producing exactly one column, enclosed in parentheses
- a list of values, enclosed in parentheses and separated by commas (e.g.
`(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.