Розуміння функції COALESCE () у SQL

Автор

У міру того, як технології невпинно розвиваються, для розробника стає критично важливим відстежувати найновіші тенденції. Незалежно від рівня вашого досвіду, глибоке розуміння маніпулювання рядками є ключовим. Воно дозволяє ефективно обробляти дані, наприклад, створювати унікальні формати, адаптовані до потреб вашого бізнесу, та керувати ними за допомогою вбудованих функцій SQL-сервера.

Окрім зміни даних, важливо вміти аналізувати набори даних, оцінювати їх значення та виконувати кодування чи декодування для отримання більш значущої інформації. Це допомагає ефективно обробляти відсутні значення, розуміти їхній вплив на обчислення та оптимізувати роботу з даними, запобігаючи появі нульових значень, які можуть спотворити результати операцій.

Цей посібник присвячено функції об’єднання (COALESCE) в SQL, яка є потужним інструментом для створення складних програм. Ми припускаємо, що ви вже знайомі з SQL та маєте певний досвід роботи з ним, і зараз прагнете поглибити свої знання саме цієї функції. Наша серія навчальних матеріалів з SQL допоможе вам швидко освоїти необхідні навички.

Що таке COALESCE() в SQL та для чого вона використовується?

Функція COALESCE в SQL послідовно аналізує список наданих параметрів (аргументів) та повертає перше значення, яке не є нульовим (NULL). Іншими словами, функція перевіряє кожен аргумент у списку і завершує роботу, щойно знаходить перше ненульове значення. Якщо всі аргументи є нульовими, функція повертає NULL.

Ця функція є широко підтримуваною і використовується в різних системах баз даних, таких як MySQL, Azure SQL Database, Oracle та PostgreSQL.

COALESCE особливо корисна у таких випадках:

  • Обробка значень NULL.
  • Об’єднання декількох запитів в один.
  • Запобігання використанню довгих і складних операторів CASE.

На відміну від операторів CASE або функції ISNULL, COALESCE може приймати необмежену кількість параметрів, тоді як CASE зазвичай приймає лише два. Це дозволяє писати більш компактний і зрозумілий код.

Ось загальний синтаксис функції:

COALESCE(valueOne, valueTwo, valueThree, …, valueX);

Серед властивостей COALESCE в SQL Server можна виділити: вимогу до аргументів одного типу даних, можливість приймати багато параметрів, а також поведінку з цілочисловими аргументами, коли функція повертає ціле число в результаті.

Читайте також: Повний шпаргалка з SQL для швидкого ознайомлення

Перед тим, як розглядати використання COALESCE, давайте розберемося з поняттям NULL.

Що таке значення NULL в SQL?

Унікальний маркер NULL в SQL вказує на відсутність значення у базі даних. Його можна уявити як невизначене або невідоме значення. Важливо не плутати NULL з порожнім рядком або нульовим значенням; це саме відсутність будь-якого значення. Нульове значення в стовпці таблиці означає, що інформація відсутня.

Наприклад, у базі даних інтернет-магазину стовпець може містити NULL, якщо клієнт не надав свій ідентифікатор. NULL в SQL – це особливий стан, який відрізняється від інших мов програмування, де це може означати “не вказує на жоден об’єкт”.

Значення NULL мають важливий вплив на реляційні бази даних. По-перше, вони дозволяють ігнорувати певні значення під час роботи з іншими внутрішніми функціями. Наприклад, ви можете складати список загальних замовлень у виробничій системі, де деякі замовлення ще не виконані. Використання NULL дозволить функції SUM підсумувати лише ті замовлення, де є дані.

Також розглянемо випадок, коли потрібно обчислити середнє значення, використовуючи функцію AVG. Якщо у вас є нульові значення, це може спотворити результати. В такому випадку, база даних може ігнорувати поля з NULL і використовувати лише ті значення, де є дані, що забезпечить точніші результати.

Значення NULL мають і деякі недоліки. Вони вважаються значеннями змінної довжини, займаючи один або кілька байтів. Оскільки база даних резервує місце для цих байтів, це може призвести до збільшення розміру бази даних на диску, порівняно з використанням звичайних значень.

Крім того, при роботі з певними функціями необхідно буде додатково налаштовувати їх для обробки значень NULL, що може ускладнити ваш код SQL.

Обробка значень NULL за допомогою COALESCE()

Нульові значення означають, що значення може існувати, але ви не знаєте, яке воно. До моменту отримання даних, які заповнять ваші поля реальними значеннями, NULL є заповнювачами.

Хоча ви можете використовувати значення NULL для різних типів даних, включаючи десяткові числа, рядки, дати та цілі числа, краще уникати їх при роботі з числовими даними.

Проблема полягає в тому, що при використанні з числовими значеннями, вам, ймовірно, доведеться додавати додаткові коментарі при розробці коду, який працює з цими даними. Про це ми поговоримо далі.

Функцію COALESCE() можна використовувати різними способами для обробки NULL:

