WHEN

Evaluate condition-value pairs and return the value of the first TRUE condition.

Category: conditionalReturns: ANYDialect: Standard

Syntax

WHEN(condition1, value1, condition2, value2, ..., [default_value])

Description

## Overview Evaluates a sequence of condition-value pairs from left to right and returns the value paired with the first TRUE condition. If the total argument count is odd, the final argument is treated as a default and returned when no condition matches; if even and no condition matches, the function returns NULL. WHEN is a function-call form of the SQL CASE expression, intended for inline multi-branch conditionals that would otherwise require many nested IFs. ## Behavior - Conditions are evaluated in order; only the first TRUE condition's value is returned. - NULL conditions are treated as not TRUE (not matched). - All value arguments must be coercible to a common supertype. - Short-circuits after the first match; remaining conditions and values are not guaranteed to be evaluated. - Returns NULL when no condition is TRUE and no default value is provided. - Odd argument count: the last argument is the default. Even argument count: no default. ## Compatibility - WHEN is a shorthand for CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ... ELSE default END. - CASE is the portable form across SQL engines; WHEN may not be available everywhere. Prefer CASE for code that targets multiple dialects.

Parameters

Name	Type	Description
`condition`		Specifies a boolean expression. Conditions are tested left-to-right; the value associated with the first TRUE condition is returned.
`value`		Specifies the value to return when the preceding condition is TRUE. All value arguments (including the optional default) must be coercible to a common supertype.

Examples

-- Two-branch with default
SELECT WHEN(1 > 0, 'positive', 1 < 0, 'negative', 'zero') AS v;

-- Multi-branch grading
SELECT student_id,
       WHEN(score >= 90, 'A',
            score >= 80, 'B',
            score >= 70, 'C',
            'F') AS grade
FROM education.grades.fall_2026;

-- NULL handling (NULL is treated as not TRUE)
SELECT WHEN(val IS NOT NULL, val * 2, 0) AS out
FROM (VALUES (5), (NULL)) AS t(val);

-- Bucket categorization
SELECT reading_id,
       WHEN(temp > 30, 'hot',
            temp > 20, 'warm',
            temp > 10, 'cool',
            'cold') AS category
FROM iot.sensors.readings;

-- No match and no default returns NULL
SELECT WHEN(1 > 2, 'yes') AS v;  -- NULL

-- Realistic: billing tier lookup
SELECT account_id,
       WHEN(monthly_spend >= 10000, 'enterprise',
            monthly_spend >= 1000, 'business',
            monthly_spend >= 100, 'pro',
            'free') AS tier
FROM billing.accounts.usage;

Pitfalls

If the argument count is even and no condition matches, the result is NULL, not an error. Always provide a default (odd argument count) when a NULL result would be wrong.
Order matters: the first TRUE condition wins, so put the most specific conditions first. Overlapping ranges (for example score >= 80 before score >= 90) mask later branches.
NULL conditions are treated as not TRUE; the value for that branch is skipped. To branch on NULL explicitly, use IS NULL.
All value arguments must share a common supertype; mixing STRING and numeric raises a type error.
Not portable across all SQL engines. Use CASE WHEN ... THEN ... ELSE ... END when cross-engine compatibility is required.