COALESCE SQL returns the first non-NULL value

SQL COALESCE – a function that returns the first defined, i.e. non-NULL value from its argument list. Usually one or more COALESCE function arguments is the column of the table the query is addressed to. Often a subquery is also an argument for a function. This is done when it is impossible to say with certainty that the subquery will return a certain value, for example, 5, “string”, ‘2018-12-09’, etc., rather than a value of NULL. Then this NULL value will be replaced by the next value immediately after it.

Let’s give simple examples without column names or subqueries.

COALESCE (NULL, 7, 9) // Return 7
COALESCE (NULL, 'Not Found') // Return 'Not Found'
COALESCE ('2017-10-20', NULL, '2018-03-08') // Return '2018-03-08'

COALESCE for easy replacement of NULL value

When creating a database table, you can provide default NULL values for a number of columns. Then, if you insert a new row in such a column without inserting any value, its value will be undefined (NULL). However, when outputting data, an undefined value (it can still be called empty) is not always suitable. In such cases the function COALESCE is used.

In the first examples we work with the database of the library and its table “Book in issue” (BOOKINUSE). The operations will refer to the Author (author of the book) and Title (title of the book) columns.

If you want to execute queries to a database from this lesson on MS SQL Server, but this DBMS is not installed on your computer, you can install it using the instruction on this link.

The script for creating a library database, its tables and filling tables with data – in the file by this link.

Example 1. There is a library database and a BOOKINUSE (Book in Issue) table. The table looks like this:

As you can see, the last line does not contain a certain value of the Author column, since the issued edition is a journal. Let the authors of issued editions with certain inventory numbers be displayed, and none of the fields should be empty. For this purpose, we write a query using the function COALESCE:

SELECT COALESCE (Author, 'Magazine')
AS InUse
FROM Bookinuse
WHERE inv_no IN (25, 81, 127)

For an edition with inventory number 127, the first non-NULL value will be returned – ‘Log’ and the resulting table will look like this:

Information systems almost never allow blank lines as a result of a query. If something that was specified in the query fails, the result line should contain 0 if it is a quantity, or “None” if a text response is required, or another suitable result for the data type.

Example 2. Once again we are working with the BOOKINUSE table of the library database. It is necessary to print the number of publications of a certain author that are in issue. In the table we see that there is one book by Pushkin. Checking. Write the following query using the COALESCE function:

SELECT COALESCE ((SELECT COUNT(*))
FROM Bookinuse
WHERE Author="Pushkin"), 0)
AS InUse

The result of this query:

But among the issued publications there are no books by Bulgakov. Checking. We write a similar request, we only change the author:

SELECT COALESCE ((SELECT COUNT(*))
FROM Bookinuse
WHERE Author='Bulgakov'), 0).
AS InUse

The result of this query:

Thus, the function COALESCE returned the first non-NULL value: 0 and instead of an empty string we got a string with the value 0.

COALESCE for alternative selection

Often, some resultant value is based on the values of different columns of the table, depending on the case. Then, as a rule, the column values that do not take part in forming the resulting value are empty. The function COALESCE is used to select the desired column.

Example 3. The company’s database contains a table STAFF, which can be used to calculate the annual income of an employee.

If the employee receives a fixed salary (Salary), the values of the Comm and Sales columns are empty (NULL). In such a case, the salary should be multiplied by 12 in order to earn annual income.
If an employee receives commissions, the Salary column value is empty (NULL). There may also be cases when an employee has been assigned a commission, but he has not made any transactions. Then the Sales column value is empty (NULL).
In the first case, the COALESCE function returns Salary*12, in the second – Comm*Sales, in the third – 0.
So, to calculate the annual income of employees, we write the following query using the function COALESCE:

SELECT LName,
COALESCE (Salary*12, Comm*Sales, 0)
AS Income
FROM STAFF

The result of the query is the following table:

COALESCE helps avoid computational uncertainty

In table connections, it is often impossible to guess in advance whether all values of a column from one table correspond to a certain value from another table. In case of discrepancy, the value is undefined (NULL). But it is based on this value that additional calculations must be made. Another reason why COALESCE is often used in complex calculations is that it is prohibited to use aggregate functions from an aggregate function, such as SUM(COUNT(*)).

We work with the database “Theatre”. The Play table contains data on productions. Team table – about the roles of actors. The Actor table is about actors. Director’s table is about directors. The table fields, primary and external keys can be seen in the figure below.

COALESCE SQL

Example 4. There is a MainTeam column in the Team table that contains information about whether the role is the main one. If it is, the column value is Y, if it is not, the column value is N. We need a list of actors with names and number of secondary roles.

You’ll need to connect the tables. As we have already noticed, in the combination of the Play (production) and Team (role) tables, some column values may be uncertain because not all actors in each production necessarily have both main and secondary roles. In addition, the sum (SUM) of the number of rows (COUNT(*)) corresponding to a certain actor, which indicates that the role is secondary, must be calculated as the number of secondary roles. However, the use of nested aggregate functions is prohibited. In this case, a query is written using the COALESCE function, the value returned by which is no longer formally a value of the aggregate function:

SELECT a.LName AS Name,
SUM (COALESCE((SELECT COUNT(*))
FROM ACTOR a1
JOIN team t
ON a1.Actor_ID-t.ACTOR_ID
WHERE a1.Actor_ID=a.Actor_ID
AND t.MainTeam='N'.
GROUP BY a1.Actor_ID), 0)) AS NumSecRole
FROM ACTOR a
JOIN team t
ON a.Actor_ID=t.ACTOR_ID
JOIN Play p
ON t.PLAY_ID=p.Play_ID
ORDER BY a.Actor_ID

Coalesce function in sql server