CASE WHEN condition THEN expr ELSE expr END

Data & Storage

Meaning

The CASE expression evaluates a condition and returns one of two expressions: the first if the condition is true, the second otherwise. It is used inside SQL statements to perform conditional logic without procedural code.

Primary Function

Conditional expression

Communicative Purpose

Return different values based on a condition within SQL statements

Pattern

CASE WHEN condition THEN then_expr ELSE else_expr END

Core Structure

CASE WHEN ... THEN ... ELSE ... END

Função primária

Conditional expression

Propósito comunicativo

Return different values based on a condition within SQL statements

Situações de gatilho

Selecting different columns based on data, handling NULLs, implementing business logic directly in queries

Contextos

SQL queries (SELECT, UPDATE, INSERT), stored procedures, view definitions

Padrão

CASE WHEN condition THEN then_expr ELSE else_expr END

Estrutura central

CASE WHEN ... THEN ... ELSE ... END

Slots de substituição

condition: boolean expression; then_expr: expression returned when condition is true; else_expr: expression returned when condition is false or unknown

Colocados típicos

SELECT
UPDATE
SET
ORDER BY
GROUP BY

Substituições comuns

Simple CASE: CASE expr WHEN value THEN result ELSE result END
COALESCE for NULL handling

Erros comuns

Omitting END, missing ELSE leading to NULL results, using = instead of WHEN, forgetting to terminate with END

Similar / contraste

IF...THEN...ELSE in procedural languages (differs in being an expression); COALESCE (returns first non-NULL); NULLIF (returns NULL if two expressions equal)

Interferências

Coming from Python: expecting if‑else syntax; SQL CASE is an expression, not a statement, and must be ended with END

Família do chunk

COALESCE
NULLIF
IFNULL
DECODE

Nuance

Conditions are evaluated sequentially; the first true condition determines the result. If no ELSE is provided and no condition matches, the result is NULL. Performance depends on order of conditions.

Efeito pragmático

Allows conditional logic to be expressed directly in SQL, reducing need for application‑side branching and making queries more self‑contained

Dica de memória

Think of a fork in the road: WHEN condition THEN take this path ELSE that

Nota

CASE returns a scalar value within a query and cannot be used as a standalone statement for control flow.

Frequência: HighFormulaicidade: Semi-fixedTipo de construção: code_patternPrioridade de aquisição: Active recallPrioridade de output: BothTag de espaçamento: Immediate

Examples

SELECT employee_id, CASE WHEN salary > 100000 THEN 'High' ELSE 'Normal' END AS salary_tier FROM employees;

UPDATE products SET price = CASE WHEN cost < 10 THEN cost * 1.2 ELSE cost * 1.1 END WHERE category = 'Electronics';

SELECT order_id, CASE WHEN status = 'Shipped' THEN 'Delivered' WHEN status = 'Pending' THEN 'Processing' ELSE 'Unknown' END AS delivery_status FROM orders;