SQL Coalesce function - learn how to use it with examples
The SQL Coalesce function receives a list of parameters that are separated 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 is therefore NULL. The COALESCE function, therefore, moves onto the next parameter in the list which is @Name2. @Name2 has been assigned a value so it is not NULL, 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 several 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'
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 is not limited to being passed just strings, it can receive 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.