SQL Book

SQL Coalesce function - how to use it with examples

The SQL Coalesce function receives a list of parameters that are seperated by commas. The function returns the value of the first of its input parameters that is not NULL. The number of parameters you pass to COALESCE is up to you. The parameters passed to Coalesce do not have to be string data types, they can be any data type and can also be different data types. The syntax is as follows.

COALESCE(value1, value2, value3, ...)

In the below example the function would return 'Jack'. This is because the @Name1 variable has not been assigned a value and so is NULL value, the COALESCE function therefore moves onto the next parameter in the list which is @Name2. @Name2 has been assigned a non NULL value and so this value is returned by the function.

DECLARE @Name 1 varchar(20)
DECLARE @Name 2 varchar(20)

SET @Name2 = 'Jack'

SELECT COALESCE(@Name1, @Name2) As 'Chosen Name'

-- Results
=========================
Chosen Name 
----------- 
Jack

The COALESCE function can be used to build dynamic WHERE clauses that modify the query depending on what input parameters are passed to it. An example is given below:

CREATE PROCEDURE dbo.SearchCustomers
 @FirstName varchar(20),
 @LastName varchar(20)
AS 
SET NOCOUNT ON

SELECT CustomerID, FirstName, LastName
FROM Customer
WHERE FirstName  = COALESCE(@FirstName, FirstName)
 AND LastName = COALESCE(@LastName, LastName)
ORDER BY LastName, FirstName

SET NOCOUNT OFF
GO

This stored procedure could be called in a number of ways and the COALESCE function would dynamically return either the non null variable or the column name. Passing the column name will always match the column name we are comparing against.

-- search on firstname
EXEC dbo.SearchCustomers 'Bob', NULL

-- search on last name
EXEC dbo.SearchCustomers NULL, 'Marley'

-- both firstname and lastname were provided
-- so search on both first and last name
EXEC dbo.SearchCustomers 'Bob', 'Marley'

Conclusion

In this article we have explained how the Coalesce function works by returning the first non NULL value that is passed to it. Although it can be used with strings, COALESCE it is not limited to being passed just strings, it can recieve values of other data types as input parameters.

We have seen some real world examples of the function in use including how it can easily be used to generate dynamic WHERE clauses when variables that may be NULL are being used in a query.