Lesson 23 of 26 · see the practice database

CASE: if, then, else in a query

CASE is SQL's way of saying "if this, then that, otherwise something else". It lets a query turn values into labels, sort rows into bands, and count different things in different columns. Once you know it, you will find a use for it in most reports.

The shape of CASE

SELECT name, price,
       CASE
         WHEN price >= 1000 THEN 'premium'
         WHEN price >= 300  THEN 'mid-range'
         ELSE 'budget'
       END AS band
FROM products
ORDER BY price DESC;

A CASE starts with the word CASE and ends with END. In between:

• each WHEN condition THEN result is tested in order, from the top;
• the first condition that is true wins, and the rest are skipped;
• ELSE gives the result when no condition is true.

Because the first match wins, the order of the WHEN lines matters. A price of 1,450 is also greater than 300, but the first test catches it. Put the narrowest condition first.

CASE produces a value, so it can go anywhere a value can: in the SELECT list, in WHERE, in ORDER BY, inside a function.

Without ELSE you get NULL

If nothing matches and there is no ELSE, the result is NULL.

SELECT name, stock,
       CASE WHEN stock = 0 THEN 'sold out' END AS note
FROM products
WHERE category = 'Electronics';

Writing an ELSE every time is a good habit, even if it only says ELSE 'other'. It shows that you thought about the remaining cases.

The short form

When every test compares the same column with a fixed value, name the column once after CASE.

SELECT id, status,
       CASE status
         WHEN 'delivered' THEN 'Done'
         WHEN 'shipped'   THEN 'On the way'
         WHEN 'pending'   THEN 'Waiting'
         ELSE 'Stopped'
       END AS meaning
FROM orders
LIMIT 6;

The short form can only test for equality. For ranges, NULL checks or anything with AND, use the full form.

Dealing with NULL

CASE can replace a missing value with a label. For this simple job COALESCE is shorter, but CASE lets you do more.

SELECT name,
       CASE WHEN email IS NULL THEN 'ask for email' ELSE 'has email' END AS email_status
FROM customers
ORDER BY name
LIMIT 5;

Grouping by a CASE

Label the rows, then count by label. This is how bands and brackets are reported.

SELECT CASE
         WHEN salary >= 50000 THEN '50,000 and above'
         WHEN salary >= 35000 THEN '35,000 to 49,999'
         ELSE 'below 35,000'
       END AS salary_band,
       COUNT(*) AS staff
FROM employees
GROUP BY 1
ORDER BY MIN(salary) DESC;

GROUP BY 1 means "group by the first column of the SELECT list". It saves repeating the whole CASE. SQLite, MySQL and PostgreSQL accept it. In SQL Server and Oracle, repeat the CASE in the GROUP BY.

Conditional counting

This is the most valuable CASE pattern. Put a CASE inside an aggregate, and one query can count several different things side by side.

SELECT COUNT(*) AS all_orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN status IN ('pending', 'shipped') THEN 1 ELSE 0 END) AS in_progress
FROM orders;

Each CASE turns a row into 1 or 0, and SUM adds the ones. Without CASE this would take four separate queries.

Combine it with GROUP BY and you get a cross table: one row per customer, one column per status.

SELECT c.name,
       SUM(CASE WHEN o.status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN o.status IN ('pending', 'shipped') THEN 1 ELSE 0 END) AS open
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;

Turning rows into columns like this is called pivoting.

Conditional totals

Return an amount in place of the 1 and you get totals split by condition.

SELECT p.category,
       SUM(CASE WHEN o.status = 'delivered' THEN oi.quantity * p.price ELSE 0 END) AS delivered_sales,
       SUM(CASE WHEN o.status = 'cancelled' THEN oi.quantity * p.price ELSE 0 END) AS cancelled_sales
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o   ON oi.order_id = o.id
GROUP BY p.category
ORDER BY p.category;

A percentage

The average of ones and zeros is the share of ones. Multiply by 100 for a percentage. Note the 1.0, which keeps the decimals.

SELECT ROUND(100 * AVG(CASE WHEN status = 'delivered' THEN 1.0 ELSE 0 END), 1) AS percent_delivered
FROM orders;

A custom sort order

Alphabetical order is rarely the order a business wants. CASE in ORDER BY gives each value a rank of your choosing.

SELECT id, status, order_date
FROM orders
ORDER BY CASE status
           WHEN 'pending'   THEN 1
           WHEN 'shipped'   THEN 2
           WHEN 'delivered' THEN 3
           ELSE 4
         END,
         order_date
LIMIT 8;

Pending orders, the ones needing attention, come first.

Avoiding division by zero

A CASE can guard a division.

SELECT name, price, stock,
       CASE WHEN stock = 0 THEN NULL ELSE price * 1.0 / stock END AS price_per_unit_in_stock
FROM products
WHERE category = 'Grocery';

A shorter way is the function NULLIF(a, b), which returns NULL when the two values are equal: price / NULLIF(stock, 0). In SQLite and MySQL, dividing by zero quietly gives NULL anyway. PostgreSQL, SQL Server and Oracle stop with an error, so the guard matters there.

CASE in an UPDATE

A single UPDATE can change different rows in different ways.

UPDATE products
SET price = CASE
              WHEN category = 'Grocery' THEN price + 5
              WHEN category = 'Home'    THEN price - 50
              ELSE price
            END;

Do not forget the ELSE price. Without it, every product outside the two categories would have its price set to NULL.

Common mistakes

• Forgetting END. Every CASE needs one.
• Putting a wide condition above a narrow one, so that the narrow one is never reached.
• Mixing types in the results, such as a number in one branch and text in another. Strict databases refuse.
• Leaving out ELSE in an UPDATE.

Try it yourself

SELECT name, CASE WHEN salary >= 45000 THEN 'senior' ELSE 'junior' END AS level FROM employees;

SELECT SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END), SUM(CASE WHEN stock = 0 THEN 1 ELSE 0 END) FROM products;

SELECT name, CASE WHEN stock = 0 THEN 'none' WHEN stock < 25 THEN 'low' ELSE 'fine' END AS stock_note FROM products;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.