Обробка 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 значення, тому порядок аргументів має велике значення.