Chapter 24 of 35

CASE Expression

Every programming language has if-else. SQL has one too, and it works inside a query: the CASE expression. With CASE you can turn salaries into "High / Medium / Low" bands, show friendly labels instead of codes, sort rows in a custom order, count several categories in a single pass, and update different rows by different amounts in one statement.

CASE shows up all the time in real reports and in interviews ("pivot this table", "count delivered and cancelled orders in one query"). In this chapter we will learn its two forms, how it is evaluated, where it can be used, and the NULL-related traps that make it return surprising results.

All examples use the shared company_db sample database, and every output shown is real MySQL 8.4 output.


What is the CASE Expression?

The CASE expression evaluates a list of conditions in order and returns the result of the first condition that is TRUE. If none is TRUE, it returns the ELSE value, or NULL if there is no ELSE.

The word expression is important. CASE is not a statement that "does" something; it produces a value, just like salary * 12 or CONCAT(...). So it can go anywhere a value can go: the SELECT list, WHERE, ORDER BY, GROUP BY, inside aggregate functions, and in UPDATE's SET.

CASE comes in two forms:

-- 1. Searched CASE: each WHEN has its own condition
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

-- 2. Simple CASE: compare one expression with several values
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

Every CASE ends with END. Forgetting it is the most common syntax error.


Searched CASE

The searched form is the general one: each WHEN can test any condition (ranges, IS NULL, AND/OR, LIKE ...). Let us put each employee into a salary band:

SELECT name, salary,
       CASE
           WHEN salary >= 100000 THEN 'High'
           WHEN salary >= 70000  THEN 'Medium'
           ELSE 'Low'
       END AS salary_band
FROM employees;
+--------------+-----------+-------------+
| name         | salary    | salary_band |
+--------------+-----------+-------------+
| Aarav Sharma | 150000.00 | High        |
| Priya Iyer   |  95000.00 | Medium      |
| Rohan Mehta  |  95000.00 | Medium      |
| Sneha Kapoor | 110000.00 | High        |
| Vikram Singh |  62000.00 | Low         |
| Ananya Das   |  58000.00 | Low         |
| Karan Patel  |  70000.00 | Medium      |
| Meera Nair   |  48000.00 | Low         |
| Arjun Reddy  |  82000.00 | Medium      |
| Ishita Gupta |  55000.00 | Low         |
+--------------+-----------+-------------+
10 rows in set (0.00 sec)

How is Priya (95,000) evaluated?

WHEN salary >= 100000 ?   95000 >= 100000 -> FALSE, try next
WHEN salary >= 70000  ?   95000 >= 70000  -> TRUE,  return 'Medium' and STOP

Notice that the second condition does not say salary >= 70000 AND salary < 100000. It does not need to: anyone with 100,000 or more was already caught by the first WHEN. This works only because CASE checks the conditions top to bottom and stops at the first TRUE one.

Order of WHEN clauses matters

Swap the two WHENs and watch what happens:

SELECT name, salary,
       CASE
           WHEN salary >= 70000  THEN 'Medium'   -- wrong order!
           WHEN salary >= 100000 THEN 'High'
           ELSE 'Low'
       END AS salary_band
FROM employees
WHERE dept_id IN (10, 20);
+--------------+-----------+-------------+
| name         | salary    | salary_band |
+--------------+-----------+-------------+
| Aarav Sharma | 150000.00 | Medium      |
| Priya Iyer   |  95000.00 | Medium      |
| Rohan Mehta  |  95000.00 | Medium      |
| Sneha Kapoor | 110000.00 | Medium      |
| Vikram Singh |  62000.00 | Low         |
| Ananya Das   |  58000.00 | Low         |
+--------------+-----------+-------------+
6 rows in set (0.00 sec)

Nobody is "High" any more. 150,000 is also >= 70,000, so the first WHEN matches and the "High" branch is never reached. No error, just wrong data. When conditions overlap, put the most specific (narrowest) one first.


Simple CASE

When every condition is "is this column equal to some value?", the simple form is shorter. Let us show friendly labels for order statuses:

SELECT order_id, status,
       CASE status
           WHEN 'Delivered' THEN 'Completed'
           WHEN 'Shipped'   THEN 'On the way'
           WHEN 'Pending'   THEN 'Not shipped yet'
           ELSE 'Closed'
       END AS status_label
