Skip to content
Coordinator DB
View as Markdown
View as Markdown

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 for detailed missing-value contracts.