CASE WHEN, IF, COALESCE, IFNULL and NULLIF in SQL (MySQL)
You need a label for the storefront in the form “product: price”, and SELECT CONCAT(product, ': ', price) FROM sales; looks harmless. But for the grapes, whose price has not been entered yet, MySQL returns not grapes: but NULL — the whole label disappears because of one empty column. To keep surprises like this from reaching production, you need to know how conditional expressions and functions treat NULL.
CASE WHEN is the SQL conditional expression that returns a value depending on conditions, and COALESCE is the function that returns its first non-NULL argument. Together with the MySQL functions IF, IFNULL and NULLIF they are single-row functions: evaluated for each row separately and usable in SELECT, WHERE, ORDER BY and UPDATE ... SET. This lesson covers conditional logic and NULL handling in MySQL 8; string, numeric and date functions are in the lesson SQL Functions in MySQL: Strings, Numbers, Dates, and aggregate functions (COUNT, SUM) in the lesson on GROUP BY and HAVING.
The sales Practice Table
Every example in this lesson runs in MySQL 8 on the sales table from the lesson on the SELECT statement, with the sale date column sold_at — the same data as in the lesson on string, numeric and date functions. It holds eight fruit sales in three grocery stores (quantities in pounds, prices per pound in US dollars); two items have no price yet (NULL), and those two rows are what most examples below are about.
The script recreates the table, so if you changed sales in other lessons, the data goes back to the original:
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
id INT NOT NULL AUTO_INCREMENT,
shop VARCHAR(50) NOT NULL,
product VARCHAR(50) NOT NULL,
lbs INT NOT NULL,
price DECIMAL(6,2) NULL,
sold_at DATETIME NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO sales (shop, product, lbs, price, sold_at) VALUES
('Acme', 'apples', 10, 1.50, '2024-12-30 18:45:00'),
('Acme', 'pears', 14, 2.50, '2025-01-15 09:30:00'),
('Acme', 'grapes', 20, NULL, '2025-01-31 20:10:00'),
('Delta', 'apples', 20, 1.75, '2025-02-03 11:00:00'),
('Delta', 'grapes', 34, 4.50, '2025-02-28 16:20:00'),
('Delta', 'lemons', 34, NULL, '2025-03-08 10:05:00'),
('Berry Hill', 'lemons', 14, 3.50, '2025-12-31 23:59:00'),
('Berry Hill', 'apples', 54, 1.25, '2026-01-02 08:15:00'); SELECT * FROM sales; +----+------------+---------+-----+-------+---------------------+
| id | shop | product | lbs | price | sold_at |
+----+------------+---------+-----+-------+---------------------+
| 1 | Acme | apples | 10 | 1.50 | 2024-12-30 18:45:00 |
| 2 | Acme | pears | 14 | 2.50 | 2025-01-15 09:30:00 |
| 3 | Acme | grapes | 20 | NULL | 2025-01-31 20:10:00 |
| 4 | Delta | apples | 20 | 1.75 | 2025-02-03 11:00:00 |
| 5 | Delta | grapes | 34 | 4.50 | 2025-02-28 16:20:00 |
| 6 | Delta | lemons | 34 | NULL | 2025-03-08 10:05:00 |
| 7 | Berry Hill | lemons | 14 | 3.50 | 2025-12-31 23:59:00 |
| 8 | Berry Hill | apples | 54 | 1.25 | 2026-01-02 08:15:00 |
+----+------------+---------+-----+-------+---------------------+
8 rows in set You can also try an expression without any table: SELECT COALESCE(NULL, 'Java'); returns Java.
CASE WHEN in SQL
CASE is the standard SQL conditional expression, the equivalent of an if / else if / else chain: it checks the WHEN conditions in order and returns the THEN value of the first branch that is true. If no branch matches, it returns the ELSE value, and without ELSE — NULL. CASE works anywhere an expression is allowed: in SELECT, WHERE, ORDER BY, UPDATE ... SET.
The searched form CASE WHEN condition THEN ... accepts any condition. Classifying sales by size:
SELECT id, product, lbs,
CASE
WHEN lbs >= 30 THEN 'bulk'
WHEN lbs >= 15 THEN 'medium'
ELSE 'small'
END AS size
FROM sales; +----+---------+-----+--------+
| id | product | lbs | size |
+----+---------+-----+--------+
| 1 | apples | 10 | small |
| 2 | pears | 14 | small |
| 3 | grapes | 20 | medium |
| 4 | apples | 20 | medium |
| 5 | grapes | 34 | bulk |
| 6 | lemons | 34 | bulk |
| 7 | lemons | 14 | small |
| 8 | apples | 54 | bulk |
+----+---------+-----+--------+ 54 pounds satisfies both conditions, but the first branch wins, which is why branches go from the narrowest to the widest. Swap the two WHENs and every sale of 15 pounds or more becomes “medium”.
The simple form CASE expression WHEN value THEN ... compares one expression against a list of values with =. Without ELSE, values that are not listed turn into NULL:
SELECT DISTINCT product,
CASE product
WHEN 'apples' THEN 'pome'
WHEN 'pears' THEN 'pome'
WHEN 'lemons' THEN 'citrus'
END AS fruit_group
FROM sales
ORDER BY product; +---------+-------------+
| product | fruit_group |
+---------+-------------+
| apples | pome |
| grapes | NULL |
| lemons | citrus |
| pears | pome |
+---------+-------------+ Why CASE price WHEN NULL Never Matches
The simple form compares with =, and price = NULL is never true (the reason is in the section NULL Values of the SELECT lesson). So a WHEN NULL branch fires for no row at all:
SELECT id, price,
CASE price WHEN NULL THEN 'no price' ELSE 'has price' END AS simple_case,
CASE WHEN price IS NULL THEN 'no price' ELSE 'has price' END AS searched_case
FROM sales
WHERE shop = 'Acme'; +----+-------+-------------+---------------+
| id | price | simple_case | searched_case |
+----+-------+-------------+---------------+
| 1 | 1.50 | has price | has price |
| 2 | 2.50 | has price | has price |
| 3 | NULL | has price | no price |
+----+-------+-------------+---------------+ For NULL, only the searched form with IS NULL works.
CASE in ORDER BY and WHERE
In ORDER BY, a CASE expression defines an order that is neither alphabetical nor numeric. Delta first, then Acme, every other store last:
SELECT id, shop, product
FROM sales
ORDER BY CASE shop WHEN 'Delta' THEN 1 WHEN 'Acme' THEN 2 ELSE 3 END, id; +----+------------+---------+
| id | shop | product |
+----+------------+---------+
| 4 | Delta | apples |
| 5 | Delta | grapes |
| 6 | Delta | lemons |
| 1 | Acme | apples |
| 2 | Acme | pears |
| 3 | Acme | grapes |
| 7 | Berry Hill | lemons |
| 8 | Berry Hill | apples |
+----+------------+---------+ In WHERE, CASE lets you set a different threshold per store: for Berry Hill only sales of 50 pounds or more matter, for the others — 20 pounds or more.
SELECT id, shop, lbs
FROM sales
WHERE lbs >= CASE shop WHEN 'Berry Hill' THEN 50 ELSE 20 END; +----+------------+-----+
| id | shop | lbs |
+----+------------+-----+
| 3 | Acme | 20 |
| 4 | Delta | 20 |
| 5 | Delta | 34 |
| 6 | Delta | 34 |
| 8 | Berry Hill | 54 |
+----+------------+-----+ The same condition can be written without CASE: (shop = 'Berry Hill' AND lbs >= 50) OR (shop <> 'Berry Hill' AND lbs >= 20). The optimizer finds this form easier to analyze, which is why CASE is rarely used in WHERE. Counting rows by a condition — COUNT(CASE WHEN ... THEN 1 END) — is covered in the lesson on aggregate functions.
The IF Function in MySQL
IF(condition, value_if_true, value_otherwise) is a shorthand for a one-branch CASE that exists only in MySQL (and MariaDB). PostgreSQL, Oracle and standard SQL have no such function, so portable code uses CASE WHEN condition THEN ... ELSE ... END. A condition that evaluates to NULL is treated as false.
SELECT id, lbs, price,
IF(lbs >= 30, 'wholesale', 'retail') AS sale_type,
IF(price > 3, 'premium', 'regular') AS price_level
FROM sales
WHERE shop = 'Delta'; +----+-----+-------+-----------+-------------+
| id | lbs | price | sale_type | price_level |
+----+-----+-------+-----------+-------------+
| 4 | 20 | 1.75 | retail | regular |
| 5 | 34 | 4.50 | wholesale | premium |
| 6 | 34 | NULL | wholesale | regular |
+----+-----+-------+-----------+-------------+ Row 6 — lemons without a price — got “regular”: NULL > 3 yields NULL, and IF took the “otherwise” branch. The report looks plausible, although nothing is known about the price of the lemons. If an unknown value needs its own label, test for it first: CASE WHEN price IS NULL THEN 'no price' WHEN price > 3 THEN 'premium' ELSE 'regular' END.
COALESCE, IFNULL and NULLIF: Handling NULL
COALESCE(a, b, ...) returns the first argument that is not NULL; IFNULL(a, b) does the same for two arguments and exists only in MySQL; NULLIF(a, b) works the other way round — it returns NULL if the arguments are equal and a if they are not. All three keep NULL from spoiling a calculation or the output.
SELECT id, product, price,
COALESCE(price, 0) AS price_or_zero,
IFNULL(price, 'no price') AS price_text,
lbs * COALESCE(price, 0) AS revenue
FROM sales
WHERE shop = 'Acme'; +----+---------+-------+---------------+------------+---------+
| id | product | price | price_or_zero | price_text | revenue |
+----+---------+-------+---------------+------------+---------+
| 1 | apples | 1.50 | 1.50 | 1.50 | 15.00 |
| 2 | pears | 2.50 | 2.50 | 2.50 | 35.00 |
| 3 | grapes | NULL | 0.00 | no price | 0.00 |
+----+---------+-------+---------------+------------+---------+ The price_text column is left-aligned for a reason: since one of the arguments is a string, the whole IFNULL result became a string, and 1.50 in it is now text. With several arguments, COALESCE walks through them left to right: COALESCE(NULL, NULL, 'third', 'fourth') returns 'third'. A typical use is a customer contact: COALESCE(mobile_phone, work_phone, email).
NULLIF is most often a guard against division by zero:
SELECT 100 / 0 AS plain,
100 / NULLIF(0, 0) AS with_nullif,
NULLIF('', '') AS empty_to_null,
NULLIF('abc', '') AS not_empty; +-------+-------------+---------------+-----------+
| plain | with_nullif | empty_to_null | not_empty |
+-------+-------------+---------------+-----------+
| NULL | NULL | NULL | abc |
+-------+-------------+---------------+-----------+
1 row in set, 1 warning In a SELECT, MySQL returns NULL with warning 1365 Division by 0 even without NULLIF. The difference appears in INSERT and UPDATE: in MySQL 8 the STRICT_TRANS_TABLES and ERROR_FOR_DIVISION_BY_ZERO modes are on by default, and division by zero while writing data fails with ERROR 1365 (22012): Division by 0. In PostgreSQL even a SELECT raises an error. A denominator written as x / NULLIF(y, 0) behaves the same everywhere. Another common trick is COALESCE(NULLIF(TRIM(phone), ''), 'not provided'): an empty string and NULL are handled the same way.
COALESCE vs IFNULL: What Is the Difference
In MySQL, COALESCE(a, b) and IFNULL(a, b) with two arguments return the same value at the same speed. They differ in portability and in the number of arguments:
| Property | COALESCE | IFNULL |
|---|---|---|
| Standard | Standard SQL: works in MySQL, PostgreSQL, Oracle, SQL Server, H2 | MySQL/MariaDB only (and SQLite); the Oracle equivalent is NVL, the SQL Server one is ISNULL |
| Number of arguments | Any, from one | Exactly two |
| Result type in MySQL | Common type of all arguments | Common type of the two arguments (string > decimal > integer) |
| Argument evaluation | Left to right up to the first non-NULL | The second argument only if the first is NULL |
The result-type difference comes up a lot in interviews, but it belongs to SQL Server: there ISNULL(a, b) takes the type of the first argument and may truncate b, while COALESCE picks the type with the highest precedence. In MySQL both functions derive a common type, so IFNULL(price, 'no price') and COALESCE(price, 'no price') both turn the number into a string. For code that may move to another database, and for tests running on H2, COALESCE is the safer choice.
CONCAT and NULL: CONCAT_WS
CONCAT returns NULL if any argument is NULL. CONCAT_WS(separator, ...) (with separator) skips NULL arguments and puts the separator only between the remaining values. Here is the query from the top of the lesson:
SELECT id,
CONCAT(product, ': ', price) AS label,
CONCAT_WS(', ', shop, product, price) AS line
FROM sales
WHERE shop = 'Acme'; +----+--------------+--------------------+
| id | label | line |
+----+--------------+--------------------+
| 1 | apples: 1.50 | Acme, apples, 1.50 |
| 2 | pears: 2.50 | Acme, pears, 2.50 |
| 3 | NULL | Acme, grapes |
+----+--------------+--------------------+ If the label must keep the “product: price” shape, replace NULL inside CONCAT: CONCAT(product, ': ', COALESCE(price, 'price not set')) gives grapes: price not set. In PostgreSQL and Oracle, concatenation with || behaves differently (Oracle treats NULL as an empty string), and in MySQL || means logical OR by default. The other string functions — SUBSTRING, TRIM, LPAD — are in the section MySQL String Functions.
NULL in Java: COALESCE in the Query or null in JDBC
A column that may be NULL can be handled in two places: replace the value with COALESCE right in the query, or read it as is and let the Java code decide. Object getters (getBigDecimal, getString, getObject) return null for SQL NULL; primitive getters (getInt, getDouble) return 0, and only rs.wasNull() tells you the value was missing.
String sql = "SELECT product, price, COALESCE(price, 0) AS price_or_zero "
+ "FROM sales WHERE shop = ? ORDER BY id";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, "Acme");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
BigDecimal price = rs.getBigDecimal("price"); // null for NULL
BigDecimal orZero = rs.getBigDecimal("price_or_zero"); // 0.00 for NULL
double primitive = rs.getDouble("price"); // 0.0 for NULL
boolean wasNull = rs.wasNull(); // refers to the last getter call
System.out.println(rs.getString("product") + " " + price + " " + orZero
+ " " + primitive + " " + wasNull);
}
}
}
// Output:
// apples 1.50 1.50 1.5 false
// pears 2.50 2.50 2.5 false
// grapes null 0.00 0.0 true For the grapes, getDouble returned 0.0 — exactly the same as for a free item. wasNull() must be called right after the getter it checks: it reports on the last column read, not on the row. With object getters this check is unnecessary: rs.getObject("price", BigDecimal.class) also returns null, and Optional.ofNullable(rs.getBigDecimal("price")) makes the missing price explicit in the domain object. The full type mapping is in the section MySQL to Java Type Mapping.
COALESCE in the query erases the difference between zero and “unknown”
In the example above, the grapes reached Java with a price_or_zero of 0.00, and the code can no longer tell a free item from an item without a price. For a report where a missing price counts as zero, that is fine. For a domain object, read the column as is: rs.getBigDecimal("price") returns null for NULL, and the Java code makes the decision (for example, with Optional). Primitive getters such as getInt() cannot do that — see the section on MySQL and Java types.
Common Mistakes with CASE, IF and NULL
Mistakes with conditions and NULL rarely produce an error: the query returns NULL, a wrong label or a strange number, and the report still looks plausible. The table lists cases not covered in the sections above, with MySQL 8 behavior and the fix.
| Query | What happens | How to do it right |
|---|---|---|
GREATEST(price, 2) | NULL for rows without a price: any NULL argument makes the result NULL (the same goes for LEAST) | GREATEST(COALESCE(price, 0), 2) |
lbs * CASE shop WHEN 'Acme' THEN 1.1 END | NULL for Delta and Berry Hill: a CASE without ELSE returns NULL, and NULL spreads through the arithmetic | Add ELSE 1 |
product || ': ' || price | With the default sql_mode, || is logical OR: the result is 1 (or NULL for the grapes) plus conversion warnings, not a string | CONCAT(...), or SET sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT') |
ORDER BY IFNULL(price, 'no price') | The result is a string, so sorting is alphabetical: a price of 10.00 comes before 2.50 | ORDER BY price IS NULL, price — numbers in order, missing prices last |
WHERE COALESCE(price, 0) = 0 | Correct rows, but an index on price is not used: the function wraps the column | WHERE price IS NULL OR price = 0; details in Functions in WHERE and Indexes |
Mistakes with string, numeric and date functions — SUBSTRING from position 0, the argument order of DATEDIFF, sorting by a formatted date — are collected in the lesson SQL Functions in MySQL.
Frequently Asked Questions
Does MySQL have NVL, DECODE and ISNULL?
MySQL has no NVL (Oracle) — use IFNULL or COALESCE instead. Oracle’s DECODE is replaced by CASE. ISNULL does exist in MySQL, but it does not substitute a value as in SQL Server: ISNULL(price) takes one argument and returns 1 or 0. The call ISNULL(price, 0) fails with ERROR 1582: Incorrect parameter count in the call to native function 'ISNULL'. The functions are compared in the section COALESCE vs IFNULL.
Should I use CASE or IF in MySQL queries?
In a query they are equally fast; the difference is portability and readability. IF() exists only in MySQL and MariaDB and handles one condition, so several conditions turn into nested calls. CASE is standard SQL and lists any number of branches in order. Do not confuse them with the statements of stored procedures: there IF ... THEN ... END IF and CASE ... END CASE control execution, and a CASE statement with no matching branch and no ELSE fails with ERROR 1339: Case not found for CASE statement, whereas the CASE expression simply returns NULL.
COALESCE or IFNULL: which one should I choose?
With two arguments in MySQL they give the same result. Choose COALESCE by default: it is standard SQL, accepts any number of arguments and works in PostgreSQL, Oracle and H2 tests. IFNULL is shorter but ties the query to MySQL. The comparison table is in the section COALESCE vs IFNULL.
How do I avoid division by zero in SQL?
Wrap the denominator in NULLIF: x / NULLIF(y, 0) returns NULL instead of an error when y is 0, in MySQL, PostgreSQL and Oracle alike. If the report needs a number instead of NULL, add a default on the outside: COALESCE(x / NULLIF(y, 0), 0). Why this matters in MySQL even though a plain SELECT only warns is explained in the section COALESCE, IFNULL and NULLIF.
Comments