CASE Expressions

Programming languages have if and else. SQL has the CASE expression. It looks at each row, checks conditions in order, and returns a value depending on which condition is true. With it you can turn prices into labels like "Budget" and "Premium", translate status codes into friendly text, sort in any order you like, and count different kinds of rows in one query.

In this lesson you will write both forms of CASE, the searched form and the simple form, and then use CASE inside aggregate functions for conditional counting, one of the most useful tricks in reporting.

Searched CASE

The searched form tests a full condition in each WHEN:

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

MySQL checks the WHEN conditions from top to bottom and returns the result of the first one that is true. If none is true, it returns the ELSE value. The whole thing ends with END, and it is usually given an alias. Here the shop puts each product into a price band:

SELECT product_name, price,
       CASE
         WHEN price < 1000 THEN 'Budget'
         WHEN price < 5000 THEN 'Mid-range'
         ELSE 'Premium'
       END AS price_band
FROM products
ORDER BY price;

product_name                | price | price_band
----------------------------+-------+-----------
Python Basics (Book)        | 450   | Budget
64GB Pen Drive              | 549   | Budget
Wireless Mouse              | 799   | Budget
Laptop Stand                | 1299  | Mid-range
USB-C Hub                   | 1899  | Mid-range
HD Webcam                   | 2199  | Mid-range
Bluetooth Speaker           | 2499  | Mid-range
Mechanical Keyboard         | 3499  | Mid-range
Graphics Tablet             | 4599  | Mid-range
1TB External SSD            | 6499  | Premium
Noise-Cancelling Headphones | 8999  | Premium
27-inch Monitor             | 15999 | Premium
(12 rows)

The Wireless Mouse at ₹799 matches the first condition, so it is Budget and the second condition is never checked. That is why the second WHEN only needs price < 5000, not price >= 1000 AND price < 5000. The order of the WHEN lines therefore matters.

Simple CASE

When every test compares the same column with different values, the simple form is shorter. You name the column once after CASE, then list the values:

SELECT order_id, status,
       CASE status
         WHEN 'delivered' THEN 'Delivered'
         WHEN 'shipped' THEN 'On the way'
         WHEN 'pending' THEN 'Being packed'
         ELSE 'Cancelled'
       END AS customer_message
FROM orders
WHERE customer_id IN (1, 3, 4);

order_id | status    | customer_message
---------+-----------+-----------------
101      | delivered | Delivered
103      | delivered | Delivered
104      | delivered | Delivered
105      | cancelled | Cancelled
113      | shipped   | On the way
115      | pending   | Being packed
(6 rows)

Each WHEN 'value' means "when status = 'value'". The simple form can only test equality, so for ranges like the price bands you need the searched form.

Without ELSE you get NULL

ELSE is optional. If you leave it out and no WHEN matches, the result is NULL. That can be useful when you only want to flag a few rows:

SELECT product_name, stock,
       CASE
         WHEN stock = 0 THEN 'Out of stock'
         WHEN stock < 20 THEN 'Low stock'
       END AS alert
FROM products
ORDER BY stock
LIMIT 4;

product_name                | stock | alert
----------------------------+-------+-------------
HD Webcam                   | 0     | Out of stock
Graphics Tablet             | 12    | Low stock
27-inch Monitor             | 18    | Low stock
Noise-Cancelling Headphones | 25    | NULL
(4 rows)

The headphones have 25 units, so no condition is true and alert is NULL. Most of the time, though, an ELSE makes the result clearer.

CASE in ORDER BY

CASE can appear anywhere a value can, including ORDER BY. Alphabetical order is not always the order people want. The warehouse wants pending orders at the top, then shipped, then delivered, then cancelled:

SELECT order_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;

order_id | status    | order_date
---------+-----------+-----------
111      | pending   | 2025-04-10
115      | pending   | 2025-05-20
108      | shipped   | 2025-03-15
113      | shipped   | 2025-05-02
101      | delivered | 2025-01-05
102      | delivered | 2025-01-12
103      | delivered | 2025-01-20
104      | delivered | 2025-02-02
106      | delivered | 2025-02-28
107      | delivered | 2025-03-09
109      | delivered | 2025-03-22
110      | delivered | 2025-04-01
112      | delivered | 2025-04-18
114      | delivered | 2025-05-11
105      | cancelled | 2025-02-14
(15 rows)

Each status is turned into a number, and the rows are sorted by that number. Orders 111 and 115 (pending) come first, then 108 and 113 (shipped), and so on. order_date then sorts the orders inside each status.

Conditional counting

This is where CASE really shines. Put it inside SUM() and you can count only the rows that meet a condition, several different conditions in one query. Each CASE gives 1 for a row that matches and 0 for one that does not, and SUM adds them up:

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

all_orders | delivered | open_orders | cancelled
-----------+-----------+-------------+----------
15         | 10        | 4           | 1
(1 row)

The last column shows a second style. COUNT skips NULLs, and a CASE without ELSE gives NULL for non-matching rows, so COUNT(CASE WHEN ... THEN 1 END) counts only the matches. Both styles are common; pick one and use it consistently.

Combined with GROUP BY, this turns rows into columns, which is how most dashboard tables are built:

SELECT category,
       COUNT(*) AS products,
       SUM(CASE WHEN stock = 0 THEN 1 ELSE 0 END) AS out_of_stock,
       SUM(CASE WHEN stock BETWEEN 1 AND 20 THEN 1 ELSE 0 END) AS low_stock
FROM products
GROUP BY category
ORDER BY category;

category    | products | out_of_stock | low_stock
------------+----------+--------------+----------
Accessories | 6        | 1            | 1
Audio       | 2        | 0            | 0
Books       | 1        | 0            | 0
Displays    | 1        | 0            | 1
Storage     | 2        | 0            | 0
(5 rows)

Common mistakes

• Forgetting END. Every CASE must close with END, or you get a syntax error that points somewhere confusing. Write CASE and END first, then fill in the middle.
• Putting WHEN lines in the wrong order. If WHEN price < 5000 comes before WHEN price < 1000, the Budget band can never be reached. Put the narrowest conditions first.
• Testing NULL with simple CASE. CASE manager_id WHEN NULL THEN 'Top boss' END never matches, because it compares with =. Use the searched form: CASE WHEN manager_id IS NULL THEN 'Top boss' END.
• Mixing result types. Returning text from one WHEN and a number from another forces MySQL to convert values. Keep all results of one CASE the same type.

Key points

• CASE returns a value based on conditions, checked from top to bottom; the first true one wins.
• The searched form (CASE WHEN condition) can test anything; the simple form (CASE column WHEN value) tests equality.
• Without ELSE, a row that matches nothing gets NULL.
• CASE can be used in SELECT, ORDER BY, WHERE and inside aggregate functions.
• SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts the rows that meet a condition.

SELECT department,
       COUNT(*) AS staff,
       SUM(CASE WHEN salary >= 100000 THEN 1 ELSE 0 END) AS high_earners,
       CASE WHEN COUNT(*) >= 3 THEN 'Large' ELSE 'Small' END AS band
FROM employees
GROUP BY department
ORDER BY department;