# Conditional Expressions

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

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