From time to time, we come across a situation where we need to handle NULL values in a database. This tutorial will look at an important SQL Server function that allows you to compare values and return the first non-null value within the set.
What is a null value?
We can define a null value as a missing or no value. In SQL Server, a null value refers to a column whose value is unknown or missing. Hence, a zero or an empty string is not a null value.
SQL Server Coalesce Function
The coalesce () function in SQL Server is used to sequentially evaluate a set of values and return the first non-null value from the set.
You may wonder, why do I need to use the coalesce() function over isnull()?
Sometimes, you can use the coalesce() function as an alternative to isnull(). The advantage of coalesce over isnull is that it accepts over two parameters.
The syntax of the coalesce() function is as shown:
The function takes scalar expressions and returns the first non-null expression from the provided set. If all the expressions evaluate to null, the function will also return a null value, indicating there is no non-null value.
You can use the COALESCE function in SQL clauses that accept expressions such as SELECT, WHERE, GROUP BY, etc.
Properties of SQL Server coalesce() Function
The coalesce function contains the following properties:
- The scalar expressions must be of a similar type.
- The function always evaluates an integer first.
- It can take more than a single expression
Let us look at various examples of using the coalesce() function in SQL Server.
Example 1
The following example shows how to use the coalesce function to return the first non-null value from the provided set.
The code above should return the string ‘null’.
Example 2
You can also use the coalesce function on a set of integer values, as shown in the example code below:
Since the first non-null value in the set is 1, the function should return 1 as:
Example 3
What happens if we use the coalesce function with expressions of a different type? Consider the example code below:
If you execute the code above, SQL Server returns an error message as:
Example 4
A good example use case of the coalesce function is when performing string concatenation. Consider the example query below:
SELECT FirstName + ' ' + MiddleInitial + LastName FullName FROM Customers;
You notice that the above query returns rows of NULL values as shown:
To resolve this, we can use the coalesce function as shown in the query below:
SELECT FirstName + ' ' + COALESCE(MiddleInitial, '') + LastName FullName FROM Customers;
The code should eliminate the NULL values as shown in the output below:
Example 5
You can use the coalesce function to replace null values with new values.
For example, consider the example query below:
SELECT * FROM Customers
ORDER BY CustomerID;
This returns the columns in the customers’ table. You will NULL values in the MiddleInital column:
Instead of a null value, we can use the coalesce function to return the string “Empty’ where the MiddleInitial is missing.
An example query is as shown:
SELECT CustomerID, FirstName, COALESCE(MiddleInitial, 'Missing') AS MiddleInitial, LastName FROM Customers
ORDER BY CustomerID;
The code above replaces the NULL values with the string “Missing” as shown:
Compare Coalesce() vs Case
If you have used the case expression in SQL Server, you notice that the coalesce() function behaves similarly. This is because the coalesce() function is a more compact and minimized version of the case statement. It is less code and much readable.
Wrapping Up!
This guide covered the basics of using the SQL Server coalesce function to get the first non-null from a given set. We also demonstrated example use cases of the coalesce function.
Thanks for reading and checking more SQL Server tutorials.
