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, and 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.
- Returns the result equal to the first non-Null value, or Null if no non-Null value is found.
- Sql Coalesce can be used in the Select clause to select a non-empty value, as well as in Where to specify that a set of columns with empty values is not allowed (/ allowed).
- This expression is equivalent to using the Case expression, which checks each argument sequentially for the When argument1 is not null then argument1 condition. In essence, Coalesce is a “shortcut” created for ease of use, and in many DBMSs, query optimizers rewrite Coalesce's expression on Case.
- Sql Coalesce functions are available in all leading relational database management systems.
Syntax Coalesce
Anyone who has ever used Coalesce when compiling sql queries knows that the syntax of this expression is extremely simple. It is enough to indicate the arguments to be checked in Null in parentheses, separated by commas. If we assume that the arguments are named arg1, arg2, ... argN, then the Coalesce syntax will look like this:
Coalesce (arg1, arg2, ... argN).
We will prepare several tables for studying the mechanism of operation of this expression.
Table preparation
In order to better understand the Coalesce sql description, we will create two tables in the database containing information on real estate.
Let the first table Area contains the names of real estate and their area. The area can be specified (area_yt) or declared (area_decl).
id | object_name | area_yt | area_decl |
1 | Building 1 | 116.2 | 114 |
2 | Construction in progress 1 | | 568 |
3 | Room 1 | 64.7 | |
4 | Room 2 | | 34.2 |
5 | Land plot 1 | 112 | 111.6 |
6 | Building 1 | | |
7 | Room 3 | 27.9 | |
8 | Building 2 | 37,2 | 36,4 |
9 | Building 2 | | |
Let the second Basic_characteristic table contain information on the main characteristics of the property - extension, depth (Depth), area (Area), volume (Scope), height (Height).
id | object_name | Extension | Depth | Area | Scope | Height |
1 | Building 1 | | | 892.4 | | thirty |
2 | Building 2 | | | | | 48 |
3 | Building 1 | 164.7 | | | | |
4 | Land plot 1 | | | | | |
5 | Room 1 | | | 23.6 | | |
6 | Room 2 | | | 34.7 | | |
7 | Room 3 | | | 19.8 | | |
We examined the Coalesce sql syntax, description, features of use and we will go directly to the examples.
Examples of using
The syntax of the Coalesce expression is extremely simple, but it is important not to forget that the result of the command will be the FIRST nonempty value found from the list of arguments. This remark is very important, therefore, the arguments in the expression must be arranged in order of importance. The easiest way to understand the principle of the area table. Make a request that selects the name of the property, as well as the value of the area:
SELECT Area.id, Area.object_name, coalesce (Area.area_yt, Area.area_decl) FROM Area |
And get the result:
id | object_name | Coalesce |
1 | Building 1 | 116.2 |
2 | Construction in progress 1 | 568 |
3 | Room 1 | 64.7 |
4 | Room 2 | 34.2 |
5 | Land plot 1 | 112 |
6 | Building 1 | |
7 | Room 3 | 27.9 |
8 | Building 2 | 37,2 |
9 | Building 2 | |
For the objects “Building 1”, “Land Plot 1” and “Construction 2” both values of the area were filled, but the area specified was the priority, since we indicated it first in the list of arguments. The Coalesce expression found the first non-empty value and deduced it, stopping further viewing of the arguments. This construction of the request is correct, because the specified area is more defined than declared. If we indicated the declared area as the first argument, then filling this field of the table would have priority.
Besides being used in Select, very often the Coalesce expression is used with the Where clause. It allows you to cut off from the result those rows in which the values of the list of fields are empty (or vice versa, include only those values where the list of fields is not filled in the result). This situation is common: for example, at the enterprise when registering a new employee, only basic information about him was entered into the database, and filling in the detailed information was left “for later”. Gradually, “gaps” pop up - either before the check, or when the employee is sent on vacation / business trip / sick leave.
We select from the table with the main characteristics real estate objects for which none of the characteristic values is filled:
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.