Coalesce sql: description, usage, examples

When compiling sql-queries, it often happens that when you select data, you need to compare the values of several columns and output one of them that contains data (not empty). This problem is perfectly solved by the expression Coalesce. In this article you will find a full description of the expression Coalesce sql, a description of the features of use, as well as examples.

Output of non-null values using Coalesce. Features

Consider the Sql Coalesce features of use:

1. Allows specifying any number of arguments (in contrast to Isnull / Nvl / Nvl2, the number of arguments in which is limited).
2. Can accept subqueries as an argument.
3. Returns the result equal to the first non-Null value, or Null, if no value is found.
4. Sql Coalesce can be used in the Select clause to select a non-empty value, and also in Where to specify that a set of columns with empty values is not allowed (/ allowed).
5. This expression is equivalent to applying the Case expression, checking each argument successively for the condition When argument1 is not null then argument1. In fact, Coalesce is a "shortcut" created for ease of use, and in many DBMS query optimizers rewrite the Coalesce expression on Case.
6. Sql Coalesce features are available in all leading relational database management systems.

Syntax Coalesce

Anyone who has ever used Coalesce to compile sql queries knows that the syntax of this expression is very simple. It is enough to specify in parentheses arguments that are checked on Null, separated by commas. If we assume that the arguments have the names 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 this expression.

Preparing tables

To better understand Coalesce sql description, we will create in the database two tables containing information on real estate objects.

The first Area table should contain the names of real estate objects and their area. The area can be specified (area_yt) or declared (area_decl).

The second Basic_characteristic table should contain information on the main characteristic of the real estate object - Extension, Depth, Area, Scope, Height.

We examined the syntax of Coalesce sql, the description, the features of use, and proceed directly to the examples.

Examples of using

The syntax of the Coalesce expression is extremely simple, but it's important to remember that the result of the command will be the FIRST non-empty value found from the argument list. This remark is very important, so the arguments in the expression must be placed 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 area value:

And get the result:

For the objects "Building 1", "Land plot 1" and "Building 2", both area values were filled, but in the priority was the area refined, since we indicated it in the list of arguments first. The expression Coalesce found the first non-empty value and withdrew it, stopping further viewing of the arguments. This construction of the request is correct, because the specified area is more specific than the declared one. If we specify the area declared as the first argument, then if this table field is full, it would be in priority.

In addition to using in Select, very often the Coalesce expression is applied with the Where clause. It allows you to cut off from the result those lines in which the list of fields is empty (or vice versa, include only those values in the result where the list of fields is not filled). This situation is everywhere: for example, at the time of registration of a new employee, only basic information about the employee was entered in the database, and the detailed information was left "for later". Gradually, the "gaps" float up - either before the check, or when sending the employee on vacation / business trip / sick leave.

Choose from the table with the main characteristics of real estate objects, which do not fill any of the values of the characteristics:

For this query, there is one match in the table - the "Land plot 1" object, in which all fields with characteristics are empty:

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.