CASE WHEN expression in SQL

CASE is SQL’s “if… then…”. It evaluates a list of conditions in order and returns the value of the first one that is true. It isn’t a statement but an expression, so it can go anywhere a value can: in SELECT, ORDER BY, WHERE or inside an aggregate function.

CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ...
    ELSE defaultValue
END

Three rules worth remembering:

• Conditions are evaluated in order, and evaluation stops at the first true one.
• If none is true and there is no ELSE, the result is NULL.
• Every branch must return the same data type (or compatible types).

Searched CASE

This is the most common form: each WHEN has its own condition. The example labels each order by its total amount:

Note that a $4,304.25 order meets the first two conditions but gets 'Large' because that one is evaluated first. If you swapped the WHEN lines, every order above $1,000 would come out as 'Medium'.

Simple CASE

When every condition compares the same column with specific values, there is a shorter form:

Simple CASE only supports equality. For ranges (>, BETWEEN) or conditions on several columns, use the searched form.

CASE and NULL

A comparison with NULL is never true, so CASE bankAccount WHEN NULL THEN ... doesn’t work. Use the searched form with IS NULL:

If all you need is to replace a NULL with a value, COALESCE is shorter.

CASE inside an aggregate function

This is the use that saves the most time: counting or adding up only the rows that meet a condition, for several conditions in a single query. For example, separating game console sales from everything else:

This technique, sometimes called conditional aggregation, is the basis for turning rows into columns without PIVOT.

CASE in ORDER BY

It also lets you sort with your own criteria. Here, customers without an account go last and the rest are sorted alphabetically:

CASE vs IIF

SQL Server also has IIF(condition, valueIfTrue, valueIfFalse), a shortcut for a CASE with a single condition. CASE is standard SQL and works in every database; IIF only works in SQL Server (and Access). If your code needs to be portable, use CASE.

-- SQL Server only
SELECT product, IIF(totalPrice >= 1000, 'Expensive', 'Cheap') AS priceRange
FROM Orders;

Practice

1. Add an 'Extra large' label for orders over $4,000. Where does the WHEN have to go for it to work, and why?
2. In a single query, count how many 2022 orders were placed before and after August 1.
3. Sort the orders so that the ones without a customer come first.