SQL WHEN

What is the SQL WHEN clause?

Defines a single conditional branch inside a CASE expression and returns its associated result when the predicate evaluates to TRUE.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL WHEN: Supported by PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, BigQuery, DB2, Teradata, Vertica and other ANSI-compliant systems.

SQL WHEN Full Explanation

WHEN is not a standalone SQL statement; it is a keyword that lives inside a CASE expression (both simple and searched forms). Each CASE can contain one or more WHEN clauses that are evaluated sequentially from top to bottom. For a simple CASE, the WHEN value is compared to the CASE input expression using =. For a searched CASE, the WHEN is followed by any Boolean predicate. The first WHEN whose condition is TRUE (not NULL) causes the associated THEN expression to be returned. If no WHEN matches, control falls through to an optional ELSE branch; without ELSE, the CASE returns NULL. Only one WHEN branch is ever taken because evaluation stops after the first match. WHEN also appears in a few dialect-specific constructs, such as triggers in SQLite (CREATE TRIGGER … WHEN) and exception handling in PL/SQL (WHEN OTHERS). This documentation focuses on its standard usage in CASE expressions, which is portable across major databases. Key caveats: evaluation is short-circuited, predicate order matters, and NULL comparisons in simple CASE do not match unless the CASE expression itself is NULL and a WHEN NULL branch is provided.

SQL WHEN Syntax

-- Searched CASE
CASE
    WHEN boolean_condition_1 THEN result_1
    WHEN boolean_condition_2 THEN result_2
    [...]
    [ELSE default_result]
END

-- Simple CASE
CASE input_expression
    WHEN compare_value_1 THEN result_1
    WHEN compare_value_2 THEN result_2
    [...]
    [ELSE default_result]
END;

SQL WHEN Parameters

condition (BOOLEAN) - Predicate evaluated for a searched CASE.
compare_value (ANY COMPARABLE) - Value compared against input_expression in a simple CASE.
result (ANY) - Expression returned when its WHEN branch matches.

Example Queries Using SQL WHEN

-- Classify order size
SELECT order_id,
       CASE
           WHEN total_amount >= 1000 THEN 'Large'
           WHEN total_amount >= 500  THEN 'Medium'
           ELSE 'Small'
       END AS order_size
FROM   orders;

-- Simple CASE to map status codes
SELECT user_id,
       CASE status_code
           WHEN 0 THEN 'Inactive'
           WHEN 1 THEN 'Active'
           WHEN 2 THEN 'Banned'
           ELSE 'Unknown'
       END AS status_text
FROM   users;

Expected Output Using SQL WHEN

First query returns a new column order_size with values Large, Medium, or Small depending on each row's total_amount
Second query converts numeric status codes into readable text

Use Cases with SQL WHEN

Deriving categorical labels from numeric ranges
Replacing nested IF/ELSE logic in SELECT lists
Building computed columns in views or materialized views
Creating dynamic ORDER BY or GROUP BY expressions
Implementing policy-driven row level logic inside queries

Common Mistakes with SQL WHEN

Omitting ELSE and unintentionally returning NULL for unmatched rows
Placing more specific WHEN predicates after broader ones, preventing them from ever matching
Expecting multiple WHEN branches to execute; evaluation stops after the first TRUE
Comparing NULL values in simple CASE without a dedicated WHEN NULL branch
Mixing incompatible data types in THEN results, causing type-coercion errors

First Introduced In

SQL-92 standard

Frequently Asked Questions

What does WHEN do inside a CASE expression?

It introduces a condition to test. If the condition is met, the corresponding THEN result is returned and the rest of the CASE is skipped.

Can I use multiple WHEN clauses?

Yes. Place them sequentially. They are checked in order until one condition is TRUE.

What happens if no WHEN condition matches?

If you included an ELSE branch, its expression is returned. Without ELSE, the CASE expression returns NULL.

Is WHEN ANSI-standard SQL?

Yes. WHEN inside CASE was added in SQL-92 and is implemented by all major relational databases.