SQL Functions · Lesson 9 of 30

SQL CASE WHEN and NULL Functions

5 min read Updated Nov 7, 2026 Saurabh Samir

On this page

CASE WHEN: labelling rows
Simple CASE
CASE inside aggregates: conditional counts
Handling NULL
Practice
Summary

CASE is SQL's if-then-else. It lets a query label rows ("Premium", "Budget"), sort by business rules, and count things conditionally – turning raw data into readable reports without any application code. Alongside it, COALESCE and NULLIF handle missing values cleanly.

In this lesson

Searched and simple CASE expressions
Bucketing values into groups
Conditional counting and pivoting with CASE
COALESCE, IFNULL, NULLIF and IF

CASE WHEN: labelling rows

SELECT name,
       price,
       CASE
         WHEN price >= 1500 THEN 'Premium'
         WHEN price >= 500  THEN 'Mid-range'
         ELSE 'Budget'
       END AS price_band
FROM products
ORDER BY price DESC;

Output

+---------------------+---------+------------+
| name                | price   | price_band |
+---------------------+---------+------------+
| Mechanical Keyboard | 2499.00 | Premium    |
| Laptop Stand        | 1499.00 | Mid-range  |
| USB-C Hub           | 1299.00 | Mid-range  |
| Desk Lamp           |  899.00 | Mid-range  |
| Wireless Mouse      |  599.00 | Mid-range  |
| JavaScript Guide    |  550.00 | Mid-range  |
| Clean Code          |  450.00 | Budget     |
| SQL in 10 Minutes   |  350.00 | Budget     |
| Gel Pen Pack        |  120.00 | Budget     |
| A5 Notebook         |   90.00 | Budget     |
+---------------------+---------+------------+

How CASE decides

Check each WHEN in orderprice >= 1500? price >= 500?
First true WHEN winsits THEN value is returned; later WHENs are skipped
Nothing trueELSE value – or NULL if there's no ELSE

Order matters: if you checked price >= 500 first, every premium product would be labelled "Mid-range".

Simple CASE

When you compare one expression against fixed values, the simple form is shorter:

SELECT order_id,
       status,
       CASE status
         WHEN 'delivered' THEN '✔ Done'
         WHEN 'shipped'   THEN 'On the way'
         WHEN 'pending'   THEN 'Being packed'
         ELSE 'Cancelled'
       END AS customer_message
FROM orders
WHERE customer_id = 1;

Output

+----------+-----------+------------------+
| order_id | status    | customer_message |
+----------+-----------+------------------+
|      101 | delivered | ✔ Done           |
|      103 | delivered | ✔ Done           |
|      109 | shipped   | On the way       |
+----------+-----------+------------------+

CASE inside aggregates: conditional counts

Because COUNT ignores NULLs and CASE without ELSE returns NULL, you can count several conditions in one pass. This is how dashboards are built:

SELECT COUNT(*)                                          AS total_orders,
       COUNT(CASE WHEN status = 'delivered' THEN 1 END)  AS delivered,
       COUNT(CASE WHEN status IN ('pending', 'shipped') THEN 1 END) AS open_orders,
       SUM(status = 'cancelled')                         AS cancelled     -- MySQL: true = 1
FROM orders;

Output

+--------------+-----------+-------------+-----------+
| total_orders | delivered | open_orders | cancelled |
+--------------+-----------+-------------+-----------+
|           12 |         7 |           4 |         1 |
+--------------+-----------+-------------+-----------+

Pivoting rows into columns

SELECT department,
       SUM(CASE WHEN hire_date < '2021-01-01' THEN 1 ELSE 0 END) AS before_2021,
       SUM(CASE WHEN hire_date >= '2021-01-01' THEN 1 ELSE 0 END) AS from_2021,
       COUNT(*) AS total
FROM employees
GROUP BY department
ORDER BY department;

Output

+-------------+-------------+-----------+-------+
| department  | before_2021 | from_2021 | total |
+-------------+-------------+-----------+-------+
| Engineering |           1 |         3 |     4 |
| HR          |           1 |         1 |     2 |
| Management  |           1 |         0 |     1 |
| Sales       |           1 |         2 |     3 |
+-------------+-------------+-----------+-------+