FROM orders;
+----------+-----------+-----------------+
| order_id | status    | status_label    |
+----------+-----------+-----------------+
|     1001 | Delivered | Completed       |
|     1002 | Delivered | Completed       |
|     1003 | Cancelled | Closed          |
|     1004 | Delivered | Completed       |
|     1005 | Shipped   | On the way      |
|     1006 | Delivered | Completed       |
|     1007 | Pending   | Not shipped yet |
|     1008 | Pending   | Not shipped yet |
+----------+-----------+-----------------+
8 rows in set (0.00 sec)

CASE status WHEN 'Delivered' ... means exactly CASE WHEN status = 'Delivered' .... The simple form can only test equality; for ranges, LIKE, IS NULL or several columns, use the searched form.

Simple CASE

Searched CASE

Syntax

CASE expr WHEN value THEN ...

CASE WHEN condition THEN ...

Tests

Equality only (expr = value)

Any condition

Can check NULL?

No

Yes, with IS NULL

Can use several columns?

No, one expression

Yes

Best for

Mapping codes to labels

Ranges, complex rules


CASE and NULL

Missing ELSE returns NULL

If no WHEN matches and there is no ELSE, the result is NULL:

SELECT name, salary,
       CASE
           WHEN salary >= 100000 THEN 'High'
           WHEN salary >= 70000  THEN 'Medium'
       END AS salary_band
FROM employees
WHERE dept_id IN (20, 30);
+--------------+-----------+-------------+
| name         | salary    | salary_band |
+--------------+-----------+-------------+
| Sneha Kapoor | 110000.00 | High        |
| Vikram Singh |  62000.00 | NULL        |
| Ananya Das   |  58000.00 | NULL        |
| Karan Patel  |  70000.00 | Medium      |
| Meera Nair   |  48000.00 | NULL        |
+--------------+-----------+-------------+
5 rows in set (0.00 sec)

Sometimes NULL is what you want (we will use this deliberately with COUNT), but usually a missing ELSE is an oversight. A good habit is to always write an ELSE, even if it is ELSE NULL, so the reader knows it was intentional.

Simple CASE cannot match NULL

Ishita's city is NULL. Try labelling it with the simple form:

SELECT name, city,
       CASE city WHEN NULL THEN 'Unknown' ELSE city END AS wrong_way,
       CASE WHEN city IS NULL THEN 'Unknown' ELSE city END AS right_way
FROM employees
WHERE emp_id IN (9, 10);
+--------------+-----------+-----------+-----------+
| name         | city      | wrong_way | right_way |
+--------------+-----------+-----------+-----------+
| Arjun Reddy  | Hyderabad | Hyderabad | Hyderabad |
| Ishita Gupta | NULL      | NULL      | Unknown   |
+--------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

CASE city WHEN NULL means city = NULL, which is UNKNOWN (never TRUE), as we learnt in the NULL Values chapter. The WHEN never matches, so it falls to ELSE city, which is NULL. Always use the searched form with IS NULL. (For the simple "replace NULL with a default" job, COALESCE(city, 'Unknown') from the NULL Values chapter is even shorter. In fact, COALESCE is shorthand for a searched CASE.)


Multiple Conditions and Multiple CASEs

A WHEN can combine columns, and a query can contain several CASE expressions:

SELECT name, hire_date,
       CASE
           WHEN hire_date < '2019-01-01' THEN 'Senior'
           WHEN hire_date < '2022-01-01' THEN 'Mid'
           ELSE 'Junior'
       END AS tenure,
       CASE
           WHEN dept_id IS NULL    THEN 'Unassigned'
           WHEN manager_id IS NULL THEN 'Top level'
           ELSE CONCAT('Reports to ', manager_id)
       END AS position
FROM employees
ORDER BY hire_date;
+--------------+------------+--------+--------------+
| name         | hire_date  | tenure | position     |
+--------------+------------+--------+--------------+
| Sneha Kapoor | 2017-11-20 | Senior | Reports to 1 |
| Aarav Sharma | 2018-04-12 | Senior | Top level    |
| Priya Iyer   | 2019-07-01 | Mid    | Reports to 1 |
| Karan Patel  | 2019-09-23 | Mid    | Reports to 1 |
| Rohan Mehta  | 2020-01-15 | Mid    | Reports to 1 |
| Arjun Reddy  | 2020-10-05 | Mid    | Reports to 1 |
| Vikram Singh | 2021-03-08 | Mid    | Reports to 4 |
| Ananya Das   | 2022-06-30 | Junior | Reports to 4 |
| Meera Nair   | 2023-02-14 | Junior | Reports to 7 |
| Ishita Gupta | 2024-01-10 | Junior | Unassigned   |
+--------------+------------+--------+--------------+
10 rows in set (0.00 sec)

