MySQL – IF, CASE and Conditional Expressions

SQL has no if statement in a query — but it has conditional expressions, which is what you actually need. They compute a value per row, so they can appear anywhere a column can: in the select list, in WHERE, in ORDER BY, and — most usefully — inside an aggregate.

IF()

IF(condition, when_true, when_false), and that is the whole function:

SELECT customer_name, total, IF(total > 30, 'large', 'standard') AS bucket
FROM   customer_order ORDER BY id LIMIT 5;

+---------------+-------+----------+
| customer_name | total | bucket   |
+---------------+-------+----------+
| Demo Customer | 28.91 | standard |
| Alex Rivera   | 18.43 | standard |
| Demo Customer | 32.18 | large    |
| Sam Chen      | 32.18 | large    |
| Jordan Blake  | 11.92 | standard |
+---------------+-------+----------+

IF() is MySQL-specific. CASE is standard SQL and does everything IF() does, so if your query might ever move to another database, prefer CASE.

Do not confuse this with the IF statement, which is a different thing that only exists inside stored procedures.

CASE

Two forms. The simple form compares one expression against values:

SELECT status,
       CASE status
           WHEN 'COMPLETED'       THEN 'done'
           WHEN 'CANCELLED'       THEN 'dead'
           WHEN 'PENDING_PAYMENT' THEN 'waiting on the customer'
           ELSE                        'in progress'
       END AS plain_english,
       COUNT(*) AS orders
FROM   customer_order GROUP BY status ORDER BY orders DESC;

+-----------------+-------------------------+--------+
| status          | plain_english           | orders |
+-----------------+-------------------------+--------+
| COMPLETED       | done                    |     13 |
| CANCELLED       | dead                    |      2 |
| PAID            | in progress             |      1 |
| PENDING_PAYMENT | waiting on the customer |      1 |
| PREPARING       | in progress             |      1 |
+-----------------+-------------------------+--------+

The searched form takes a full condition per branch, so it can compare ranges and combine columns:

SELECT CASE
           WHEN total >= 40 THEN 'large'
           WHEN total >= 25 THEN 'medium'
           ELSE                  'small'
       END AS size_band,
       COUNT(*) AS orders
FROM   customer_order
GROUP  BY size_band
ORDER  BY orders DESC;

Branches are evaluated in order and the first match wins, which is why the bands above do not need an upper bound each — by the time >= 25 is tested, anything 40 or over has already been taken. Reordering those two lines silently changes the answer.

Omit ELSE and unmatched rows get NULL rather than an error. That is the most common CASE bug: a value nobody thought of turns into a NULL that then vanishes from a SUM. Write the ELSE.

NULL-handling shorthands

SELECT id, COALESCE(guest_email, 'account holder') AS contact,
       IFNULL(address_line1, 'carryout') AS delivery_to
FROM   customer_order ORDER BY id LIMIT 4;

+----+--------------------+-------------+
| id | contact            | delivery_to |
+----+--------------------+-------------+
|  1 | account holder     | 123 Main St |
|  2 | guest1@example.com | carryout    |
|  3 | account holder     | 123 Main St |
|  4 | guest2@example.com | 88 Oak Ave  |
+----+--------------------+-------------+

Function	Returns
`COALESCE(a, b, c, …)`	the first non-NULL argument. Standard SQL, any number of arguments.
`IFNULL(a, b)`	`b` if `a` is NULL. MySQL-specific, exactly two arguments.
`NULLIF(a, b)`	NULL when the two are equal — used to turn a zero denominator into a NULL instead of an error.

COALESCE covers both jobs, so there is little reason to reach for IFNULL. More on all of this in NULL and IS NULL.

Conditional aggregation — the reason this lesson matters

This is the technique worth taking away. Putting a condition inside an aggregate lets one pass over the table answer several questions at once:

SELECT COUNT(*) AS orders,
       SUM(status = 'COMPLETED')                    AS completed,
       SUM(status = 'CANCELLED')                    AS cancelled,
       SUM(CASE WHEN order_type = 'DELIVERY' THEN 1 ELSE 0 END) AS delivery,
       SUM(CASE WHEN status = 'COMPLETED' THEN total ELSE 0 END) AS completed_revenue
FROM   customer_order;

+--------+-----------+-----------+----------+-------------------+
| orders | completed | cancelled | delivery | completed_revenue |
+--------+-----------+-----------+----------+-------------------+
|     18 |        13 |         2 |       11 |            357.40 |
+--------+-----------+-----------+----------+-------------------+

Five numbers, one scan. The alternatives are five separate queries, or a UNION of five — both of which read the table five times and give you a column of rows where a report wants a row of columns.

Two spellings, and the difference is worth knowing. SUM(status = 'COMPLETED') works because a MySQL comparison evaluates to 1 or 0, so summing it counts the matches. It is compact and MySQL-specific. SUM(CASE WHEN … THEN 1 ELSE 0 END) is the portable form and reads more clearly to someone who has not seen the trick.

Use SUM, not COUNT. COUNT(CASE WHEN … THEN 1 END) also works — because COUNT ignores NULLs and the missing ELSE yields NULL — but COUNT(CASE WHEN … THEN 1 ELSE 0 END) counts every row, since 0 is not NULL. That one silently returns the table's row count and looks entirely plausible.

Add a GROUP BY and the same shape becomes a pivot table — one row per group, one column per category. That is the standard answer to "turn these rows into columns", and it is in advanced queries.

Elsewhere in a query

-- sort by a computed priority, not by a stored one
SELECT id, status FROM customer_order
ORDER  BY CASE status
              WHEN 'PREPARING'       THEN 1
              WHEN 'PAID'            THEN 2
              WHEN 'PENDING_PAYMENT' THEN 3
              ELSE                        4
          END, id
LIMIT  5;

CASE works in ORDER BY, in WHERE, in GROUP BY and in an UPDATE's SET. Note that sorting by an expression cannot use an index — fine on 18 rows, and something to check on a large table. See ORDER BY.

What to remember

IF() for two outcomes, CASE for more — and CASE is the portable one.
Branches are tested in order; the first match wins.
Always write ELSE, or unmatched rows become NULL.
SUM(CASE …) answers several questions in one scan. Use SUM, not COUNT with an ELSE 0.