# Conditional functions and CASE

Conditional expressions select one result from several alternatives. CASE, `iif`/`if` and `coalesce` evaluate only the alternatives needed to choose their result.

## CASE

```text
CASE
    WHEN condition THEN value
    [WHEN condition THEN value ...]
    [ELSE fallback]
END

CASE expression
    WHEN candidate THEN value
    [WHEN candidate THEN value ...]
    [ELSE fallback]
END
```

The first form selects the first SQL-true condition. The second compares one base expression with candidate values. When no branch matches, ELSE is returned; omitting ELSE returns NULL.

### Classifying order amounts

```sql
CREATE TABLE orders (id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (1, 20), (2, 150), (3, NULL);
```

```sql
SELECT id,
       CASE
           WHEN amount IS NULL THEN 'unknown'
           WHEN amount >= 100 THEN 'large'
           ELSE 'standard'
       END AS category
FROM orders
ORDER BY id
FORMAT csv;
```

```text
1,"standard"
2,"large"
3,"unknown"
```

## iif and if

```text
iif(condition, value, ... [, fallback])
if(condition, value, ... [, fallback])
```

These aliases accept one or more condition/value pairs and an optional final fallback. The first true condition selects its value. If nothing matches and no fallback is present, the result is NULL.

### Skipping an unselected expression

The decimal division is not evaluated because the first condition is true:

```sql
SELECT iif(1, 'safe', CAST(1 AS Decimal(18, 2)) / 0),
       if(0, 'unused')
FORMAT csv;
```

```text
"safe",\N
```

## Missing-value functions

| Function                        | Result                                                                          |
| ------------------------------- | ------------------------------------------------------------------------------- |
| `coalesce(value1, value2, ...)` | The first non-NULL value; requires at least two arguments.                      |
| `ifnull(value, fallback)`       | The fallback when value is NULL; exactly two arguments.                         |
| `nullif(value, other)`          | NULL when the two values compare equal, otherwise value; exactly two arguments. |

```sql
SELECT coalesce(NULL, NULL, 'anonymous'),
       ifnull(NULL, 'fallback'),
       nullif(0, 0)
FORMAT csv;
```

```text
"anonymous","fallback",\N
```

See [functions for NULL values](https://coordinatordb.com/docs/reference/functions/regular-functions/functions-for-nulls) for detailed missing-value contracts.