The THEN and ELSE parts can be any expression, including function calls like CONCAT and column values, not just fixed text. You can even nest a CASE inside another CASE, though deeply nested CASEs quickly become hard to read.


CASE in ORDER BY: Custom Sort Order

Sorting by status alphabetically gives Cancelled, Delivered, Pending, Shipped. But an operations team wants to see orders in workflow order: Pending first, then Shipped, Delivered, and Cancelled last. Map each status to a number and sort by that number:

SELECT order_id, status, amount
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    | amount  |
+----------+-----------+---------+
|     1007 | Pending   | 5600.00 |
|     1008 | Pending   |  800.00 |
|     1005 | Shipped   |  950.00 |
|     1001 | Delivered | 2500.00 |
|     1002 | Delivered | 4800.00 |
|     1004 | Delivered | 7300.00 |
|     1006 | Delivered | 3100.00 |
|     1003 | Cancelled | 1200.00 |
+----------+-----------+---------+
8 rows in set (0.00 sec)

The CASE value (1, 2, 3, 4) is used only for sorting; it is not displayed. Within the same status, rows are sorted by order_date. MySQL also has a shortcut, ORDER BY FIELD(status, 'Pending', 'Shipped', 'Delivered'), but CASE works in every database.


CASE in GROUP BY: Grouping into Buckets

How many employees fall into each salary band?

SELECT CASE
           WHEN salary >= 100000 THEN 'High'
           WHEN salary >= 70000  THEN 'Medium'
           ELSE 'Low'
       END AS salary_band,
       COUNT(*)    AS employees,
       MIN(salary) AS min_sal,
       MAX(salary) AS max_sal
FROM employees
GROUP BY salary_band
ORDER BY min_sal DESC;
+-------------+-----------+-----------+-----------+
| salary_band | employees | min_sal   | max_sal   |
+-------------+-----------+-----------+-----------+
| High        |         2 | 110000.00 | 150000.00 |
| Medium      |         4 |  70000.00 |  95000.00 |
| Low         |         4 |  48000.00 |  62000.00 |
+-------------+-----------+-----------+-----------+
3 rows in set (0.00 sec)

The CASE creates a new "column" that does not exist in the table, and we group by it. MySQL lets you write the alias salary_band in GROUP BY. That is a MySQL convenience; in standard SQL (and some other databases) you must repeat the whole CASE expression in GROUP BY.

CASE can also be applied to an aggregate, to label groups after counting:

SELECT d.dept_name,
       COUNT(e.emp_id) AS headcount,
       CASE
           WHEN COUNT(e.emp_id) = 0  THEN 'Empty'
           WHEN COUNT(e.emp_id) >= 3 THEN 'Large'
           ELSE 'Small'
       END AS team_size
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;
+-------------+-----------+-----------+
| dept_name   | headcount | team_size |
+-------------+-----------+-----------+
| Engineering |         3 | Large     |
| Sales       |         3 | Large     |
| HR          |         2 | Small     |
| Marketing   |         1 | Small     |
| Research    |         0 | Empty     |
+-------------+-----------+-----------+
5 rows in set (0.00 sec)

Conditional Aggregation

This is the most powerful use of CASE, and a favourite in interviews. The idea: put a CASE inside an aggregate function, so each aggregate only "counts" the rows you choose. One scan of the table produces many different totals.

Order statistics in a single row:

SELECT COUNT(*) AS total_orders,
       SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'Pending'   THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN status <> 'Cancelled' THEN amount ELSE 0 END) AS revenue
FROM orders;
+--------------+-----------+---------+-----------+----------+
| total_orders | delivered | pending | cancelled | revenue  |
+--------------+-----------+---------+-----------+----------+
|            8 |         4 |       2 |         1 | 25050.00 |
+--------------+-----------+---------+-----------+----------+
1 row in set (0.00 sec)

How does SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) count? For every row, the CASE produces 1 if delivered and 0 otherwise, and SUM adds them up:

order   status      CASE value
1001    Delivered   1
1002    Delivered   1
1003    Cancelled   0
1004    Delivered   1
1005    Shipped     0
1006    Delivered   1
1007    Pending     0
1008    Pending     0
                    --- SUM = 4