CASE in ORDER BY

SELECT name, department
FROM employees
ORDER BY CASE department WHEN 'Management' THEN 1 WHEN 'Engineering' THEN 2 ELSE 3 END, name
LIMIT 5;

Output

+--------------+-------------+
| name         | department  |
+--------------+-------------+
| Anita Desai  | Management  |
| Dev Patel    | Engineering |
| Priya Sharma | Engineering |
| Rohit Mehta  | Engineering |
| Sana Iqbal   | Engineering |
+--------------+-------------+

Handling NULL

SELECT name,
       city,
       COALESCE(city, 'Not provided')  AS city_label,     -- first non-NULL value
       IFNULL(city, '-')               AS city_short      -- MySQL: two-argument version
FROM customers
WHERE customer_id IN (6, 7);

Output

+-------------+--------+--------------+------------+
| name        | city   | city_label   | city_short |
+-------------+--------+--------------+------------+
| Arjun Singh | Mumbai | Mumbai       | Mumbai     |
| Neha Gupta  | NULL   | Not provided | -          |
+-------------+--------+--------------+------------+

Function	Returns	Portable?
`COALESCE(a, b, c…)`	the first value that isn't NULL	standard SQL – works everywhere
`IFNULL(a, b)`	b if a is NULL	MySQL (`ISNULL` in SQL Server, `NVL` in Oracle)
`NULLIF(a, b)`	NULL if a = b, otherwise a	standard SQL
`IF(cond, x, y)`	x if cond is true, else y	MySQL only – use CASE for portability

NULLIF: avoid division by zero

SELECT name, price, stock,
       ROUND(price / NULLIF(stock, 0), 2) AS price_per_unit_in_stock
FROM products
WHERE product_id IN (2, 3);

Output

+---------------------+---------+-------+-------------------------+
| name                | price   | stock | price_per_unit_in_stock |
+---------------------+---------+-------+-------------------------+
| Mechanical Keyboard | 2499.00 |    35 |                   71.40 |
| USB-C Hub           | 1299.00 |     0 |                    NULL |
+---------------------+---------+-------+-------------------------+

In MySQL, dividing by zero returns NULL with a warning; in PostgreSQL and SQL Server it's an error that stops the query. NULLIF(stock, 0) turns 0 into NULL first, so the result is NULL everywhere instead of a crash.

Practice

Try it yourself

Label each employee's salary as "Senior" (≥ ₹1,40,000), "Mid" (≥ ₹90,000) or "Junior", for Engineering and Sales.

Show solution

SELECT name, salary,
       CASE WHEN salary >= 140000 THEN 'Senior'
            WHEN salary >= 90000  THEN 'Mid'
            ELSE 'Junior' END AS level
FROM employees
WHERE department IN ('Engineering', 'Sales')
ORDER BY salary DESC, name;

Output

+--------------+--------+--------+
| name         | salary | level  |
+--------------+--------+--------+
| Rohit Mehta  | 180000 | Senior |
| Farhan Ali   | 140000 | Senior |
| Dev Patel    | 120000 | Mid    |
| Sana Iqbal   | 120000 | Mid    |
| Priya Sharma |  95000 | Mid    |
| Lata Joshi   |  85000 | Junior |
| Tom Mathew   |  78000 | Junior |
+--------------+--------+--------+

Count products that are in stock and out of stock in one row.

Show solution

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

Output

+----------+--------------+
| in_stock | out_of_stock |
+----------+--------------+
|        9 |            1 |
+----------+--------------+

Quick check

What does CASE return if no WHEN matches and there's no ELSE?

NULL.

COALESCE(NULL, NULL, 'x', 'y') returns?

'x' – the first non-NULL argument.

How do you count only delivered orders inside a bigger query?

COUNT(CASE WHEN status = 'delivered' THEN 1 END) or SUM(CASE … THEN 1 ELSE 0 END).

Summary

CASE WHEN returns the THEN of the first true condition (ELSE or NULL otherwise).
CASE inside COUNT/SUM gives conditional counts and pivots in one pass.
COALESCE supplies defaults for NULL; NULLIF prevents division by zero.

Finished this lesson?

Mark it complete to track your progress, then carry on with the next lesson.

Next lesson