При составлении 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 описание помогло вам понять все особенности использования данного выражения, а также разобраться с важными нюансами.
