SQL COALESCE function returns the first non-null value from the expression list specified by the user.

Description

In SQL, there are some functions to handle null values and missing data in the result sets. COALESCE is one such function. It evaluates the input list given by the user and returns the first non-null value.

SQL COALESCE Usage Notes

  • The first argument in the SQL COALESCE function is the argument list containing multiple values.
  • All the argument values in the input expression are of the same data type.
  • The function returns the data type of highest precedence. For example, in the case of integer and varchar, the COALESCE function returns integer data type.
  • If all values in the input expression are non-nullable, then the COALESCE function returns a non-nullable data type.
  • If all values in the argument list are null, SQL COALESCE function returns a null.

Syntax

COALESCE ( input_expression [ …n ])

SQL COALESCE Examples

Let’s see how the SQL COALESCE function works with a simple example.

SELECT COALESCE(NULL, 1, 2, 'SQLQUERY') CoalesceResult;
/* Answer */
SELECT COALESCE(NULL, 1, 2, 'SQLQUERY') CoalesceResult;

The above query returns 1 as the first non-null value. However, if we replace positions of ‘SQLQUERY’ and 1, we get an error. It is because we have two data types of Int and varchar. As Int has higher precedence, CAST will try to return an int data type, but varchar can’t be converted into an int value.

If all the argument values in the expression list are varchar, the query works fine, as shown below.

SELECT COALESCE(NULL, NULL, 'SQLQUERY', 'WORKS', 'FINE') CoalesceResult;
/* Answer */
SELECT COALESCE(NULL, NULL, 'SQLQUERY', 'WORKS', 'FINE') CoalesceResult;

The above query returns SQLQUERY as the first non-null value in the argument list.

Next, we will apply SQL COALESCE function on the Adventureworks2019 database.

The following SQL query selects Territory ID, Sales Quota, Bonus, and Commission Percentage columns from the SalesPerson table. COALESCE function selects the first non-null value from column list of Sales Quota, Bonus, and Commission Percentage.

SELECT TerritoryID, SalesQuota, Bonus, CommissionPct, COALESCE(SalesQuota, Bonus, CommissionPct) AS FirstNotNull FROM Sales.SalesPerson;
/* Answer */
SELECT TerritoryID, SalesQuota, Bonus, CommissionPct,  
COALESCE(SalesQuota, Bonus, CommissionPct) AS FirstNotNull  
FROM Sales.SalesPerson;

Columns passed as arguments to the SQL COALESCE function have the same data type of money.

We can also handle null values in our resultant rows using COALESCE function for easy readability.

The following query returns all rows in the Address table.

SELECT * FROM Person.Address
/* Answer */
SELECT * FROM Person.Address

We can see that the column AddressLine2 contains a lot of NULL values. If we concatenate AddressLine1, AddressLine2, and city to get address details for each AddressId, we get a NULL value. It is because the SQL server returns a NULL if there is a null value for any column while performing concatenation.

SELECT AddressLine1 + ' ' + AddressLine2 + ' ' + City AS AddressDetail FROM Person.Address
/* Answer */
SELECT AddressLine1 + ' ' + AddressLine2 + ' ' + City AS AddressDetail
FROM Person.Address

We can avoid these NULL values using SQL COALESCE function. The following example demonstrates how we can replace null values in the column with a blank space.

SELECT AddressLine1 + ' ' + COALESCE(AddressLine2,'') +' '+ City AS AddressDetail FROM Person.Address
/* Answer */
SELECT AddressLine1 + ' ' + COALESCE(AddressLine2,'') +' '+ City AS AddressDetail
FROM Person.Address

The above query replaces a NULL value with a blank space. In this way, we can get all the address details as a concatenated string despite NULL values.

See Also

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>