When compiling sql queries, a situation often arises when, when fetching data, you need to compare the values of several columns and display the one that contains the data (not empty). This task is perfectly solved by Coalesce expression. In this article you will find a full description of the Coalesce sql expression, a description of the usage features, as well as examples.
Output non-empty values using Coalesce. Features
Consider Sql Coalesce usage features:
- It allows specifying any number of arguments (unlike Isnull / Nvl / Nvl2, which has a limited number of arguments).
- May take subqueries as an argument.
- , 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 |
For this query, there is one match in the table - the “Land 1” object, in which all fields with characteristics are empty:
id | object_name |
4 | Land plot 1 |
We hope that our detailed Coalesce sql description helped you understand all the features of using this expression, as well as deal with important nuances.