CASE WHEN, COALESCE, IFNULL и NULLIF в SQL на примерах MySQL
Нужна подпись для витрины «продукт: цена», и запрос SELECT CONCAT(product, ': ', price) FROM sales; выглядит безобидно. Но для винограда, цена которого ещё не внесена, MySQL вернёт не «виноград: », а NULL — вся подпись пропадает из-за одного пустого столбца. Чтобы такие вещи не всплывали уже на сайте, нужно знать, как выражения и функции обращаются с NULL.
CASE WHEN и функция IF выбирают значение по условию прямо в запросе, а COALESCE, IFNULL и NULLIF заменяют NULL на значение по умолчанию или, наоборот, превращают значение в NULL. Все они однострочные: вычисляются для каждой строки и работают в SELECT, WHERE, ORDER BY, UPDATE ... SET. Строковые, числовые функции и функции даты разобраны в уроке Функции SQL в MySQL, подсчёт по условию COUNT(CASE WHEN ...) — в уроке про агрегатные функции.
1. Учебная таблица sales
Примеры выполняются в MySQL 8 на той же таблице sales, что и в уроке про функции SQL: восемь продаж фруктов в трёх магазинах, у двух позиций цена не внесена (NULL) — именно на них видна разница между функциями этого урока. Скрипт пересоздаёт таблицу:
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
id INT NOT NULL AUTO_INCREMENT,
shop VARCHAR(50) NOT NULL,
product VARCHAR(50) NOT NULL,
kg INT NOT NULL,
price DECIMAL(6,2) NULL,
sold_at DATETIME NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO sales (shop, product, kg, price, sold_at) VALUES
('АТБ', 'яблоки', 10, 45.00, '2024-12-30 18:45:00'),
('АТБ', 'груши', 14, 80.00, '2025-01-15 09:30:00'),
('АТБ', 'виноград', 20, NULL, '2025-01-31 20:10:00'),
('Дигма', 'яблоки', 20, 50.00, '2025-02-03 11:00:00'),
('Дигма', 'виноград', 34, 150.00, '2025-02-28 16:20:00'),
('Дигма', 'лимоны', 34, NULL, '2025-03-08 10:05:00'),
('Брусничка', 'лимоны', 14, 120.00, '2025-12-31 23:59:00'),
('Брусничка', 'яблоки', 54, 40.00, '2026-01-02 08:15:00'); SELECT * FROM sales; +----+-----------+----------+----+--------+---------------------+
| id | shop | product | kg | price | sold_at |
+----+-----------+----------+----+--------+---------------------+
| 1 | АТБ | яблоки | 10 | 45.00 | 2024-12-30 18:45:00 |
| 2 | АТБ | груши | 14 | 80.00 | 2025-01-15 09:30:00 |
| 3 | АТБ | виноград | 20 | NULL | 2025-01-31 20:10:00 |
| 4 | Дигма | яблоки | 20 | 50.00 | 2025-02-03 11:00:00 |
| 5 | Дигма | виноград | 34 | 150.00 | 2025-02-28 16:20:00 |
| 6 | Дигма | лимоны | 34 | NULL | 2025-03-08 10:05:00 |
| 7 | Брусничка | лимоны | 14 | 120.00 | 2025-12-31 23:59:00 |
| 8 | Брусничка | яблоки | 54 | 40.00 | 2026-01-02 08:15:00 |
+----+-----------+----------+----+--------+---------------------+
8 rows in set 2. CASE WHEN в SQL
CASE — условное выражение стандарта SQL, аналог цепочки if / else if / else: оно проверяет условия WHEN по порядку и возвращает значение из THEN первой истинной ветки. Если ни одна ветка не подошла, возвращается значение из ELSE, а без ELSE — NULL. CASE работает везде, где допустимо выражение: в SELECT, WHERE, ORDER BY, UPDATE ... SET.
Поисковая форма CASE WHEN условие THEN ... позволяет писать любые условия. Классификация продаж по объёму:
SELECT id, product, kg,
CASE
WHEN kg >= 30 THEN 'крупная'
WHEN kg >= 15 THEN 'средняя'
ELSE 'мелкая'
END AS size
FROM sales; +----+----------+----+-----------+
| id | product | kg | size |
+----+----------+----+-----------+
| 1 | яблоки | 10 | мелкая |
| 2 | груши | 14 | мелкая |
| 3 | виноград | 20 | средняя |
| 4 | яблоки | 20 | средняя |
| 5 | виноград | 34 | крупная |
| 6 | лимоны | 34 | крупная |
| 7 | лимоны | 14 | мелкая |
| 8 | яблоки | 54 | крупная |
+----+----------+----+-----------+ 54 кг удовлетворяют обоим условиям, но срабатывает первая ветка, поэтому ветки пишут от самой узкой к самой широкой. Поменяйте WHEN местами — и все продажи от 15 кг станут «средними».
Простая форма CASE выражение WHEN значение THEN ... сравнивает одно выражение со списком значений через =. Без ELSE неперечисленные значения превращаются в NULL:
SELECT DISTINCT product,
CASE product
WHEN 'яблоки' THEN 'семечковые'
WHEN 'груши' THEN 'семечковые'
WHEN 'лимоны' THEN 'цитрусовые'
END AS fruit_group
FROM sales
ORDER BY product; +----------+-------------+
| product | fruit_group |
+----------+-------------+
| виноград | NULL |
| груши | семечковые |
| лимоны | цитрусовые |
| яблоки | семечковые |
+----------+-------------+ Почему CASE price WHEN NULL не срабатывает
Простая форма сравнивает через =, а price = NULL никогда не бывает истинным (причина — в разделе NULL значения урока про SELECT). Поэтому ветка WHEN NULL не сработает ни для одной строки:
SELECT id, price,
CASE price WHEN NULL THEN 'нет цены' ELSE 'есть цена' END AS simple_case,
CASE WHEN price IS NULL THEN 'нет цены' ELSE 'есть цена' END AS searched_case
FROM sales
WHERE shop = 'АТБ'; +----+-------+-------------+---------------+
| id | price | simple_case | searched_case |
+----+-------+-------------+---------------+
| 1 | 45.00 | есть цена | есть цена |
| 2 | 80.00 | есть цена | есть цена |
| 3 | NULL | есть цена | нет цены |
+----+-------+-------------+---------------+ Для NULL годится только поисковая форма с IS NULL.
CASE в ORDER BY и WHERE
В ORDER BY выражение CASE задаёт порядок, которого нет ни в алфавите, ни в числах. Сначала «Дигма», потом «АТБ», остальные магазины в конце:
SELECT id, shop, product
FROM sales
ORDER BY CASE shop WHEN 'Дигма' THEN 1 WHEN 'АТБ' THEN 2 ELSE 3 END, id; +----+-----------+----------+
| id | shop | product |
+----+-----------+----------+
| 4 | Дигма | яблоки |
| 5 | Дигма | виноград |
| 6 | Дигма | лимоны |
| 1 | АТБ | яблоки |
| 2 | АТБ | груши |
| 3 | АТБ | виноград |
| 7 | Брусничка | лимоны |
| 8 | Брусничка | яблоки |
+----+-----------+----------+ В WHERE через CASE можно задать разный порог для разных магазинов: для «Бруснички» интересны продажи от 50 кг, для остальных — от 20 кг.
SELECT id, shop, kg
FROM sales
WHERE kg >= CASE shop WHEN 'Брусничка' THEN 50 ELSE 20 END; +----+-----------+----+
| id | shop | kg |
+----+-----------+----+
| 3 | АТБ | 20 |
| 4 | Дигма | 20 |
| 5 | Дигма | 34 |
| 6 | Дигма | 34 |
| 8 | Брусничка | 54 |
+----+-----------+----+ То же условие можно записать без CASE: (shop = 'Брусничка' AND kg >= 50) OR (shop <> 'Брусничка' AND kg >= 20). Такую форму оптимизатору проще разобрать, поэтому в WHERE CASE используют редко (как выражение над столбцом мешает индексу, разобрано в разделе Функции в WHERE и индексы). Подсчёт строк по условию — COUNT(CASE WHEN ... THEN 1 END) — разобран в уроке про агрегатные функции.
3. Функция IF в MySQL
IF(условие, значение_если_истина, значение_иначе) — короткая запись CASE с одной веткой, которая есть только в MySQL (и MariaDB). В PostgreSQL, Oracle и стандарте SQL такой функции нет, поэтому в переносимом коде пишут CASE WHEN условие THEN ... ELSE ... END. Условие, равное NULL, функция считает ложным.
SELECT id, kg, price,
IF(kg >= 30, 'опт', 'розница') AS sale_type,
IF(price > 100, 'дорого', 'обычно') AS price_level
FROM sales
WHERE shop = 'Дигма'; +----+----+--------+-----------+-------------+
| id | kg | price | sale_type | price_level |
+----+----+--------+-----------+-------------+
| 4 | 20 | 50.00 | розница | обычно |
| 5 | 34 | 150.00 | опт | дорого |
| 6 | 34 | NULL | опт | обычно |
+----+----+--------+-----------+-------------+ Строка 6 — лимоны без цены — получила «обычно»: NULL > 100 даёт NULL, и IF выбрал ветку «иначе». Отчёт выглядит правдоподобно, хотя о цене лимонов ничего не известно. Если неизвестное значение нужно показывать отдельно, проверяйте его первым: CASE WHEN price IS NULL THEN 'нет цены' WHEN price > 100 THEN 'дорого' ELSE 'обычно' END.
4. COALESCE, IFNULL и NULLIF: работа с NULL
COALESCE(a, b, ...) возвращает первый аргумент, который не равен NULL; IFNULL(a, b) — то же для двух аргументов, только в MySQL; NULLIF(a, b) делает обратное — возвращает NULL, если аргументы равны, и a, если нет. Все три функции нужны, чтобы NULL не испортил вычисление или вывод.
SELECT id, product, price,
COALESCE(price, 0) AS price_or_zero,
IFNULL(price, 'нет цены') AS price_text,
kg * COALESCE(price, 0) AS revenue
FROM sales
WHERE shop = 'АТБ'; +----+----------+-------+---------------+------------+---------+
| id | product | price | price_or_zero | price_text | revenue |
+----+----------+-------+---------------+------------+---------+
| 1 | яблоки | 45.00 | 45.00 | 45.00 | 450.00 |
| 2 | груши | 80.00 | 80.00 | 80.00 | 1120.00 |
| 3 | виноград | NULL | 0.00 | нет цены | 0.00 |
+----+----------+-------+---------------+------------+---------+ Столбец price_text выровнен по левому краю не случайно: раз один из аргументов — строка, весь результат IFNULL стал строкой, и 45.00 в нём уже текст. COALESCE с несколькими аргументами перебирает их слева направо: COALESCE(NULL, NULL, 'третий', 'четвёртый') вернёт 'третий'. Типичный случай — контакт клиента: COALESCE(mobile_phone, work_phone, email).
NULLIF чаще всего защищает от деления на ноль:
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 В SELECT MySQL и без NULLIF вернёт NULL с предупреждением 1365 Division by 0. Разница появляется в INSERT и UPDATE: в MySQL 8 режимы STRICT_TRANS_TABLES и ERROR_FOR_DIVISION_BY_ZERO включены по умолчанию, и деление на ноль при записи данных завершается ERROR 1365 (22012): Division by 0. В PostgreSQL ошибку даёт даже SELECT. Знаменатель вида x / NULLIF(y, 0) работает одинаково везде. Второй частый приём — COALESCE(NULLIF(TRIM(phone), ''), 'не указан'): пустая строка и NULL обрабатываются одинаково.
COALESCE vs IFNULL: в чём разница
В MySQL COALESCE(a, b) и IFNULL(a, b) с двумя аргументами возвращают одно и то же и работают с одинаковой скоростью. Различаются они переносимостью и числом аргументов:
| Свойство | COALESCE | IFNULL |
|---|---|---|
| Стандарт | Стандарт SQL: работает в MySQL, PostgreSQL, Oracle, SQL Server, H2 | Только MySQL/MariaDB (и SQLite); в Oracle аналог — NVL, в SQL Server — ISNULL |
| Число аргументов | Любое, от одного | Ровно два |
| Тип результата в MySQL | Общий тип всех аргументов | Общий тип двух аргументов (строка > дробное > целое) |
| Вычисление аргументов | Слева направо до первого не-NULL | Второй аргумент — только если первый NULL |
Разницу в типе результата часто вспоминают на собеседованиях, но она относится к SQL Server: там ISNULL(a, b) берёт тип первого аргумента и может обрезать b, а COALESCE выбирает тип с наивысшим приоритетом. В MySQL обе функции выводят общий тип, поэтому IFNULL(price, 'нет цены') и COALESCE(price, 'нет цены') одинаково превращают число в строку. Для кода, который может переехать на другую СУБД, и для тестов на H2 надёжнее COALESCE.
CONCAT и NULL: CONCAT_WS
CONCAT возвращает NULL, если хотя бы один аргумент NULL. CONCAT_WS(разделитель, ...) (with separator) пропускает аргументы-NULL и ставит разделитель только между оставшимися значениями. Это запрос из начала урока:
SELECT id,
CONCAT(product, ': ', price) AS label,
CONCAT_WS(', ', shop, product, price) AS line
FROM sales
WHERE shop = 'АТБ'; +----+---------------+--------------------+
| id | label | line |
+----+---------------+--------------------+
| 1 | яблоки: 45.00 | АТБ, яблоки, 45.00 |
| 2 | груши: 80.00 | АТБ, груши, 80.00 |
| 3 | NULL | АТБ, виноград |
+----+---------------+--------------------+ Если подпись нужна именно в формате «продукт: цена», NULL заменяют внутри CONCAT: CONCAT(product, ': ', COALESCE(price, 'цена не указана')) даст для винограда виноград: цена не указана. В PostgreSQL и Oracle конкатенация через || ведёт себя по-разному (в Oracle NULL считается пустой строкой), а в MySQL || по умолчанию вообще означает логическое ИЛИ.
5. COALESCE и null в Java: JDBC
NULL можно заменить двумя способами: через COALESCE прямо в запросе — тогда Java получает готовое значение, — или прочитать столбец как есть и обработать null в коде. Объектные геттеры getBigDecimal, getString, getObject возвращают null, а примитивные (getInt, getDouble) — ноль, и отличить его от NULL можно только вызовом wasNull().
String sql = "SELECT id, 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, "АТБ");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
BigDecimal price = rs.getObject("price", BigDecimal.class); // null для NULL
BigDecimal orZero = rs.getBigDecimal("price_or_zero"); // 0.00 для NULL
double asDouble = rs.getDouble("price"); // 0.0 для NULL
boolean wasNull = rs.wasNull(); // относится к последнему прочитанному столбцу
System.out.println(rs.getInt("id") + " " + rs.getString("product") + " "
+ price + " " + orZero + " " + asDouble + " " + wasNull);
}
}
}
// Вывод:
// 1 яблоки 45.00 45.00 45.0 false
// 2 груши 80.00 80.00 80.0 false
// 3 виноград null 0.00 0.0 true wasNull() отвечает только про столбец, прочитанный последним, поэтому вызывать его нужно сразу после геттера — до следующего getXxx. Для отчёта, где пустая цена означает ноль, удобнее COALESCE в запросе: код короче, а итог по выручке совпадает с SQL. Таблица соответствия типов MySQL и Java — в разделе о типах MySQL и Java.
COALESCE в запросе стирает разницу между нулём и «неизвестно»
В примере выше виноград пришёл в Java в столбце price_or_zero с ценой 0.00, и код уже не отличит бесплатный товар от товара без цены. Для отчёта, где пустая цена считается нулём, это нормально. Для объекта предметной области лучше читать столбец как есть: rs.getBigDecimal("price") вернёт null для NULL, и решение примет Java-код (например, через Optional). Примитивные геттеры вроде getInt() так не умеют — про них в разделе о типах MySQL и Java.
6. Где ошибаются с CASE, IF и NULL
Ошибки с условными выражениями почти никогда не дают сообщения об ошибке: запрос возвращает правдоподобный, но неверный результат. В таблице — случаи, которых нет в разделах выше, с поведением MySQL 8 на таблице sales и исправлением.
| Запрос | Что происходит | Как правильно |
|---|---|---|
IF(price, 'есть цена', 'нет цены') | Число в условии проверяется на ноль: товар с ценой 0.00 получит «нет цены» так же, как NULL | IF(price IS NOT NULL, 'есть цена', 'нет цены') |
revenue / IFNULL(kg, 0) | IFNULL заменяет NULL на ноль и сам создаёт деление на ноль: NULL с предупреждением в SELECT, ERROR 1365 в UPDATE | revenue / NULLIF(kg, 0) |
CASE WHEN price IS NULL THEN 'нет цены' ELSE price END AS p ... ORDER BY p | Ветки разного типа: результат CASE становится строкой, и '120.00' сортируется раньше '40.00' | ORDER BY price, а текст — только в списке SELECT |
SELECT id, COALESCE(price, 0) AS price FROM sales WHERE price = 0 | Empty set: WHERE видит столбец таблицы price, а не псевдоним, и строки с NULL не находит | WHERE price IS NULL OR price = 0 |
Ошибки со строковыми функциями, округлением и датами (SUBSTRING с позиции 0, DATEDIFF с переставленными аргументами, сортировка по DATE_FORMAT) собраны в разделе ошибок урока про функции SQL.
Часто задаваемые вопросы
Есть ли в MySQL функции NVL, DECODE и ISNULL?
NVL (Oracle) в MySQL нет — вместо неё IFNULL или COALESCE. Вместо DECODE из Oracle пишут CASE. А ISNULL в MySQL есть, но это не замена значения, как в SQL Server: ISNULL(price) принимает один аргумент и возвращает 1 или 0. Вызов ISNULL(price, 0) завершится ошибкой ERROR 1582: Incorrect parameter count in the call to native function 'ISNULL'. Сравнение функций — в разделе COALESCE vs IFNULL.
Что выбрать в MySQL: CASE или IF?
Для одного условия с двумя исходами IF короче, но он есть только в MySQL и MariaDB. CASE — стандарт SQL, допускает любое число веток и читается лучше вложенных IF(..., ..., IF(...)). Заметной разницы в скорости нет: оба выражения вычисляются для каждой строки. Как IF обращается с NULL в условии — в разделе Функция IF в MySQL.
COALESCE или IFNULL: что использовать в Java-проекте?
COALESCE. В MySQL результат одинаковый, но COALESCE входит в стандарт SQL и без изменений работает в PostgreSQL и Oracle, где IFNULL нет, — запрос переживёт смену СУБД. Таблица различий — в разделе COALESCE vs IFNULL.
Зачем NULLIF, если MySQL и так возвращает NULL при делении на ноль?
NULL вместо ошибки MySQL даёт только в SELECT. В INSERT и UPDATE со строгим режимом MySQL 8 то же деление завершается ERROR 1365 (22012): Division by 0, а PostgreSQL падает даже на SELECT. Запись x / NULLIF(y, 0) ведёт себя одинаково везде — пример в разделе COALESCE, IFNULL и NULLIF.
Комментарии