> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Conditional functions

> Conditional expressions choose or normalize values. Their result branches must resolve to compatible types. Explicit casts are useful when mixing numeric, text, or temporal argumen

ReferenceApplies to: Unified engine deployments

Conditional expressions choose or normalize values. Their result branches must resolve to compatible types. Explicit casts are useful when mixing numeric, text, or temporal arguments.

| Function or expression | Returns | Null behavior |
| - | - | - |
| `coalesce(a, b, ...)` | Common argument type | First non-null argument; NULL if all are null |
| `nvl(a,b)`, `ifnull(a,b)` | Common argument type | Alias of coalesce |
| `nullif(a,b)` | Type compatible with a | NULL if a equals b; otherwise a |
| `greatest(a,b,...)` | Common argument type | Greatest comparable value under the resolved function's null rules |
| `least(a,b,...)` | Common argument type | Least comparable value under the resolved function's null rules |
| `CASE WHEN condition THEN a ELSE b END` | Common branch type | A WHEN branch is chosen only when its condition is TRUE |

## Missing labels

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT i_item_sk,
       COALESCE(i_category, 'Unknown') AS category,
       NULLIF(i_category, 'Books') AS category_except_books,
       CASE WHEN i_current_price IS NULL THEN 'Missing'
            WHEN i_current_price < 20 THEN 'Budget'
            ELSE 'Standard' END AS price_band
FROM demo.tpcds.item
ORDER BY i_item_sk
LIMIT 10;
```

## Bounding a value

Use values of the intended common type. This example compares prices as DOUBLE.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT i_item_sk,
       GREATEST(CAST(i_current_price AS DOUBLE), 10.0) AS minimum_ten,
       LEAST(CAST(i_current_price AS DOUBLE), 50.0) AS maximum_fifty
FROM demo.tpcds.item
WHERE i_current_price IS NOT NULL
ORDER BY i_item_sk
LIMIT 5;
```

COALESCE can normalize a missing grouping label, but replacing NULL with a real business key can change join semantics. Keep missing-value presentation separate from matching keys when that distinction matters.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.