CASE

Returns a result based on conditional evaluation, similar to if-then-else logic.

Syntax

CASE expressions use conditional evaluation:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Parameters

condition

A boolean expression evaluated for each WHEN clause.

result

The value to return if the corresponding condition is TRUE. Can be any expression, column, or literal.

ELSE result (optional)

The default value returned if no conditions are TRUE. If omitted and no conditions match, returns NULL.

Returns

Returns the result of the first matching WHEN clause, or the ELSE value if no matches are found, or NULL if no ELSE clause is specified and no matches are found.

All result expressions must be of compatible types (or NULL).

Examples

Setup

For these examples, assume we have a products table:

CREATE TABLE products(
    id BIGINT,
    name STRING,
    category STRING,
    price BIGINT,
    stock INTEGER,
    PRIMARY KEY(id))

INSERT INTO products VALUES
    (1, 'Widget A', 'Electronics', 100, 50),
    (2, 'Widget B', 'Electronics', 150, 5),
    (3, 'Gadget X', 'Electronics', 200, 0),
    (4, 'Tool A', 'Hardware', 80, 100),
    (5, 'Tool B', 'Hardware', 120, 15)

Simple Condition

Categorize products by stock level:

SELECT name,
       stock,
       CASE
           WHEN stock = 0 THEN 'Out of Stock'
           WHEN stock < 10 THEN 'Low Stock'
           WHEN stock < 50 THEN 'In Stock'
           ELSE 'Well Stocked'
       END AS stock_status
FROM products

name	stock	stock_status
"Widget A"	50	"Well Stocked"
"Widget B"	5	"Low Stock"
"Gadget X"	0	"Out of Stock"
"Tool A"	100	"Well Stocked"
"Tool B"	15	"In Stock"

Multiple Conditions

Calculate discount based on price and category:

SELECT name,
       price,
       CASE
           WHEN category = 'Electronics' AND price > 150 THEN price * 0.85
           WHEN category = 'Electronics' THEN price * 0.90
           WHEN price > 100 THEN price * 0.95
           ELSE price
       END AS discounted_price
FROM products

name	price	discounted_price
"Widget A"	100	90.0
"Widget B"	150	135.0
"Gadget X"	200	170.0
"Tool A"	80	80.0
"Tool B"	120	114.0

Value Matching with CASE

Map category codes to names using WHEN conditions:

SELECT name,
       category,
       CASE
           WHEN category = 'Electronics' THEN 'E'
           WHEN category = 'Hardware' THEN 'H'
           WHEN category = 'Media' THEN 'M'
           ELSE 'Other'
       END AS category_code
FROM products

name	category	category_code
"Widget A"	"Electronics"	"E"
"Widget B"	"Electronics"	"E"
"Gadget X"	"Electronics"	"E"
"Tool A"	"Hardware"	"H"
"Tool B"	"Hardware"	"H"

Nested CASE

CASE expressions can be nested:

SELECT name,
       price,
       stock,
       CASE
           WHEN stock = 0 THEN 'Unavailable'
           ELSE CASE
               WHEN price < 100 THEN 'Budget'
               WHEN price < 150 THEN 'Standard'
               ELSE 'Premium'
           END
       END AS product_tier
FROM products

name	price	stock	product_tier
"Widget A"	100	50	"Standard"
"Widget B"	150	5	"Premium"
"Gadget X"	200	0	"Unavailable"
"Tool A"	80	100	"Budget"
"Tool B"	120	15	"Standard"

Important Notes

Evaluation Order

CASE evaluates conditions in order and returns the first matching result. Once a match is found, remaining conditions are not evaluated:

CASE
    WHEN x > 100 THEN 'High'      -- Evaluated first
    WHEN x > 50 THEN 'Medium'      -- Only checked if first is false
    WHEN x > 0 THEN 'Low'          -- Only checked if previous are false
    ELSE 'Zero or Negative'
END

Order your conditions from most specific to least specific.

NULL Handling

NULL values in conditions are treated as FALSE:

-- If price IS NULL, condition is FALSE
CASE
    WHEN price > 100 THEN 'Expensive'
    ELSE 'Other'
END

To explicitly check for NULL:

CASE
    WHEN price IS NULL THEN 'No Price'
    WHEN price > 100 THEN 'Expensive'
    ELSE 'Affordable'
END

Type Compatibility

Important

All result expressions in a CASE statement must return compatible types. Mixing types (e.g., strings and numbers) will cause an error.

-- VALID: All results are strings
CASE
    WHEN x > 100 THEN 'High'
    WHEN x > 50 THEN 'Medium'
    ELSE 'Low'
END

-- INVALID: Mixed types (string and number)
CASE
    WHEN x > 100 THEN 'High'
    ELSE 0
END

See Also

• Comparison Operators - Operators used in CASE conditions