Заміна нульових значень на конкретне значення за допомогою COALESCE()

Ви можете використовувати COALESCE(), щоб замінити всі нульові значення на певні задані. Наприклад, у вас може бути таблиця “працівники” зі стовпцем “зарплата”, де може бути NULL, якщо зарплата працівника ще не нарахована. У такому випадку, при виконанні розрахунків, ви можете використовувати певне значення, наприклад, нуль, для всіх NULL записів. Ось як це зробити:

SELECT COALESCE(salary, 0) AS adjusted_salary
FROM employees;

Вибір першого ненульового значення з кількох варіантів за допомогою COALESCE()

Іноді потрібно використовувати перше ненульове значення зі списку виразів. У таких випадках у вас може бути кілька стовпців зі схожими даними, і ви хочете визначити пріоритетність їхніх значень, що не є NULL. Синтаксис залишається тим же:

COALESCE (expression1, expression2, …)

На практиці, припустімо, що у вас є таблиця контактів зі стовпцями preferred_name і full_name. Ви хочете створити список контактів разом з їхніми улюбленими іменами (якщо вони є) або з їхніми повними іменами. Ось як це можна зробити:

SELECT COALESCE(preferred_name, full_name) AS display_name
FROM contacts.

Якщо значення preferred_name не є NULL для даного запису, воно буде повернуто. В іншому випадку повне ім’я буде використане як відображуване ім’я.

Конкатенація рядків за допомогою SQL COALESCE

Ви можете зіткнутися з проблемами при конкатенації рядків в SQL, коли використовуються нульові значення. У таких випадках ви отримаєте NULL, як небажаний результат. Щоб уникнути цього, можна використати функцію COALESCE. Розглянемо приклад.

Звичайна конкатенація рядків виглядає так:

SELECT ‘Привіт, де ти, ‘ || ‘Іван’ || ‘?’ AS example

Результатом буде:

Приклад: Привіт, де ти, Іване?

Однак, якщо використовувати NULL, як показано нижче:

SELECT ‘Привіт, де ти, ‘ || null || ‘?’ AS example

Ви отримаєте:

Оскільки будь-яка конкатенація рядка з NULL повертає NULL, то вищеподаний результат буде NULL. Проблему можна вирішити за допомогою COALESCE(). Вона дозволяє замінити NULL на порожній рядок (або пробіл). Наприклад, припустимо, що ви виводите назви автомобілів разом з їхніми виробниками, ось ваш запит:

SELECT
car || ‘, виробник: ‘ || COALESCE(manufacturer, ‘—') AS car_brand
FROM stock

Якщо виробник має NULL, то ви отримаєте “—” замість NULL. Ось очікувані результати:

car_brandoutlander, виробник: —flying spurs, виробник: Bentleyroyal athlete, виробник: —royal saloon, виробник: Crown

Як бачите, значення NULL замінено на вказане значення рядка.

Функція SQL Coalesce і зведення даних

Зведення даних в SQL – це техніка, яка використовується для перетворення рядків у стовпці. Це дозволяє транспонувати дані з “нормалізованої” форми (з багатьма рядками та меншою кількістю стовпців) у “денормалізовану” (з меншою кількістю рядків та більшою кількістю стовпців). Функцію COALESCE можна використовувати разом зі зведенням даних для обробки нульових значень у результатах.

Коли ви використовуєте PIVOT у SQL, перетворюючи рядки у стовпці, результуючі стовпці є агрегатами даних. Якщо в якомусь випадку агрегація дає NULL для певної комірки, ви можете використовувати COALESCE, щоб замінити NULL на значення за замовчуванням або інше значиме значення. Розглянемо приклад.

Припустимо, у вас є таблиця “Продажі” зі стовпцями “рік”, “квартал” і “дохід”, і ви хочете звести дані так, щоб роки були стовпцями, а сума доходу за кожен квартал – значеннями. Деякі квартали можуть не мати даних про дохід, і тоді ви отримаєте NULL у зведених результатах. У цьому випадку ви можете використати COALESCE, щоб замінити нульові значення в зведеному результаті на нуль (0).

SELECT
    year,
    COALESCE(SUM(CASE WHEN quarter="Q1" THEN revenue END), 0) AS Q1_Revenue,
    COALESCE(SUM(CASE WHEN quarter="Q2" THEN revenue END), 0) AS Q2_Revenue,
    COALESCE(SUM(CASE WHEN quarter="Q3" THEN revenue END), 0) AS Q3_Revenue,
    COALESCE(SUM(CASE WHEN quarter="Q4" THEN revenue END), 0) AS Q4_Revenue
FROM sales
GROUP BY year;

Скалярна функція, визначена користувачем, та функція об’єднання SQL

Ви можете поєднувати скалярні функції, визначені користувачем (UDF), з функцією COALESCE для реалізації складної логіки обробки нульових значень. Поєднання цих функцій допоможе вам виконувати складні перетворення даних і обчислення в SQL-запитах. Розглянемо таблицю Employees з наступною структурою:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary INT,
    Bonus INT
);

