Обработка NULL в расчетах с COALESCE()

Сегодня мы опять углубимся в тему обработки NULL и познакомимся с очень полезной функцией — COALESCE(). Эта функция позволяет элегантно справляться с NULL значениями в ваших данных.

Рассмотрим следующий пример: у вас есть таблица с данными о сотрудниках, но у некоторых сотрудников отсутствует информация о зарплате. Что будет, если мы попытаемся увеличить все зарплаты? Ничего хорошего. Ведь нельзя проводить операции с NULL. А что если мы хотим заменить NULL зарплаты на, скажем, 0? Здесь на сцену выходит COALESCE().

COALESCE() — это функция, которая возвращает первое не-NULL значение из переданного списка аргументов. Если все значения в списке равны NULL, функция возвращает NULL. Простыми словами, она говорит: "Дайте мне первое адекватное значение, которое вы можете найти, пожалуйста!"

Синтаксис

COALESCE(value1, value2, ..., value_n)

value1, value2, ..., value_n — это аргументы, которые вы передаете функции. Она вернёт первое значение, которое не равно NULL.

Примеры работы COALESCE()

Приведём парочку примеров.

Пример 1: Заменяем NULL на 0

Предположим, у нас есть таблица salaries:

Мы хотим посчитать общую сумму зарплат - это просто сделать:

SELECT SUM(salary) AS total_salary
FROM salaries;

Функция SUM() игнорирует NULL, так что проблем нет.

Но затем мы хотим посчитать общую сумму зарплат, если дадим каждому сотруднику премию в 1000.

SELECT SUM(salary+1000) AS total_salary
FROM salaries;

Считаем по строкам: 51000 + NULL + 61000 + NULL — поскольку NULL + 1000 = NULL, а SUM игнорирует NULL, на выходе получим 112000. Премия достанется только тем, у кого salary уже была указана; «безсолевых» сотрудников запрос обойдёт стороной.

Если мы хотим, чтобы премия в 1000 досталась всем (включая тех, у кого зарплата не указана), нужно сначала заменить NULL на 0 через COALESCE:

SELECT SUM(COALESCE(salary, 0) + 1000) AS total_salary
FROM salaries;

Теперь считаем: 51000 + 1000 + 61000 + 1000 = 114000. Это две разные бизнес-задачи (а не «правильный» и «неправильный» запрос) — выбирайте формулу в зависимости от того, кому реально полагается премия.

Результат:

Пример 2: Заменяем NULL на значение по умолчанию

Допустим, у нас есть таблица students с именами и адресами:

Мы хотим заменить NULL в адресах на "Не указано":

SELECT name, COALESCE(address, 'Не указано') AS resolved_address
FROM students;

Результат запроса:

Пример 3: Использование нескольких значений

Иногда бывает, что NULL нужно заменить не одним значением, а целым рядом значений. Например, мы хотим выбирать имя, имя для друзей или использовать "Без имени", если ни одно из них не задано. Таблица users:

Запрос:

SELECT user_id,
       COALESCE(first_name, short_name, 'Без имени') AS display_name
FROM users;

Результат:

Практическое применение COALESCE()

На практике COALESCE() — это спасательный круг для работы с неидеальными данными.

Посмотрим, как он помогает в различных задачах.

Пример 1: Замена значений в текстовых полях

Исходная таблица customers:

Запрос:

SELECT name, COALESCE(address, 'Не указано') AS address
FROM customers;

Результат:

Пример 2: Подготовка данных для отчетов

Исходная таблица sales:

Запрос:

SELECT SUM(COALESCE(price, 0)) AS total_sales
FROM sales;

Результат:

Типичные ошибки при использовании COALESCE()

Хотя COALESCE() кажется простой и универсальной функцией, с ней можно столкнуться с несколькими подводными камнями.

Несоответствие типов данных. Все аргументы, передаваемые в COALESCE(), должны быть совместимы по типу данных. Например, нельзя смешивать строковые значения и числовые.

-- Ошибка
SELECT COALESCE(salary, 'Не указано') FROM employees; 
-- salary — числовое поле, а 'Не указано' — текст.

Пренебрежение порядком аргументов. COALESCE() возвращает первое не-NULL значение, поэтому порядок аргументов играет важную роль.

2

Задача

SQL SELF, 9 уровень, 3 лекция

Недоступна

Замена NULL значений на значение по умолчанию

Замена NULL значений на значение по умолчанию

Открыть

2

Задача

SQL SELF, 9 уровень, 3 лекция

Недоступна

Замена NULL значений с несколькими уровнями приоритетов

Замена NULL значений с несколькими уровнями приоритетов

Открыть