It is common to encounter missing (NULL) values in a table. In real-time, dealing with NULL values in tables is very challenging, especially when performing aggregations, comparisons, or mathematical calculations. The SQL COALESCE function is one of the few options for handling NULL values. It provides a fallback or default value when it encounters NULL values. This article demonstrates how to utilize the COALESCE function, including its syntax, benefits, and handling of table nulls with an example.
The SQL Server COALESCE function returns the first NOT NULL value from the series of expressions. It allows you to replace the NULL value with alternative non-empty values. The COALESCE function accepts multiple expressions as the input parameter and evaluates them in the order they are provided (left to right). It simply returns the first NOT NULL value, and if all the expressions are NULL, it returns NULL as the output.
SQL COALESCE Syntax
The basic syntax of the COALESCE function for handling NULL values is as follows.
COALESCE (expression1, expression2, ......, expressionN)
All the expressions within the SQL COALESCE function should be of the same data type. It checks the arguments for non-NULL values from left to right. Next, it returns the first NOT NULL value as the output. However, if all values are NULL, the function will return NULL as the output.
NULLs in Integer or Numeric values
This section uses multiple examples to show the working functionality of the SQL COALESCE function on integer or numeric fields.
This example has no null values. So, it returns the first value, 10, as the output.
SELECT COALESCE(10, 20)
10The process starts from left to right, and the first value is NULL. So, the COALESCE function returns the second value, 40, because it is the first non-null value.
SELECT COALESCE(NULL, 40, 50, 60)
40Here, we mentioned NULL values in the first, second, and fourth positions. So, when the server reaches the third position, it returns 4 as the output. It won’t go for 7.
SELECT COALESCE(NULL, NULL, 4, NULL, 7)
4It is another example of the COALESCE function with numeric values.
SELECT COALESCE(NULL, NULL, NULL, 95, 105)
95Handle NULL values in the String text
In this section, we use the SQL COALESCE function on string data. The first statement returns Apple as the output because this function will return the first non-empty value.
SELECT COALESCE('Apple', 'Orange', 'Kiwi', NULL)
AppleThe second SQL Server statement returns Orange as the Output. Because the first one is Null, the function always returns the first non-empty value.
SELECT COALESCE(NULL, 'Orange', 'Kiwi', 'Cherry')
OrangeHere, the third value is Kiwi, the fifth one is Cherry, and all remaining ones are NULL values. Although there are two non-null values, it will return Kiwi as the output because it is the first one that comes from left to right.
SELECT COALESCE(NULL, NULL, 'Kiwi', NULL, 'Cherry')
KiwiSimilarly, the following statement returns Cherry as the output.
SELECT COALESCE(NULL, NULL, NULL, 'Cherry')
CherrySQL COALESCE Function on Phone Numbers
How to write this COALESCE function on a table with a practical example that you might see in real-time. For this demonstration, we use the [Emp] table. The following screenshot shows you the data inside the Emp table. As you can see, it has 15 records.
In this example, we will use the SQL COALESCE function to find out the contact number of each employee:
- If an employee has an Office number, the function returns the office number.
- If an employee does not have an Office number but has a Mobile, this function displays the Mobile.
- And, if our employee in the Emp table does not have an Office or Mobile number but has a Home phone, it displays the Home number.
- If an employee has an Office, Mobile, and Home, then it will return the first non-empty, i.e., office number.
SELECT [Id],[Name],[Education],[Occupation],[YearlyIncome]
,COALESCE ([Office Phone], [Mobile], [Home Phone]) AS PHONE
,[Office Phone]
,[Mobile]
,[Home Phone]
FROM [Emp]
SQL COALESCE Function on Table Columns
In the two sections that we mentioned earlier, we have directly used both NULL and NOT NULL values as the arguments. In the coming sections, we use the COALESCE function on table columns. For this, we use the table below.
| ID | FirstName | LastName | Department | Income | Bonus | Country | status |
|---|---|---|---|---|---|---|---|
| 1 | John | Ericson | Sales | 80000.00 | 5000.00 | USA | active |
| 2 | Bob | NULL | Sales | 79000.00 | NULL | NULL | NULL |
| 3 | Ram | Krishna | NULL | 110000.00 | 7000.00 | India | active |
| 4 | David | NULL | HR | 95000.00 | NULL | NULL | inactive |
| 5 | NULL | Smith | NULL | 64000.00 | NULL | NULL | NULL |
| 6 | Miller | NULL | Admin | 135000.00 | 10000.00 | NULL | active |
As you can see from the above table, there are many NULL values in almost all columns. We start the section with a simple example.
Within the employee table, the LastName for employee ID numbers 2, 4, and 6 is NULL. The query below uses the SQL COALESCE function on the LastName column to replace NULL values with TG.
SELECT ID, COALESCE(LastName, 'TG') AS LastName
FROM [employees]
ID LastName
1 Ericson
2 TG
3 Krishna
4 TG
5 Smith
6 TGSQL COALESCE Function in Math Calculations
When we perform arithmetic operations on numeric values, if any of the values are NULL, the entire calculation becomes NULL. As we have already explained the problems with NULL values in mathematical calculations, please refer to the Arithmetic Operators article.
The query below performs arithmetic addition on income and bonus to find the total payment paid to each employee.
SELECT ID, Income + Bonus AS Salary
FROM employees
ID Salary
1 85000.00
2 NULL
3 117000.00
4 NULL
5 NULL
6 145000.00As you can see, there are three NULL values in the result set because adding any value to NULL becomes NULL. To deal with this, we must utilize the COALESCE function to replace NULL values with zero. The query below returns the actual result.
SELECT ID, Income + COALESCE(Bonus, 0) AS Salary
FROM employees
ID Salary
1 85000.00
2 79000.00
3 117000.00
4 95000.00
5 64000.00
6 145000.00Using calculated values
Instead of using a standard value like 100 or zero as the SQL COALESCE function’s second argument (default value), we can use any calculated value. For example, the query below calculates the Bonus on the fly for each row based on the Income. Next, the COALESCE function replaces NULL values with the 5% income value.
SELECT ID, Income,
COALESCE(Bonus, Income * 0.05) AS Bonus
FROM employees
ID Income Bonus
1 80000.00 5000.0000
2 79000.00 3950.0000
3 110000.00 7000.0000
4 95000.00 4750.0000
5 64000.00 3200.0000
6 135000.00 10000.0000Sorting the Data by the COALESCE Function
The ORDER BY clause is placed last in the execution process. So, we can use the SQL COALESCE function result in the ORDER BY clause to sort the result set in ascending or descending order. The query below sorts the employees by their salary in descending order.
SELECT ID, FirstName, LastName,
Income + COALESCE(Bonus,0) AS Salary
FROM employees
ORDER BY Salary DESC
-- ORDER BY COALESCE(Bonus,0) DESC
ID FirstName LastName Salary
6 Miller NULL 145000.00
3 Ram Krishna 117000.00
4 David NULL 95000.00
1 John Ericson 85000.00
2 Bob NULL 79000.00
5 NULL Smith 64000.00SQL COALESCE function in String concatenation
In SQL Server, there are two approaches for performing string concatenation; this section covers handling NULL values in these two methods. The query below joins two columns using the + operator.
SELECT ID, FirstName + ' ' + LastName AS FullName
FROM employees;
ID FullName
1 John Ericson
2 NULL
3 Ram Krishna
4 NULL
5 NULL
6 NULLIf you observe the result set, there are four rows filled with NULL values because either the FirstName or the LastName column has a NULL value for those records. To handle the situation, we must use the SQL COALESCE function on both columns. Remember, if there are no NULL values in either FirstName or LastName, remove the COALESCE function on that column.
SELECT ID,
COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '') AS FullName
FROM employees;
ID FullName
1 John Ericson
2 Bob
3 Ram Krishna
4 David
5 Smith
6 Miller If you want the AdventureWorksDW database example, use the query below.
SELECT TOP 5 [FirstName] +' ' +
COALESCE([MiddleName], '') + ' '+ [LastName] AS Name
,[MaritalStatus],[Gender],[YearlyIncome]
FROM [DimCustomer]
Name MaritalStatus Gender YearlyIncome
Jon V Yang M M 90000.00
Eugene L Huang S M 60000.00
Ruben Torres M M 60000.00
Christy Zhu S F 70000.00
Elizabeth Johnson S F 80000.00There is a built-in CONCAT function for performing string concatenation and handling NULL values at the same time. The following query ignores the NULL values in both columns and returns the output.
SELECT ID,
CONCAT(FirstName, ' ', LastName) AS FullName
FROM employees;
The above statement is helpful when we want to ignore the NULL values. However, to replace the NULL values with meaningful information, we must use the COALESCE function. The SQL COALESCE functions in the following query find the NULL values and replace the FirstName NULL value with Tutorial and the LastName NULLs with Gateway.
SELECT ID,
CONCAT(COALESCE(FirstName, 'Tutorial'), ' ',
COALESCE(LastName, 'Gateway')) AS FullName
FROM employees;
ID FullName
1 John Ericson
2 Bob Gateway
3 Ram Krishna
4 David Gateway
5 Tutorial Smith
6 Miller GatewaySQL COALESCE Function in the WHERE clause
If we want to view employees’ information who are active or inactive, the WHERE clause will help to filter them.
As there are two NULL values in the status column, if you specify status as ‘inactive’, it returns one column, and ‘active’ returns three columns.
SELECT * FROM employees
WHERE status = 'inactive';
ID FirstName LastName Department Income Bonus Country status
4 David NULL HR 95000.00 NULL NULL inactiveIn most cases, we might consider the NULL value as inactive. In such a scenario, use the COALESCE function to replace the NULLs in the status column with ‘inactive’. The following query returns three columns, one is ‘inactive,’ and the other two with a NULL status.
SELECT * FROM employees
WHERE COALESCE(status, 'inactive') = 'inactive';
ID FirstName LastName Department Income Bonus Country status
2 Bob NULL Sales 79000.00 NULL NULL NULL
4 David NULL HR 95000.00 NULL NULL inactive
5 NULL Smith NULL 64000.00 NULL NULL NULLSQL COALESCE function Vs CASE Statement
When dealing with NULL values, we can call the COALESCE function as a shortcut or a more straightforward approach than the CASE statement.
To demonstrate the CASE statement against the NULL values, we use the LastName column from our employees table. The CASE statement uses the IS NULL function to find NULL values in the LastName column and replaces them with N/A.
SELECT ID, FirstName, LastName,
CASE
WHEN LastName IS NULL THEN 'N/A'
ELSE LastName
END AS LastName
FROM employees;
ID FirstName LastName LastName
1 John Ericson Ericson
2 Bob NULL N/A
3 Ram Krishna Krishna
4 David NULL N/A
5 NULL Smith Smith
6 Miller NULL N/AAs you can see from the result set below, it returns the same result as the COALESCE function that we mentioned in the Table columns example.
To demonstrate the CASE statement on multiple columns of NULLs, we use the famous Phone Numbers example that we mentioned earlier. We can write the query below as a replacement for the example specified above.
SELECT [Id],[Name],[Education],[Occupation],[YearlyIncome]
, CASE
WHEN [Office Phone] IS NOT NULL THEN [Office Phone]
WHEN [Mobile] IS NOT NULL THEN [Mobile]
WHEN [Home Phone] IS NOT NULL THEN [Home Phone]
ELSE 'No Information'
END AS PHONE
FROM [Emp]
Apart from using the CASE statement as an alternative to the SQL COALESCE function, we can use the combination of both.
The following query uses the COALESCE function within the CASE statement to replace the NULL values in the Bonus with 0. Next, the condition checks whether the Bonus value is greater than 5000. If TRUE, return High as the Bonus information; otherwise, print Normal.
SELECT ID, Income, Bonus,
CASE
WHEN COALESCE(Bonus, 0) > 5000 THEN 'High'
ELSE 'Normal'
END AS BonusInfo
FROM employees;
ID Income Bonus BonusInfo
1 80000.00 5000.00 Normal
2 79000.00 NULL Normal
3 110000.00 7000.00 High
4 95000.00 NULL Normal
5 64000.00 NULL Normal
6 135000.00 10000.00 HighUsing the SQL COALESCE function in the GROUP BY Clause
When grouping column rows, the GROUP BY clause considers NULL values as another group and displays the result set with one NULL value. However, SQL Server allows the COALESCE function inside the GROUP BY clause to provide a unique name or replace NULL with a meaningful name.
For instance, the statement below returns three records. As there are four NULL values in the Country column, it groups them as one and prints one NULL as output.
SELECT Country, COUNT(*) AS employees,
SUM(Income) AS Salaries
FROM employees
GROUP BY Country;
Country employees Salaries
NULL 4 373000.00
India 1 110000.00
USA 1 80000.00If we use the COALESCE function, we can replace that NULL value with more meaningful information. The query below replaces the NULL value with Europe. Remember, we must use the COALESCE function in the SELECT statement and the GROUP BY clause.
SELECT COALESCE(Country, 'Europe') AS Country,
COUNT(*) AS employees,
SUM(Income) AS Salaries
FROM employees
GROUP BY COALESCE(Country, 'Europe');
Country employees Salaries
Europe 4 373000.00
India 1 110000.00
USA 1 80000.00In the above GROUP BY example, we haven’t used the COALESCE function within the aggregate function (SUM) because there are no NULL values in the Income column. However, if there are any NULL values, it will return NULL as the output.
Using the SQL COALESCE function in the HAVING clause
In our employees table, there are three distinct departments: Sales (2), HR, and Admin. Apart from them, there are two NULL values, and for ID number 3, the Bonus value is 7000. The query below uses the COALESCE function in the HAVING clause to find that the total bonus of each group is greater than 1000.
SELECT COALESCE(Department, 'Marketing') AS Department,
SUM(COALESCE(Bonus,0)) AS [Total Bonus]
FROM employees
GROUP BY COALESCE(Department,'Marketing')
HAVING SUM(COALESCE(Bonus,0)) > 1000;
-- HAVING SUM(Bonus) IS NULL;
Department Total Bonus
Admin 10000.00
Marketing 7000.00
Sales 5000.00SQL COALESCE Function with ROLLUP clause
By default, the GROUP BY ROLLUP generates the subtotals and grand totals based on the groups and subgroups. If there is one grouping column, it generates a grand total; for two grouping columns, it generates subtotals too.
To demonstrate the COALESCE function with the GROUP BY ROOLUP example, we use the FactInternetSales tables from the AdventureWorksDW database.
By default, the ROLLUP will return NULL as the grouping value for the grand total (last row). Here, the SQL COALESCE function replaces that last row’s NULL value with ‘Grand Total’. As the message is a VARCHAR, and the YEAR function returns an integer value, we used the CAST function to convert the year value to a string.
SELECT COALESCE(
CAST(YEAR(f.OrderDate) AS VARCHAR(10)),
'Grand Total') AS Year,
SUM(f.SalesAmount) AS TotalSales
FROM FactInternetSales AS f
GROUP BY ROLLUP(YEAR(f.OrderDate))
Year TotalSales
2010 43421.0364
2011 7075525.9291
2012 5842485.1952
2013 16351550.34
2014 45694.72
Grand Total 29358677.2207SQL COALESCE function on Multiple tables
We can use the COALESCE function in the ON clause to perform JOIN operations on multiple tables. If there are any NULL values, when joining, use the COALESCE function on common columns with any default value. Remember, it will drop the existing indexes, and it is a very bad practice to use COALESCE on JOINs. However, the COALESCE function helps work on columns from multiple tables.
The following query uses the INNER JOIN to join the DimCustomer and the DimGeography tables based on the common GeographyKey. First, the COALESCE function replaces the NULL values in the MiddleName. Next, we used the COALESCE function inside the CONCAT function to join columns from both tables.
TIP: If there are any NULL values in GeographyKey, use the COALESCE in the ON clause and replace those NULL values with any valid numeric value.
SELECT TOP 7 c.FirstName, c.MiddleName,
COALESCE(c.MiddleName, 'TG') AS NewMiddleName,
c.LastName, g.City,
CONCAT(c.FirstName, ' ',
COALESCE(c.MiddleName, 'TG'),' ', c.LastName, ' From ', g.City) AS Name
FROM DimCustomer AS c LEFT JOIN DimGeography AS g
ON c.GeographyKey = g.GeographyKey
FirstName MiddleName NewMiddleName LastName City Name
Jon V V Yang Rockhampton Jon V Yang From Rockhampton
Eugene L L Huang Seaford Eugene L Huang From Seaford
Ruben NULL TG Torres Hobart Ruben TG Torres From Hobart
Christy NULL TG Zhu North Ryde Christy TG Zhu From North Ryde
Elizabeth NULL TG Johnson Wollongong Elizabeth TG Johnson From Wollongong
Julio NULL TG Ruiz East Brisbane Julio TG Ruiz From East Brisbane
Janet G G Alvarez Matraville Janet G Alvarez From MatravilleNested COALESCE Functions
SQL Server allows using a nested COALESCE function (one inside the other). However, it is not recommended and rather useless to use the nested COALESCE Functions.
To demonstrate the same, we use the Phone Number example. Here, we used two COALESCE functions nested one inside the other. If the Office phone is NULL, it checks the Mobile phone number; if not found, the query returns Unknown.
SELECT [Id]
, COALESCE ([Office Phone], COALESCE([Mobile], 'Unknown')) AS PHONE
FROM [Emp]
Instead of using the Nested, we can use a single COALSECE function, and it does the same job as the above.
SELECT [Id]
, COALESCE ([Office Phone], [Mobile], [Home Phone], 'Unknown') AS PHONE
FROM [Emp]
Data type Error
By default, the SQL COALESCE function can handle various data types, including strings, numbers, dates, etc. However, all the arguments in the expression should be of the same data type or at least implicitly convertible.
The query below returns 100 because int can be converted to a string, and the string data type has the highest precedence.
SELECT COALESCE(NULL, NULL, 100, 'Kiwi')
100The following example returns an error because a string cannot be implicitly converted to an int.
SELECT COALESCE(NULL, NULL, 'Cherry', 1)
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Cherry' to data type int.SQL COALESCE Vs ISNULL
Both the COALESCE function and the ISNULL function help deal with NULL values in a table column. There are some differences between them, and we cover them in this section.
Arguments: ISNULL accepts only two arguments, whereas COALESCE accepts multiple arguments.
SELECT ISNULL(NULL, 'Apple') SELECT COALESCE(NULL, 'Apple', NULL, 'Banana', NULL)
Apple
AppleNULL arguments: If all the arguments are NULL values, the ISNULL function returns NULL as the output.
SELECT ISNULL(NULL, NULL)
NULLHowever, the COALESCE function will throw an error.
SELECT COALESCE(NULL, NULL)
Message
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.Return Value: Choosing the data type of a return value is different for both functions.
The ISNULL function selects the data type of the first argument as the return value. The first ISNULL statement returns an integer, and the second one returns a VARCHAR as the output.
SELECT ISNULL(NULL, 1) SELECT ISNULL(NULL, '1');
1
1The below COALESCE statement returns VARCHAR as the output. Although the first NOT NULL value is an integer value, the VARCHAR has the highest precedence over the integer.
SELECT COALESCE(NULL, 1, '1');
1Evaluation: The ISNULL function is evaluated once, whereas the COALESCE function can be evaluated multiple times. So, the query performance of both functions is different.
Nested: Both allow nested functions.
As we mentioned earlier, there is no need to use the nested SQL COALESCE function as a single one will do the same. On the other hand, the ISNULL function accepts two arguments; we must use the nested ISNULL function to handle multiple values.
For instance, the nested COALESCE functions in the first SELECT statement is not necessary.
SELECT COALESCE ([Office], COALESCE([Mobile], 'Unknown')) AS PHONE FROM [Emp]
To do the same work using ISNULL, we must utilize the nested function.
SELECT ISNULL([Office], ISNULL([Mobile], 'Unknown')) AS PHONE FROM [Emp]
Comments are closed.