Припустимо, ви хочете обчислити загальний дохід кожного працівника (зарплата плюс премія). Однак деякі значення можуть бути відсутні. У такому випадку ваша скалярна функція UDF може обробляти додавання зарплати та премії, тоді як COALESCE обробляє NULL. Ось скалярна UDF для загального доходу:

CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
    DECLARE @totalEarnings INT;
    SET @totalEarnings = @salary + COALESCE(@bonus, 0);
    RETURN @totalEarnings;
END;
You can then use the scalar UDF with coalesce in a query:
SELECT EmployeeID, FirstName, LastName,
       Salary, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employees;

Перевірка даних за допомогою SQL COALESCE

При роботі з базами даних вам може знадобитися перевіряти числові значення. Наприклад, припустимо, що у вас є стовпці product_name, price і discount в таблиці products. Ви хочете отримати назви товарів, ціни та знижки кожного товару. Усі значення знижки NULL ви хочете розглядати як 0. У цьому випадку функція COALESCE може бути корисною. Ось як нею користуватися:

SELECT product_name, price, COALESCE(discount, 0) AS discount
FROM products

SQL COALESCE і обчислені стовпці

Обчислені стовпці – це віртуальні стовпці, обчислені на основі виразів або інших стовпців у таблиці. Оскільки обчислені стовпці фізично не зберігаються в базі даних, ви можете використовувати їх разом із функцією COALESCE під час обробки складних сценаріїв і перетворень. Розглянемо практичний приклад.

Припустимо, що у вас є таблиця `products` зі стовпцями `price`, `discount` і `tax_rate`. Ви хочете створити обчислений стовпець `total_price`, щоб відобразити остаточну ціну товару після застосування знижки та податку. Якщо знижка або податок не вказані (NULL), ви маєте використовувати нуль для обчислення. Ось як використовувати COALESCE відповідно до операції:

CREATE TABLE products(
price DECIMAL(10, 2),
discount DECIMAL(10, 2),
tax_rate DECIMAL(5, 2),
total_price AS (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1)
);

В наведеному вище коді відбувається наступне:

  • Обчислений стовпець total_price визначається як (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1).
  • Якщо price є NULL, COALESCE(price, 0) гарантує, що він буде розглядатись як 0.
  • Якщо discount є NULL, COALESCE(price*discount, 0) гарантує, що він буде розглядатись як 0, і множення не вплине на обчислення.
  • Якщо tax_rate є NULL, COALESCE(1 + tax_rate, 1) гарантує, що він буде розглядатись як 1, тобто податок не застосовується, і множення не вплине на обчислення.

Така конфігурація дозволяє створити обчислений стовпець total_price із фактичною остаточною ціною, незалежно від наявності чи відсутності NULL.

SQL COALESCE і вираз CASE

Синтаксично ви можете використовувати COALESCE через вираз CASE. Наприклад:

SELECT
Productname + ‘ ’+ deliverydate productdetails,
dealer,
CASE
WHEN cellphone is NOT NULL Then cellphone
WHEN workphone is NOT NULL Then workphone
ELSE ‘NA’
END
EmergencyContactNumber
FROM
dbo.tb_EmergencyContact

У цьому випадку запит CASE аналогічний функції COALESCE.

Також можливе використання виразів COALESCE і CASE в одному запиті. Ці два методи можуть обробляти значення NULL і застосовувати умовну логіку одночасно. Розглянемо приклад.

Припустимо, що у вас є таблиця продуктів зі стовпцями product_id, product_name, price і discount. Деякі ваші продукти мають знижку, а інші – ні. Якщо на товар діє знижка, ви хочете показати ціну зі знижкою, інакше – звичайну ціну.

SELECT
    product_id,
    product_name,
    price,
    COALESCE(
        CASE
            WHEN discount > 0 THEN price - (price * discount / 100)
            ELSE NULL
        END,
        price
    ) AS discounted_price
FROM products;

У цьому коді `CASE` перевіряє, чи `discount` більший за нуль, і обчислює ціну зі знижкою, інакше повертає NULL. Функція `COALESCE` приймає результат з `CASE` та `price` як параметри. Вона повертає перше значення, яке не є NULL, тобто ціну зі знижкою, якщо вона є, або звичайну ціну, якщо її немає.

Заключні слова

У цій публікації було показано різні способи використання функції COALESCE у ваших SQL-запитах. Обчислюючи параметри у визначеному порядку та повертаючи перше ненульове значення, функція COALESCE спрощує запити, роблячи їх ефективнішими.

COALESCE є універсальною функцією, незалежно від того, чи працюєте ви з нульовими значеннями, конкатенацією рядків, зведенням даних, перевіркою або обчислюваними стовпцями. Освоївши функцію COALESCE, розробники можуть ефективно працювати з відсутніми даними та створювати бази даних без помилок. Пам’ятайте, що для оволодіння технікою потрібна практика.

Тепер ви можете подивитися, як створити обмеження зовнішнього ключа в SQL.