Обробка 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;

І результат починає "плисти". Краще одразу позбутися NULL значень за допомогою функції COALESCE і замінити їх на 0. Дивимось, як це зробити:

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

Результат:

Так краще і надійніше.

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