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

If any expression is `null` or `undefined` the result will 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`).

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

### #EXISTS

```[NOT] EXISTS x
```

The `EXISTS` predicate evaluates to true if the specified subquery returns at least one row. Otherwise, if the subquery returns zero rows, the predicate 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 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.

Note that `y` cannot be an array. If `y` is an array, this functionality can be achieved with 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')`

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

## #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 `undefined` behaves the same as `null` in almost all functions, so it is`
`-- ignored in `COALESCE` as well:`
`SELECT`
`    COALESCE(undefined, 10, 'xyz')`
`10`

### #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')`
`Error: If condition must be boolean, not string.`

### #NULLIF

`NULLIF(value1, value2)`

Returns `null` if `value1` equals `value2`, otherwise returns `value1`.

`SELECT`
`    NULLIF('blue', 'blue')`
`null`
`SELECT`
`    NULLIF('red', 'blue')`
`'red'` 