Logic inside a query
So far every query has returned stored values or simple calculations. Often you want the result to depend on the data: label products as cheap or expensive, show a friendly text instead of NULL, or turn a status code into a sentence. SQL has several tools for this.
CASE: the general tool
CASE evaluates conditions one by one and returns the value for the first one that is true:
SELECT name, price,
CASE
WHEN price < 20 THEN 'budget'
WHEN price < 50 THEN 'standard'
ELSE 'premium'
END AS price_band
FROM products
ORDER BY price;+-----------------------------+--------+------------+
| name | price | price_band |
+-----------------------------+--------+------------+
| Water Bottle | 12.00 | budget |
| Resistance Bands | 14.99 | budget |
| Ceramic Mug Set | 18.75 | budget |
| Puzzle 1000 Pieces | 19.99 | budget |
| Wireless Mouse | 24.99 | standard |
| Yoga Mat | 27.50 | standard |
| SQL Basics Handbook | 29.95 | standard |
| Desk Lamp | 32.00 | standard |
| Web Design Patterns | 34.50 | standard |
| USB-C Hub | 39.00 | standard |
| Data Structures Illustrated | 45.00 | standard |
| Building Blocks Set | 49.99 | standard |
| Standing Desk Mat | 55.00 | premium |
| Mechanical Keyboard | 89.50 | premium |
| Noise-Cancelling Headphones | 199.99 | premium |
+-----------------------------+--------+------------+The conditions run from top to bottom, so order matters. A product priced at 15 is budget because the first test is true; the later tests never run for it. If you reversed the first two tests, every product under 50 would be standard.
Always add an ELSE. Without it, rows that match nothing return NULL, which can hide gaps.
The simple form of CASE
When you compare one expression to several exact values, the shorter form is easier to read:
SELECT id, status,
CASE status
WHEN 'pending' THEN 'Waiting to ship'
WHEN 'shipped' THEN 'On its way'
WHEN 'delivered' THEN 'Arrived'
ELSE 'Cancelled'
END AS description
FROM orders
WHERE id IN (6, 9, 4, 1);+----+-----------+-----------------+
| id | status | description |
+----+-----------+-----------------+
| 1 | delivered | Arrived |
| 4 | shipped | On its way |
| 6 | cancelled | Cancelled |
| 9 | pending | Waiting to ship |
+----+-----------+-----------------+CASE with aggregates
CASE inside an aggregate function is a classic technique for counting categories side by side, a "pivot":
SELECT COUNT(*) AS total,
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
FROM orders;+-------+-----------+---------+-----------+
| total | delivered | pending | cancelled |
+-------+-----------+---------+-----------+
| 20 | 11 | 3 | 2 |
+-------+-----------+---------+-----------+You will meet SUM and COUNT properly in the aggregate lesson; the point here is that one pass over the table produced several counts.
IF: two outcomes
MySQL has a shortcut for a simple either-or:
SELECT name, stock, IF(stock = 0, 'out of stock', 'available') AS availability FROM products WHERE id IN (14, 15);+---------------------+-------+--------------+
| name | stock | availability |
+---------------------+-------+--------------+
| Building Blocks Set | 35 | available |
| Puzzle 1000 Pieces | 0 | out of stock |
+---------------------+-------+--------------+IF(condition, value_if_true, value_if_false) is MySQL-specific. The CASE version works in every database, so prefer it in code you may move.
IFNULL and COALESCE: defaults for NULL
IFNULL(x, fallback) returns x unless it is NULL. COALESCE accepts any number of arguments and returns the first one that is not NULL:
SELECT first_name, IFNULL(phone, 'unknown') AS phone, COALESCE(phone, email, 'no contact') AS best_contact FROM customers WHERE id IN (1, 2);+------------+-----------------+------------------------+
| first_name | phone | best_contact |
+------------+-----------------+------------------------+
| Asha | +91-98100-00001 | +91-98100-00001 |
| Ben | unknown | ben.carter@example.com |
+------------+-----------------+------------------------+COALESCE is part of the SQL standard, so it is the more portable choice.
NULLIF: avoid dividing by zero
NULLIF(a, b) returns NULL when a equals b, otherwise a. That is useful for turning a harmless value into NULL on purpose, especially in a divisor:
SELECT name, stock, 1000 / NULLIF(stock, 0) AS days_of_cover FROM products WHERE id IN (15, 13);+--------------------+-------+---------------+
| name | stock | days_of_cover |
+--------------------+-------+---------------+
| Water Bottle | 250 | 4.0000 |
| Puzzle 1000 Pieces | 0 | NULL |
+--------------------+-------+---------------+For the puzzle with zero stock, the division gives NULL instead of an error. You can then wrap the whole thing in COALESCE to show a message.
Using CASE in ORDER BY and UPDATE
CASE works anywhere an expression does. To list orders with pending ones first:
SELECT id, status FROM orders ORDER BY CASE status WHEN 'pending' THEN 0 WHEN 'shipped' THEN 1 ELSE 2 END, id LIMIT 6;+----+---------+
| id | status |
+----+---------+
| 9 | pending |
| 14 | pending |
| 19 | pending |
| 4 | shipped |
| 8 | shipped |
| 12 | shipped |
+----+---------+And it can drive an update that treats groups differently:
UPDATE products SET price = CASE WHEN price >= 100 THEN price * 0.90 WHEN price >= 40 THEN price * 0.95 ELSE price END;
SELECT name, price FROM products WHERE id IN (4, 2, 1);+-----------------------------+--------+
| name | price |
+-----------------------------+--------+
| Wireless Mouse | 24.99 |
| Mechanical Keyboard | 85.03 |
| Noise-Cancelling Headphones | 179.99 |
+-----------------------------+--------+Common mistakes
- No ELSE, so unmatched rows become NULL.
- Conditions in the wrong order, so a broad test catches rows meant for a narrow one.
- Comparing with NULL inside CASE using
=. WriteWHEN phone IS NULL. - Using
IFand expecting it to run in other databases. - Forgetting END.
Exercise
Write a query that lists every employee's name and salary with a column level: 'junior' below 60000, 'mid' from 60000 up to but not including 100000, and 'senior' otherwise. Also show the department as the text 'unassigned' when the department id is NULL.
Show one possible answer
SELECT name, salary,
CASE WHEN salary < 60000 THEN 'junior'
WHEN salary < 100000 THEN 'mid'
ELSE 'senior' END AS level,
COALESCE(department_id, 'unassigned') AS department
FROM employees;+-------------+-----------+--------+------------+
| name | salary | level | department |
+-------------+-----------+--------+------------+
| Meera Iyer | 150000.00 | senior | 1 |
| Tom Hughes | 110000.00 | senior | 2 |
| Nina Petrov | 125000.00 | senior | 1 |
| Omar Haddad | 98000.00 | mid | 1 |
| Paula Gomez | 82000.00 | mid | 1 |
| Quinn Baker | 64000.00 | mid | 2 |
| Rina Das | 66000.00 | mid | 2 |
| Sam Wright | 79000.00 | mid | 1 |
| Tara Singh | 72000.00 | mid | 3 |
| Uma Reddy | 48000.00 | junior | 1 |
| Victor Lang | 90000.00 | mid | unassigned |
+-------------+-----------+--------+------------+Key points
- CASE WHEN ... THEN ... ELSE ... END is SQL's if/else for a column. Conditions are checked in order and the first true one wins.
- IF(condition, a, b) is a short MySQL form for two outcomes. IFNULL(x, default) and COALESCE(a, b, c) replace NULL.
- NULLIF(a, b) returns NULL when the two are equal, a neat way to avoid division by zero.
- Always include ELSE in a CASE. Without it, unmatched rows silently become NULL.