Coalesce sql: опис, особливості використання, приклади

При складанні sql-запитів нерідко виникає ситуація, коли при вибірці даних треба порівняти значення декількох стовпців і вивести з них, яке містить дані (не порожнє). Цю завдання відмінно вирішує вираз Coalesce. У даній статті ви знайдете повну характеристику вираження Coalesce sql, опис особливостей використання, а також приклади.

Висновок непустих значень з допомогою Coalesce. Особливості

Розглянемо Sql Coalesce особливості використання: Допускає зазначення будь-якої кількості аргументів (на відміну від Isnull/Nvl/Nvl2 число аргументів в яких обмежена). Може приймати в якості аргументу підпорядкований. Повертає результат, рівний першого відмінному від значенням Null, Null, якщо відмінного від Null значення не буде знайдено. Sql Coalesce може використовуватися в операторі Select для вибору непустого значення, а також у Where для уточнення, що набір стовпців з порожніми значеннями не допускається (/допускається). Дане вираз рівнозначно застосування виразу Case, перевіряючому послідовно кожний аргумент на умову When argument1 is not null then argument1. По суті, Coalesce це «ярлик», створений для зручності використання, і в багатьох СУБД оптимізатори запитів переписують вираз Coalesce на Case. Sql Coalesce функції є у всіх провідних реляційних системах управління базами даних.

Синтаксис Coalesce

Кожен, хто хоч раз застосовував Coalesce при складанні sql-запитів, знає, що синтаксис цього виразу гранично простий. Достатньо в круглих дужках вказати аргументи, перевіряються на Null, через кому. Якщо припустимо, що аргументи мають імена arg1 arg2 argN, то синтаксис Coalesce буде мати наступний вигляд: Coalesce(arg1 arg2 argN). Підготуємо кілька таблиць для вивчення механізму роботи даного виразу.

Підготовка таблиць

Щоб краще зрозуміти Coalesce sql опис, створимо в базі даних дві таблиці, що містять інформацію по об'єктах нерухомості. Перша таблиця Area нехай містить найменування об'єктів нерухомості та їх площу. Площа може бути уточненою (area_yt) або декларованої (area_decl).

id



object_name



area_yt



area_decl



1



Будівля 1



1162



114



2



Об'єкт незавершеного будівництва 1







568



3



Приміщення 1



647







4



Приміщення 2







342



5



Земельна ділянка 1



112



1116



6



Споруда 1











7



Приміщення 3



279







8



Спорудження 2



372



364



9



Будівля 2









Друга таблиця Basic_characteristic нехай містить інформацію з основною характеристикою об'єкта нерухомості – протяжність (Extension), глибина (Depth), площа (Area), обсяг (Scope), висота (Height).

id



object_name



Extension



Depth



Area



Scope



Height



1



Будівля 1











8924







30



2



Будівля 2



















48



3



Споруда 1



1647



















4



Земельна ділянка 1























5



Приміщення 1











236











6



Приміщення 2











347











7



Приміщення 3











198









Розглянули синтаксис Coalesce sql, опис, особливості використання і перейдемо безпосередньо до прикладів.

Приклади використання

Синтаксис у вирази Coalesce гранично простий, проте важливо не забувати, що результатом виконання команди стане ПЕРШЕ непорожнє знайдене значення зі списку аргументів. Це зауваження має дуже велике значення, тому аргументи у виразі необхідно розставляти в порядку їх важливості. Простіше всього зрозуміти принцип по таблиці площ. Складіть запит, що вибирає найменування об'єкта нерухомості, а також значення площі:




SELECT Area.id Area.object_name, coalesce(Area.area_yt, Area.area_decl) FROM Area

Та отримаєте результат:

id



object_name



Coalesce



1



Будівля 1



1162



2



Об'єкт незавершеного будівництва 1



568



3



Приміщення 1



647



4



Приміщення 2



342



5



Земельна ділянка 1



112



6



Споруда 1







7



Приміщення 3



279



8



Спорудження 2



372



9



Будівля 2





По об'єктах «Будівля 1», «Земельна ділянка 1» і «Споруда 2» були заповнені обидва значення площі, але в пріоритеті виявилася площа уточнена, оскільки її ми вказали в списку аргументів першої. Вираз Coalesce знайшло перше непорожнє значення і вивело його, припинивши подальший перегляд аргументів. Дане побудова запиту – вірне, адже уточнена площа більш визначена, ніж декларована. Якщо б ми вказали в якості першого аргументу площа декларовану, то при заповнення цього поля таблиці вона опинилася б у пріоритеті. Крім використання в Select, дуже часто вираз Coalesce застосовується з умовою Where. Воно дозволяє відсікти з результату ті рядки, в яких значення переліку полів порожні (або навпаки, включити в результат тільки ті значення, де перелік полів не заповнений). Дана ситуація зустрічається повсюдно: наприклад, на підприємстві при оформленні нового співробітника в базу даних внесли тільки базові відомості про нього, а заповнення детальної інформації залишили «на потім». Поступово «прогалини» спливають – або перед перевіркою, чи при направленні працівника відпустка/відрядження/лікарняний.
Виберемо з таблиці з основними характеристиками об'єкти нерухомості, у яких не заповнено ні одне із значень характеристик:

SELECT id, object_name FROM Basic_characteristic Where coalesce(Extension, Depth, Area, Scope, Height) is null

За даним запитом в таблиці знайдеться один збіг – об'єкт «Земельну ділянку 1», у якого всі поля з характеристиками порожні:

id



object_name



4



Земельна ділянка 1

Сподіваємося, що наше детальне Coalesce sql опис допомогло вам зрозуміти всі особливості використання даного виразу, а також розібратися з важливими нюансами.