Замена NULL на 0 в SQL: функция SUM и отсутствие данных

Быстрый ответ

Для элегантного преобразования NULL, полученного от SQL-функции, в 0 воспользуйтесь функцией COALESCE:

SELECT COALESCE(yourFunction(...), 0);

Теперь если результат функции будет NULL, на его место подставится надежное значение 0.

Понимание NULL и агрегатных функций в SQL

Корректное применение SQL-функций критически важно для обеспечения точности данных. Агрегатные функции вида SUM() возвращают NULL в случае отсутствия соответствующих записей. Для обеспечения более предсказуемых результатов распространено заменять NULL на 0 с помощью функции COALESCE:

SELECT COALESCE(SUM(column), 0) ... -- Если SUM ничего не нашла, COALESCE выручит, подставив ноль.

Как альтернативу можно использовать функцию ISNULL:

SELECT ISNULL(SUM(column), 0) ... -- Если в результатах SUM обнаружен NULL, функция ISNULL заменит его на ноль.

ISNULL проверяет первый аргумент на NULL, и если он является таковым, заменяет его вторым аргументом. Однако поддержка ISNULL ограничена двумя параметрами. Если целью является простое замещение NULL в SQL Server, ISNULL будет эффективной, но COALESCE предпочтительнее за свою универсальность.

Переводите возможные NULL в подзапросах в нули для учета, подобного тому, как это делается в CRM-системах:

SELECT COALESCE((SELECT SUM(column) FROM table), 0); -- "Не найдено данных? Получите ноль!"

Преобразование NULL не только в нули и не только для агрегатных функций

Предложение альтернативных значений

Если ожидается не ноль, а другое значение? Функция COALESCE без проблем с этим справится! Она позволяет вам определить цепочку возможных альтернатив:

SELECT COALESCE(column1, column2, 'alternative') FROM table;

Подробнее об этом расскажет наш спикер на видео

Оценка производительности

Важно учитывать, что функции COALESCE и ISNULL могут вести себя по-разному в зависимости от системы управления базой данных и конкретных условий. Рекомендуется проводить тестирование запросов, чтобы выбрать наиболее производительное решение.

Обработка сложных сценариев

Функции COALESCE и ISNULL успешно справляются не только с заменой значений одного столбца, они также эффективны в более сложных сценариях:

SELECT COALESCE(AVG(NULLIF(column, 0)), 0) ... -- "Нужно среднее значение без учета нулей? Получите ноль при NULL!"

Визуализация

Представьте оценивание прыжков в воду на соревнованиях и вы играете в этом процессе роль судьи:

Прыжок 1: 7.5   | Прыжок 2: NULL   | Прыжок 3: 8.0

Пропущенный балл вполне можно сравнить с невыполненным прыжком:

До: [⭐, ❓, ⭐]

Используя COALESCE или ISNULL мы заменяем пропущенный балл на 0, таким образом у всех спортсменов появляется результат:

SELECT COALESCE(score_diver, 0);

Мы получаем следующее представление оценок:

После:  [⭐, 0️⃣, ⭐]

Соревнование продолжается без перебоев!

Лучшие практики и профессиональные советы

Функция COUNT() и NULL

Функция COUNT() всегда возвращает минимум 0, т.к. подсчитывает строки, а не уникальные значения. Не есть необходимостью оборачивать COUNT() в COALESCE или ISNULL:

SELECT COALESCE(COUNT(column), 0) ... -- Будет ли NULL для пустой таблицы? Нет, функция COUNT всегда возвращает результат, хотя бы ноль.

Обеспечение согласованности типов данных

Важно совпадение типов данных запасного значения с ожидаемым типом данных столбца, чтобы избежать нежелательных эффектов.

Каскадирование COALESCE для обработки нескольких NULL

Если стоит задача обработки нескольких потенциальных NULL, COALESCE идеально подойдет для решения этой проблемы:

SELECT COALESCE(column1, column2, default_value) ... -- "NULLы передают эстафету, а на конец — замена на ноль!"

Учитывайте разницу в поведении фукнций в разных базах данных

Имейте в виду, что поведение функций COALESCE и ISNULL может заметно отличаться в различных базах данных. Обязательно изучите документацию по вашей системе управления базами данных.

Полезные материалы

1. COALESCE (Transact-SQL) – SQL Server | Microsoft Learn – подробное описание использвания COALESCE для обработки NULL в SQL Server.
2. Функция SQL Server ISNULL() — отличное пошаговое руководство по функции ISNULL для замены NULL на указанное значение.
3. SQL – Using ISNULL instead of COALESCE for checking a specific condition? – Stack Overflow — обсуждение на Stack Overflow о сравнении ISNULL и COALESCE.
4. NVL (Oracle Documentation) — описание функции NVL в Oracle, выполняющей аналогичное использование COALESCE или ISNULL.
5. ZeroIFNull usage in Teradata — страничка с рекомендациями по использованию ZeroIFNull в Teradata для обработки значений NULL (ссылка в задаче некорректна, заменена на более подходящую по смыслу).