Вопросы по теме «CASE WHEN, COALESCE, IFNULL и NULLIF в SQL на примерах MySQL»

Всего: 6 вопросов

1. 

Как работает CASE WHEN в SQL и чем поисковая форма CASE отличается от простой?

CASE — условное выражение стандарта SQL, аналог if / else if / else. Оно проверяет условия WHEN по порядку и возвращает THEN первой истинной ветки; если ни одна не подошла — значение ELSE, а без ELSE — NULL.

  • Поисковая форма CASE WHEN kg >= 30 THEN 'крупная' WHEN kg >= 15 THEN 'средняя' ELSE 'мелкая' END принимает любые условия. Ветки пишут от самой узкой к самой широкой: если первым поставить kg >= 15, 54 кг никогда не попадут в ветку «крупная».
  • Простая форма CASE product WHEN 'яблоки' THEN ... WHEN 'лимоны' THEN ... END сравнивает одно выражение со списком значений через =. Поэтому CASE price WHEN NULL не срабатывает ни для одной строки: price = NULL никогда не истинно. Для NULL нужна поисковая форма с IS NULL.

CASE работает везде, где допустимо выражение: в SELECT, WHERE, ORDER BY, UPDATE ... SET. В ORDER BY он задаёт порядок, которого нет в алфавите: ORDER BY CASE shop WHEN 'Дигма' THEN 1 WHEN 'АТБ' THEN 2 ELSE 3 END. В WHERE — разный порог для разных значений, но такое условие обычно понятнее оптимизатору, если записать его через AND/OR. Подсчёт по условию COUNT(CASE WHEN ... THEN 1 END) относится к агрегатным функциям.

2. 

Как работает функция IF в MySQL и что выбрать: CASE или IF?

IF(условие, значение_если_истина, значение_иначе) — короткая запись CASE с одной веткой: IF(kg >= 30, 'опт', 'розница'). Функция есть только в MySQL и MariaDB; в PostgreSQL, Oracle и стандарте SQL её нет, поэтому в переносимом коде пишут CASE WHEN ... THEN ... ELSE ... END.

Условие, равное NULL, IF считает ложным: IF(price > 100, 'дорого', 'обычно') для лимонов без цены вернёт «обычно», хотя о цене ничего не известно. Если неизвестное значение нужно показать отдельно, проверяйте его первым: CASE WHEN price IS NULL THEN 'нет цены' WHEN price > 100 THEN 'дорого' ELSE 'обычно' END.

Что выбрать: для одного условия с двумя исходами IF короче. CASE — стандарт SQL, допускает любое число веток и читается лучше вложенных IF(..., ..., IF(...)). Заметной разницы в скорости нет: оба выражения вычисляются для каждой строки.

3. 

В чём разница между COALESCE и IFNULL в MySQL и есть ли в MySQL NVL и ISNULL?

COALESCE(a, b, ...) возвращает первый аргумент, не равный NULL; IFNULL(a, b) делает то же для двух аргументов. С двумя аргументами в MySQL результат и скорость одинаковые, различаются они так:

  • Стандарт: COALESCE — стандарт SQL (MySQL, PostgreSQL, Oracle, SQL Server, H2), IFNULL — только MySQL/MariaDB и SQLite.
  • Число аргументов: у COALESCE любое, перебор идёт слева направо: COALESCE(mobile_phone, work_phone, email); у IFNULL ровно два.
  • Тип результата в MySQL у обеих общий для всех аргументов: IFNULL(price, 'нет цены') и COALESCE(price, 'нет цены') одинаково превращают и 45.00 в строку. Различие типов, о котором спрашивают на собеседованиях, относится к ISNULL в SQL Server.

Для кода, который может переехать на другую СУБД, и для тестов на H2 надёжнее COALESCE. Аналоги в других СУБД: NVL в Oracle (в MySQL её нет), ISNULL(a, b) в SQL Server; DECODE из Oracle заменяют на CASE. В MySQL ISNULL(x) — не замена значения, а проверка: принимает один аргумент и возвращает 1 или 0, а ISNULL(price, 0) завершится ERROR 1582: Incorrect parameter count.

4. 

Что делает функция NULLIF и зачем она нужна, если MySQL и так возвращает NULL при делении на ноль?

NULLIF(a, b) возвращает NULL, если аргументы равны, и a, если нет: NULLIF(0, 0) = NULL, NULLIF('abc', '') = abc. Это обратная операция к COALESCE: та убирает NULL, а NULLIF превращает выбранное значение в NULL.

Главное применение — защита от деления на ноль: x / NULLIF(y, 0). Деление на NULL даёт NULL, поэтому вместо ошибки получается пустое значение.

  • В SELECT MySQL и без NULLIF вернёт NULL с предупреждением 1365 Division by 0.
  • В INSERT и UPDATE режимы STRICT_TRANS_TABLES и ERROR_FOR_DIVISION_BY_ZERO включены в MySQL 8 по умолчанию, и то же деление завершается ERROR 1365 (22012): Division by 0.
  • В PostgreSQL ошибку даёт даже SELECT.

Запись x / NULLIF(y, 0) ведёт себя одинаково везде. Второй приём с NULLIF — COALESCE(NULLIF(TRIM(phone), ''), 'не указан'): пустая строка и NULL обрабатываются одинаково.

5. 

Почему CONCAT возвращает NULL и как склеить строки в MySQL, если одно из значений NULL?

CONCAT подчиняется общему правилу функций SQL: если хотя бы один аргумент NULL, результат тоже NULL. Подпись CONCAT(product, ': ', price) для винограда без цены вернёт не «виноград: », а NULL — вся строка пропадает из-за одного пустого столбца.

Есть два способа это исправить:

  • CONCAT_WS(разделитель, ...) (with separator) пропускает аргументы-NULL и ставит разделитель только между оставшимися значениями: CONCAT_WS(', ', shop, product, price) → АТБ, виноград. Подходит, когда пустое значение можно просто не показывать.
  • COALESCE внутри CONCAT сохраняет формат: CONCAT(product, ': ', COALESCE(price, 'цена не указана')) → виноград: цена не указана.

Оператор || для склейки в MySQL не подходит: по умолчанию он означает логическое ИЛИ, а не конкатенацию. В Oracle NULL при склейке через || считается пустой строкой, в PostgreSQL — нет, поэтому переносимый код с NULL лучше писать через CONCAT и COALESCE.

6. 

Где обрабатывать NULL при чтении из базы в Java: через COALESCE в SQL-запросе или в коде через JDBC?

NULL можно заменить двумя способами: через COALESCE прямо в запросе — тогда Java получает готовое значение, — или прочитать столбец как есть и решить в коде.

String sql = "SELECT price, COALESCE(price, 0) AS price_or_zero FROM sales WHERE shop = ?";
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();                             // true: про последний прочитанный столбец
  • COALESCE в запросе удобен для отчёта, где пустая цена означает ноль: код короче, итоги совпадают с SQL. Но он стирает разницу между нулём и «неизвестно»: виноград без цены придёт в Java как 0.00, и код уже не отличит его от бесплатного товара.
  • Чтение как есть нужно для объекта предметной области. Объектные геттеры (getBigDecimal, getString, getObject) возвращают null, и решение принимает Java-код, например через Optional.
  • Примитивные геттеры (getInt, getDouble) возвращают для NULL ноль. Отличить его от настоящего нуля можно только вызовом wasNull() сразу после геттера, до следующего getXxx.
Страница 1 из 1