Допоможіть розробці сайту, ділитися статтею з друзями!
Вступ до SQL COALESCE
COALESCE - це попередньо визначена вбудована функція мови структурованих запитів (SQL), яка використовується для обробки значень NULL у записах даних. Він послідовно оцінює набір або список вхідних параметрів і повертає серед них перші значення, відмінні від NULL. Функція COALESCE завершується, коли зустріне перше значення, відмінне від NULL, і повертає його. Якщо всі аргументи у списку введення є значеннями NULL, то функція поверне NULL.
Це загальна функція, яка підтримується в усіх базах даних, як-от MYSQL, SQL Server (починаючи з 2008 року), база даних Azure SQL, PostgreSQL, Oracle тощо.
Деякі види використання функції SQL COALESCE:
- Обробка значень NULL
- Виконання двох або більше запитів як одного запиту
- Ярлик альтернатива тривалим і трудомістким операторам CASE
Синтаксис і параметри
Основний синтаксис використання функції COALESCE в SQL такий:
SELECT COALESCE(значення_1, значення_2, значення_3, значення_4,… значення_n);
Параметри, згадані в синтаксисі вище:
COALESCE() : функція SQL, яка повертає перше ненульове значення зі списку введення.
value_1, value_2,value_3,value_4,… value_n : вхідні значення, які необхідно оцінити.
Оскільки функція COALESCE є виразом сама по собі, її можна використовувати в будь-якому операторі або речення, що приймає такі вирази, як SELECT, WHERE і HAVING.
функцію COALESCE можна вважати спеціалізованою версією оператора CASE в SQL.
Синтаксис оператора CASE, що демонструє функцію COALESCE, такий:
SELECT column_name1, Column_name2, column_name3,FROM table_name;
КЕЙС №
КОЛИ ім'я_столбця1 НЕ НУЛЕВИЙ, ТОІМ_стовпця1
КОЛИ column_name2 НЕ NULL THEN column_name2 ELSE NULL
END як 'case_name'
Параметри, згадані в синтаксисі вище:
Column_name1, column_name2: значення стовпців, які мають об’єднатися, тобто якщо результати будь-якого з цих стовпців призводять до значень NULL, тоді буде повернуто перше значення, відмінне від NULL. Але якщо обидва вони оцінюються як NULL, то CASE поверне NULL.
Продовжуючи, ми детально обговоримо вищезгадану функцію COALESCE.
Щоб краще зрозуміти концепцію, ми скористаємося таблицею співробітників (в ній містяться особисті дані всіх співробітників).
Дані в таблиці "співробітники" такі:
Приклади SQL COALESCE
Ось кілька прикладів, щоб краще зрозуміти SQL COALESCE.
Приклад №1
запит SQL для ілюстрації використання простої функції COALESCE.
Код:
SELECT COALESCE(NULL, NULL, NULL, 'EduCBA', NULL, NULL);
Вихід:
У цьому прикладі ми бачимо, що функція COALESCE вибирає перше значення, що не є NULL, воно зустрічається, а потім закінчується.
Приклад 2
запит SQL, щоб знайти перші ненульові значення з імені та прізвища співробітника.
Код:
ВИБЕРІТЬ ідентифікатор співробітника,ім'я, прізвище,ВІД співробітників;
COALESCE(ім'я,прізвище) як "ім'я, яке не є нульовим"
Вихід:
У цьому прикладі повертається перший не NULL.
Приклад №3
запит SQL для ілюстрації функції COALESCE як окремий випадок CASE.
Код:
ВИБЕРІТЬ ідентифікатор співробітника,ім'я, прізвище,ВІД співробітників;
КЕЙС №
КОЛИ ім'я НЕ НУЛЕ, ТО ім'я
КОЛИ прізвище НЕ НУЛЕВИЙ, ТО ПІДЖИЩЕ ІНШЕ NULL
END як "перше ненульове значення"
Вихід:
Порівняйте результати цього запиту та результату запиту в третьому прикладі. Ми бачимо, що обидва вони дають однаковий результат. Це тому, що функцію COALESCE можна розглядати як окремий випадок CASE.
Приклад №4
запит SQL для ілюстрації використання функції COALESCE для значень з різними типами даних.
Код:
ВИБЕРІТЬ ідентифікатор співробітника,ім'я, прізвище,ВІД співробітників;
COALESCE(employeeid,ім'я,прізвище) як "ім'я, яке не є нульовим"
Вихід:
У наведеному вище прикладі ми бачимо, що ідентифікатор співробітника не може бути поміщений із значеннями даних імені та прізвища, оскільки вони мають різні типи даних.
Щоб вирішити цю проблему, ми можемо змінити тип даних Employeeid за допомогою функції CONVERT, як показано нижче.
Код:
ВИБЕРІТЬ ідентифікатор співробітника,ім'я, прізвище,ВІД співробітників;
COALESCE(CONVERT(varchar(50),employeeid),ім'я,прізвище) як "перші ненульові значення"
Вихід:
Приклад №5
Складний запит SQL для ілюстрації використання функції COALESCE.
Припустимо, ми хочемо знати зарплати всіх співробітників фірми. Але в таблиці співробітників ми бачимо, що не вказано зарплату всіх працівників. Проте згадуються погодинні ставки та комісійні працівників із відсутніми полями зарплати. Ми можемо використовувати це для порівняння зарплат співробітників у такий спосіб.
Код:
ВИБЕРІТЬ COALESCE(CONVERT(varchar(50),employeeid),ім'я,прізвище) як "Ідентифікатор співробітника",Упорядкувати за 2 СПИСОК;
COALESCE(зарплата,погодинна_ставка830 + COALESCE(комісія,0))як "компенсація"
ВІД співробітників
Вихід:
Використовуючи першу функцію COALESCE, ми спробували ідентифікувати співробітника, об'єднавши ідентифікатор співробітника, ім'я та прізвище разом, а потім за допомогою другої функції COALESCE ми спробували вибрати зарплату або розраховану компенсацію.
Висновок
Функція SQL COALESCE - це вбудована функція в таких базах даних, як SQL Server, Azure тощо, яка повертає перше значення, відмінне від NULL, зі списку вхідних аргументів. Це дуже корисно для обробки нульових значень і служить швидкою та легкою альтернативою операторам SQL CASE.