The revenue column uses the same trick with amount instead of 1: every non-cancelled amount is added, and the cancelled order contributes 0 (26,250 − 1,200 = 25,050). Without CASE you would need a separate query, with its own WHERE, for each number.

COUNT with CASE: leave out the ELSE

COUNT(x) counts the rows where x is not NULL. So with COUNT, the trick is a CASE that returns NULL for rows you do not want, which is exactly what a missing ELSE does:

SELECT COUNT(CASE WHEN status = 'Delivered' THEN 1 END)        AS delivered_count,
       COUNT(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS wrong_count
FROM orders;
+-----------------+-------------+
| delivered_count | wrong_count |
+-----------------+-------------+
|               4 |           8 |
+-----------------+-------------+
1 row in set (0.00 sec)

wrong_count is 8 because 0 is not NULL; COUNT counts it. Remember the pairs: SUM with ELSE 0, or COUNT with no ELSE.

Pivoting rows into columns

Combine conditional aggregation with GROUP BY and you can turn rows into columns, a pivot. Each customer's spend per month:

SELECT customer_id,
       SUM(CASE WHEN order_date BETWEEN '2024-01-01' AND '2024-01-31'
                THEN amount ELSE 0 END) AS jan,
       SUM(CASE WHEN order_date BETWEEN '2024-02-01' AND '2024-02-29'
                THEN amount ELSE 0 END) AS feb,
       SUM(CASE WHEN order_date BETWEEN '2024-03-01' AND '2024-03-31'
                THEN amount ELSE 0 END) AS mar,
       SUM(amount) AS total
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY customer_id;
+-------------+---------+---------+---------+---------+
| customer_id | jan     | feb     | mar     | total   |
+-------------+---------+---------+---------+---------+
|         101 | 2500.00 | 1200.00 | 5600.00 | 9300.00 |
|         102 | 4800.00 |    0.00 | 3100.00 | 7900.00 |
|         103 |    0.00 | 7300.00 |    0.00 | 7300.00 |
|         104 |    0.00 |    0.00 |  950.00 |  950.00 |
+-------------+---------+---------+---------+---------+
4 rows in set (0.00 sec)

In the orders table each customer-month is spread over separate rows; here each month is a column. MySQL has no PIVOT keyword (SQL Server and Oracle do), so this CASE-based pattern is the way to pivot in MySQL. (2024 was a leap year, hence 29 February. Date & Time Functions shows cleaner ways to pick a month.)

MySQL shortcut: booleans are numbers

In MySQL a comparison returns 1 (TRUE), 0 (FALSE) or NULL, so SUM(condition) counts matching rows directly:

SELECT SUM(status = 'Delivered') AS delivered,
       SUM(amount > 3000)        AS big_orders
FROM orders;
+-----------+------------+
| delivered | big_orders |
+-----------+------------+
|         4 |          4 |
+-----------+------------+
1 row in set (0.00 sec)

It is compact, but MySQL-specific (PostgreSQL, for example, will not SUM a boolean). In interviews, write the portable CASE version and mention this shortcut as a bonus.


CASE in WHERE

CASE can produce a value that a WHERE condition uses. For example, a different salary threshold per department:

SELECT name, salary, dept_id
FROM employees
WHERE salary > CASE dept_id
                   WHEN 10 THEN 100000
                   WHEN 20 THEN 60000
                   ELSE 65000
               END;
+--------------+-----------+---------+
| name         | salary    | dept_id |
+--------------+-----------+---------+
| Aarav Sharma | 150000.00 |      10 |
| Sneha Kapoor | 110000.00 |      20 |
| Vikram Singh |  62000.00 |      20 |
| Karan Patel  |  70000.00 |      30 |
| Arjun Reddy  |  82000.00 |      40 |
+--------------+-----------+---------+
5 rows in set (0.00 sec)

Ishita (dept NULL) falls into ELSE (65,000) because NULL = 10 is not TRUE; her 55,000 is not enough. This works, but wrapping columns in CASE inside WHERE prevents index use, and the same logic can usually be written with plain AND/OR: (dept_id = 10 AND salary > 100000) OR (dept_id = 20 AND salary > 60000) OR .... Use CASE in WHERE sparingly.


CASE in UPDATE

CASE lets one UPDATE apply different changes to different rows. These demos were run on a copy of the sample database. A department-wise raise: Engineering 10%, Sales 8%, everyone else 5%:

UPDATE employees
SET salary = CASE dept_id
                 WHEN 10 THEN salary * 1.10
                 WHEN 20 THEN salary * 1.08
                 ELSE salary * 1.05
             END;
Query OK, 10 rows affected (0.01 sec)
Rows matched: 10  Changed: 10  Warnings: 0
SELECT emp_id, name, dept_id, salary FROM employees;
+--------+--------------+---------+-----------+
| emp_id | name         | dept_id | salary    |
+--------+--------------+---------+-----------+
|      1 | Aarav Sharma |      10 | 165000.00 |
|      2 | Priya Iyer   |      10 | 104500.00 |
|      3 | Rohan Mehta  |      10 | 104500.00 |
|      4 | Sneha Kapoor |      20 | 118800.00 |
|      5 | Vikram Singh |      20 |  66960.00 |
|      6 | Ananya Das   |      20 |  62640.00 |
|      7 | Karan Patel  |      30 |  73500.00 |
|      8 | Meera Nair   |      30 |  50400.00 |
|      9 | Arjun Reddy  |      40 |  86100.00 |
|     10 | Ishita Gupta |    NULL |  57750.00 |
+--------+--------------+---------+-----------+
10 rows in set (0.00 sec)

Doing this with three separate UPDATE statements works too, but a single statement is atomic (all rows change together) and scans the table once.

The danger: CASE without ELSE in UPDATE

Suppose you want to change only Meera's city and write:

UPDATE employees
SET city = CASE WHEN emp_id = 8 THEN 'Trivandrum' END;   -- no ELSE, no WHERE!
Query OK, 9 rows affected (0.00 sec)
Rows matched: 10  Changed: 9  Warnings: 0

"9 rows affected" for a change meant for one person is a red flag:

SELECT emp_id, name, city FROM employees WHERE emp_id <= 3 OR emp_id = 8;
+--------+--------------+------------+
| emp_id | name         | city       |
+--------+--------------+------------+
|      1 | Aarav Sharma | NULL       |
|      2 | Priya Iyer   | NULL       |
|      3 | Rohan Mehta  | NULL       |
|      8 | Meera Nair   | Trivandrum |
+--------+--------------+------------+
4 rows in set (0.00 sec)

The UPDATE has no WHERE, so it touches every row, and for every row except Meera's the CASE returns NULL. Everyone else's city was wiped out. (Ishita's was already NULL, hence 9 changed, not 10.) On a NOT NULL column like salary, MySQL in its default strict mode refuses instead: ERROR 1048 (23000): Column 'salary' cannot be null.

Two safe patterns:

-- 1. Restrict the rows with WHERE
UPDATE employees
SET salary = CASE WHEN emp_id = 5 THEN 65000
                  WHEN emp_id = 6 THEN 61000
             END
WHERE emp_id IN (5, 6);

-- 2. Or keep the old value in ELSE
UPDATE employees
SET city = CASE WHEN emp_id = 8 THEN 'Trivandrum' ELSE city END;

The first statement reported Rows matched: 2 Changed: 2 and set Vikram to 65,000 and Ananya to 61,000, leaving everyone else alone.


The Result Type of CASE

A CASE expression produces one column, so all its THEN/ELSE results should have the same type. If you mix types, MySQL quietly converts them to a common type:

SELECT name,
       CASE WHEN emp_id = 1 THEN 100 ELSE 'n/a' END AS mixed
FROM employees
WHERE emp_id <= 2;
+--------------+-------+
| name         | mixed |
+--------------+-------+
| Aarav Sharma | 100   |
| Priya Iyer   | n/a   |
+--------------+-------+
2 rows in set (0.00 sec)

The 100 is left-aligned: the whole column became a string, so sorting it would now be alphabetical ('100' < '9'). Stricter databases such as PostgreSQL reject this. Keep result types consistent, e.g. return NULL instead of 'n/a' for a numeric column.


IF(): MySQL's Two-Way Shortcut

For a simple two-way choice MySQL has the function IF(condition, value_if_true, value_if_false):

SELECT name, IF(salary > 90000, 'Yes', 'No') AS above_90k
FROM employees
WHERE dept_id = 10;
+--------------+-----------+
| name         | above_90k |
+--------------+-----------+
| Aarav Sharma | Yes       |
| Priya Iyer   | Yes       |
| Rohan Mehta  | Yes       |
+--------------+-----------+
3 rows in set (0.00 sec)

IF(c, a, b) is the same as CASE WHEN c THEN a ELSE b END. It is MySQL-specific; CASE is standard SQL and works everywhere. Related functions you already know from the NULL Values chapter are also CASE shorthands:

Function

Equivalent CASE

IF(c, a, b)

CASE WHEN c THEN a ELSE b END

IFNULL(x, d)

CASE WHEN x IS NOT NULL THEN x ELSE d END

COALESCE(a, b, c)

CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END

NULLIF(a, b)

CASE WHEN a = b THEN NULL ELSE a END

One naming note: inside stored procedures MySQL also has a CASE statement (control flow, ending with END CASE), which runs SQL statements rather than returning a value. It is covered in the Stored Procedures & Functions chapter. This chapter is about the CASE expression, which ends with plain END.


Common Mistakes

Mistake

What happens

Fix

Forgetting END

Syntax error

Every CASE ends with END

Overlapping WHENs in the wrong order

Broad condition catches rows meant for a later branch

Most specific condition first

No ELSE

Unmatched rows get NULL

Write an explicit ELSE

CASE col WHEN NULL

Never matches

CASE WHEN col IS NULL

COUNT(CASE ... ELSE 0 END)

Counts every row

COUNT with no ELSE, or SUM with ELSE 0

UPDATE with CASE but no WHERE / ELSE

Other rows set to NULL (or error 1048)

Add WHERE, or ELSE col

Mixing numbers and text in THEN/ELSE

Whole column becomes text

Keep one type; use NULL for "no value"


Interview Questions

What is the CASE expression in SQL?

SQL's if-then-else. It evaluates WHEN conditions in order and returns the THEN value of the first TRUE condition, otherwise the ELSE value, or NULL if there is no ELSE. It is an expression, so it can be used anywhere a value is allowed.

Difference between simple and searched CASE?

Simple CASE compares one expression with a list of values using equality (CASE status WHEN 'Pending' THEN ...). Searched CASE evaluates an independent condition in each WHEN (CASE WHEN salary > 100000 THEN ...) and can handle ranges, NULL checks and multiple columns.

What does CASE return if no condition matches and there is no ELSE?

NULL.

Count delivered and cancelled orders in one query.

SELECT SUM(CASE WHEN status='Delivered' THEN 1 ELSE 0 END) AS delivered, SUM(CASE WHEN status='Cancelled' THEN 1 ELSE 0 END) AS cancelled FROM orders; This is conditional aggregation; it scans the table once.

How do you pivot rows into columns in MySQL?

MySQL has no PIVOT operator. Use GROUP BY on the row key plus one SUM(CASE WHEN category = X THEN value ELSE 0 END) per output column.

How do you sort rows in a custom (non-alphabetical) order?

ORDER BY CASE col WHEN 'first' THEN 1 WHEN 'second' THEN 2 ELSE 3 END. In MySQL, ORDER BY FIELD(col, ...) also works.

Why does CASE col WHEN NULL never match?

Simple CASE tests col = NULL, which is UNKNOWN, never TRUE. Use the searched form CASE WHEN col IS NULL THEN ....

Swap the gender values 'M' and 'F' in a table with one UPDATE.

UPDATE t SET gender = CASE gender WHEN 'M' THEN 'F' WHEN 'F' THEN 'M' ELSE gender END; The ELSE keeps any other value unchanged.

CASE vs IF() in MySQL?

IF(c, a, b) is a MySQL-specific shorthand for a two-way CASE. CASE is standard SQL, supports many branches, and is portable.


Summary

  • CASE is SQL's if-else expression; it returns the result of the first TRUE WHEN, else the ELSE value, else NULL.

  • Searched CASE (CASE WHEN cond) handles any condition; simple CASE (CASE expr WHEN value) handles equality only and cannot match NULL.

  • WHEN order matters when conditions overlap.

  • Use CASE in SELECT (labels, bands), ORDER BY (custom order), GROUP BY (buckets), WHERE (sparingly) and UPDATE (different changes per row).

  • Conditional aggregation, SUM(CASE ... THEN 1 ELSE 0 END) or COUNT(CASE ... THEN 1 END), counts several categories in one pass and is how you pivot in MySQL.

  • In UPDATE, a CASE without ELSE sets unmatched rows to NULL; always add WHERE or ELSE col.

  • IF(), IFNULL(), COALESCE() and NULLIF() are shorthands for common CASE patterns.

CASE often works together with functions that reshape values. In the next chapter, String Functions, we will learn how to join, cut, search, pad and clean up text in SQL.