Вопросы по теме «CASE WHEN, COALESCE, IFNULL и NULLIF в SQL на примерах MySQL»
Сначала изучите урок: CASE WHEN, COALESCE, IFNULL и NULLIF в SQL на примерах MySQL
Всего: 6 вопросов
1. Как работает CASE WHEN в SQL и чем поисковая форма CASE отличается от простой?
Как работает 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 в 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 и 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 и зачем она нужна, если MySQL и так возвращает NULL при делении на ноль?
NULLIF(a, b) возвращает NULL, если аргументы равны, и a, если нет: NULLIF(0, 0) = NULL, NULLIF('abc', '') = abc. Это обратная операция к COALESCE: та убирает NULL, а NULLIF превращает выбранное значение в NULL.
Главное применение — защита от деления на ноль: x / NULLIF(y, 0). Деление на NULL даёт NULL, поэтому вместо ошибки получается пустое значение.
- В
SELECTMySQL и безNULLIFвернётNULLс предупреждением 1365Division 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 возвращает 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 при чтении из базы в 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.