При складанні 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 | 116,2 | 114 |
2 | Об'єкт незавершеного будівництва 1 | 568 | |
3 | приміщення 1 | 64,7 | |
4 | приміщення 2 | 34,2 | |
5 | Земельна ділянка 1 | 112 | 111,6 |
6 | спорудження 1 | ||
7 | приміщення 3 | 27,9 | |
8 | спорудження 2 | 37,2 | 36,4 |
9 | будівля 2 |
Друга таблиця Basic_characteristic нехаймістить інформацію по основній характеристиці об'єкта нерухомості - протяжність (Extension), глибина (Depth), площа (Area), обсяг (Scope), висота (Height).
id | object_name | Extension | Depth | Area | Scope | Height |
1 | будівля 1 | 892,4 | 30 | |||
2 | будівля 2 | 48 | ||||
3 | спорудження 1 | 164,7 | ||||
4 | Земельна ділянка 1 | |||||
5 | приміщення 1 | 23,6 | ||||
6 | приміщення 2 | 34,7 | ||||
7 | приміщення 3 | 19,8 |
Розглянули синтаксис Coalesce sql, опис, особливості використання і перейдемо безпосередньо до прикладів.
приклади використання
Синтаксис у вираження Coalesce гранично простий,однак важливо не забувати, що результатом виконання команди стане ПЕРШЕ непорожнє знайдене значення зі списку аргументів. Це зауваження має дуже велике значення, тому аргументи на вираженні необхідно розставляти в порядку їх важливості. Найпростіше зрозуміти принцип по таблиці площ. Складіть запит, що вибирає найменування об'єкта нерухомості, а також значення площі:
SELECT Area.id, Area.object_name, coalesce (Area.area_yt, Area.area_decl) FROM Area |
І отримаєте результат:
id | object_name | Coalesce |
1 | будівля 1 | 116,2 |
2 | Об'єкт незавершеного будівництва 1 | 568 |
3 | приміщення 1 | 64,7 |
4 | приміщення 2 | 34,2 |
5 | Земельна ділянка 1 | 112 |
6 | спорудження 1 | |
7 | приміщення 3 | 27,9 |
8 | спорудження 2 | 37,2 |
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 опис допомогло вам зрозуміти всі особливості використання цього виразу, а також розібратися з важливими нюансами.
