SQL Server COALESCE

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the COALESCE function to find the first non-null value in a list containing both NULL and valid values.

What is COALESCE in SQL Server?

COALESCE is a function which takes a number of expressions as an argument and evaluates them one by one till it comes at a non-null value. Once it does it escapes immediately and returns that value as the output. The non-null value can be either numeric or string. Since COALESCE is a function that returns a unique value it can be used as an expression with clauses like SELECT, WHERE, GROUP BY, etc.

An important point to note and keep in mind is that the datatypes of the expressions passed as arguments with COALESCE should be either same or numeric expressions should precede string. Otherwise the query fails and returns a casting error. This is because COALESCE always processes numeric expressions (i.e. numbers) first.

Operation

The following shows the different operating cases of the COALESCE function.

1. SELECT COALESCE (NULL,'A',' test string');
2. SELECT COALESCE (NULL, 100, 20, 30, 40);
3. SELECT COALESCE (NULL, NULL, NULL, NULL,1,'test string');
4. SELECT COALESCE (NULL, NULL, NULL, NULL, NULL, ‘test string', 1);
  1. It will return the first non-null character string A.
  2. It will return the first non-null numeric value 100.
  3. It will return the first non-null numeric value 1.
  4. It will throw a casting error since string precedes number and the COALESCE logic fails.

There are quite a few practical use cases of the COALESCE function of which the most common are string concatenation, pivoting and column value validation and consolidation.

Syntax

The basic syntax of SQL Server COALESCE statement is given below.

SELECT COALESCE (expression list);  

In this syntax,

  • COALESCE() – function name which takes multiple expressions as arguments looking for and returning the first non-null value.
  • expression – a literal (numeric or string) value or anything (including table column, parameter, variable, subqueries, mathematical operation, function etc.) that returns a single numeric or string value.

SQL Server COALESCE Examples

We will understand the above-mentioned use cases through the following examples.

1)  SQL Server COALESCE – string concatenation example

Sometimes a single piece of information is spread across multiple columns in a table. The most ubiquitous example is that of a person’s name which consists of multiple parts (in multiple columns) all of which do not exist for all names. But when presenting the information for display and viewing it has to be presented as a single unit i.e. the name of a person. The same is usually achieved through string concatenation. However, it does not work if some columns (like middle name, husbands last name etc.) do not contain any value or contains NULL value. Concatenating the different parts of the name for such names produces NULL as the result because the concatenation process breaks at missing or NULL values. The COALESCE function comes to the rescue here by ensuring that the concatenation does not break.  

To understand the scenario let us consider the below sample customer table containing customer names and contact numbers.

first_namemiddle_namelast_namework_phonepers_phonehome_phone
ArupRoyChoudhuryNULLNULLNULL
ParthibanNULLThyagiNULLNULL080-66337199
MuniraKhanPathan9999112393NULLNULL
JaydevNULLMalik9441377778NULLNULL
VikasNULLMalhotraNULLNULL011-227977790
BNSinghNULL8814773537NULL
RashmiJainAgarwalNULLNULLNULL
Table Name:  customer

We can try to concatenate the different name fields using the following query.

SELECT first_name +' ' + middle_name + ' ' + last_name 'Customer Name'
FROM customer;

The query will generate the following output from which we can see that it has returned NULL as the name for names which do not have a middle name.

Customer Name
Arup Roy Choudhury
NULL
Munira Khan Pathan
NULL
NULL
B N Singh
Rashmi Jain Agarwal

 To get around this we can run the following query using the COALESCE function to get the desired output.

SELECT first_name +' ' + COALESCE (middle_name, '') + ' ' + last_name
AS Customer Name'
FROM customer;

This will generate the following output which contains complete names of all customers in the table.

Customer Name
Arup Roy Choudhury
Parthiban  Thyagi
Munira Khan Pathan
Jaydev  Malik
Vikas  Malhotra
B N Singh
Rashmi Jain Agarwal

Explanation: In the query we have passed 2 expressions to the COALESCE function – first the middle_name column and then a blank space. For columns where the middle_name exist, the same is returned and for NULL value columns COALESCE (NULL, ‘’) returns empty i.e. a blank space which is concatenated with the rest of the string and the complete name returned in the resultset.

2) SQL Server COALESCE – validation and consolidation example

A variation to the above scenario is a scenario where a piece of information exists in any one of the multiple columns and the all the columns have to checked and the value picked up and returned in the resultset from the appropriate column. If we refer the customer table above, we can see the phone number columns present such a scenario. To check all the columns and fetch the appropriate phone number from where it exists we can use the COALESCE function as done in the following query.

SELECT first_name +' ' + COALESCE (middle_name, '') + ' ' + last_name
AS 'Customer Name',
COALESCE (work_phone, pers_phone, home_phone, 'Not Available')
AS 'Customer Phone'
FROM customer;

The query will generate the following output which lists the applicable phone number against a customer where it is available in any of the columns and ‘Not Available’ where none exists.

Customer NameCustomer Phone
Arup Roy ChoudhuryNot Available
Parthiban  Thyagi080-66337199
Munira Khan Pathan9999112393
Jaydev  Malik9441377778
Vikas  Malhotra011-227977790
B N Singh8814773537
Rashmi Jain AgarwalNot Available

Explanation: Let us ignore the first case of COALESCE in the query which has been discussed above. In the second COALESCE case relevant here we have passed 4 arguments – the work_phone, per_phone and home_phone columns and a literal string ‘Not Available’. If any of the customer phone numbers exist COALESCE returns the same. Otherwise the literal string ‘Not Available’ becomes the first non-null value (since all the phone number columns are null) and is returned as the output. Hence any available phone number or ‘Not Available’ is displayed as ‘Customer Phone’ in the resultset.

Advertisement