SQL Reference > Conditional Expressions

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

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

### #Comparison Operators

Rockset supports basic comparison operators show 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, but the result will be `null` if the two sides are of different types (except two numeric types is allowed).

### #BETWEEN

``x [NOT] BETWEEN a AND b``

This is equivalent to the expression below (without `NOT`).

### #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'
FROM
_events
LIMIT
5``````
``````SELECT
userEmail,
CASE
REGEXP_EXTRACT(userEmail, '(.*?)@', 1)
WHEN 'user1' THEN 'User 1'
WHEN 'user2' THEN 'User 2'
ELSE 'Unknown User'
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``

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

### #IN

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

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

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

## #Functions

### #COALESCE

``COALESCE(x, ...)``

Returns the first non-null value in the argument list.

``````SELECT
COALESCE(null, 10, 'xyz')``````
``10``
``````SELECT
COALESCE(null, 'xyz', 10)``````
``'xyz'``
``````SELECT
COALESCE('xyz', null, 10.5)``````
``'xyz'``
``````-- Note that null_value is JSON null which is different from SQL null and hence
-- is considered non-null in the COALESCE operation.
SELECT
COALESCE(null_value, 10, 'xyz')``````
``null``

### #IF

``IF(cond, x, y)``

Returns `x` if `cond` is true, `y` otherwise.

``````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')``````
``10``
``````SELECT
IF('', 10, 'xyz')``````
``'xyz'``