Understanding SQL server COALESCE() with example (ISNULL comparison included)

Well sometimes we may need to check if the data or string passed in SQL server query is empty or not, in that case we use SQL Server COALESCE to find the first value which is not null, while we have another function ISNULL function it replaces null data with some relevant value, so in this article, I am going to provide you complete details of sql server COALESCE with example & it's comparison with ISNULL.

COALESCE() Function

COALESCE() function is used to "returns the first non-null expression among its arguments". It evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. 

For example,

SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');

returns the third value because the third value is the first value that is not null.

Syntax of COALESCE() function

COALESCE ( expression [ ,...n ] )   

where expression is an expression of any type.

It returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.

Note: If all arguments are NULL, COALESCE returns NULL. At least one of the null values must be a typed NULL.  

One of the most important thing about COALESCE() function is when it is executed it's get converted into SQL server CASE statement, i.e, The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression

CASE  
WHEN (expression1 IS NOT NULL) THEN expression1  
WHEN (expression2 IS NOT NULL) THEN expression2  
...  
ELSE expressionN  
END  

This means that the input values (expression1, expression2, expressionN, etc.) are evaluated multiple times. Also, in compliance with the SQL standard, a value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. In either case, different results can be returned between the first evaluation and subsequent evaluations.

SQL server coalesce example

Let's get started to see few examples of it, but before we start, I consider that you have already downloaded SQL server, also we will be using SQL server example database AdventureWorks2012, so if you don't have sample database download it from this link & restore database using .bak file

1. Simple example

SELECT Name, Class, Color, ProductNumber,  
COALESCE(Class, Color, ProductNumber) 
AS FirstNotNull  
FROM Production.Product;  

In the above example, query shows how COALESCE selects the data from the first column that has a nonnull value.

Output of the above query, when executed in the SQL server is as below

2. A Complex example

Let's take a little bit more complex example, in the below query the wages table includes three columns that contain information about the yearly wages of the employees: the hourly wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and commission.

SET NOCOUNT ON;  
GO  
USE tempdb;  
IF OBJECT_ID('dbo.wages') IS NOT NULL  
    DROP TABLE wages;  
GO  
CREATE TABLE dbo.wages  
(  
    emp_id        tinyint   identity,  
    hourly_wage   decimal   NULL,  
    salary        decimal   NULL,  
    commission    decimal   NULL,  
    num_sales     tinyint   NULL  
);  
GO  
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)  
VALUES  
    (10.00, NULL, NULL, NULL),  
    (20.00, NULL, NULL, NULL),  
    (30.00, NULL, NULL, NULL),  
    (40.00, NULL, NULL, NULL),  
    (NULL, 10000.00, NULL, NULL),  
    (NULL, 20000.00, NULL, NULL),  
    (NULL, 30000.00, NULL, NULL),  
    (NULL, 40000.00, NULL, NULL),  
    (NULL, NULL, 15000, 3),  
    (NULL, NULL, 25000, 2),  
    (NULL, NULL, 20000, 6),  
    (NULL, NULL, 14000, 4);  
GO  
SET NOCOUNT OFF;  
GO  
SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
   salary,   
   commission * num_sales) AS money) AS 'Total Salary'   
FROM dbo.wages  
ORDER BY 'Total Salary';  
GO

Output of the above query is below

3. Another simple example sql server coalesce example

SELECT COALESCE(NULL, NULL, 'qawithexperts', NULL, 'SQL Server')

The Output is: qawithexperts

By the above example we can clearly understand that Coalesce function returns the first nonnull expression among its arguments in sql server.

4. Employee database  coalesce function example

Suppose, we have the database table(EmployeeContactNumber) as below

Now, suppose we need to select any of the Numbers for each of the employee, we should have HomeNumber, OfficeNumber or MobileNumber of each employee  and 1st preference is MobileNumber and 2nd preference is HomeNumber and 3rd preference is OfficeNumber.

So, to fulfill our above need, we can have coalesce query as below

select EmpID, EmpName, coalesce(MobileNumber,HomeNumber,OfficeNumber) as ContactNumber from EmployeeContactNumber

The output for the above query is

ISNULL() SQL server function

ISNULL function replaces NULL with the specified replacement value.

Syntax

ISNULL ( check_expression , replacement_value )  

Where,

check_expression
Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

Example of ISNULL function SQL server

Simple example of ISNULL with AVG

In the below example, we are using AdventureWorks2012 & query below finds the average of the weight of all products. It substitutes the value 50 for all NULL entries in the Weight column of the Product table.  

USE AdventureWorks2012;  
GO  
SELECT AVG(ISNULL(Weight, 50))  
FROM Production.Product;  
GO  

Output of the above query is as below

Difference between SQL server ISNULL() and COALESCE()

1. The ISNULL() method takes only two parameters, it return first parameter if its not null and return the second parameter if the first parameter is null
   While
   COALESCE can take multiple parameters, and return the NOT NULL parameter starting from first to last.
2. ISNULL is a proprietary T-SQL function
   while
   COALESCE is ANSI SQL standard.
3. ISNULL is built-in function implemented in database engine
   while
   COALESCE translates into CASE expression.
4. Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter
   while
   COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.
5. ISNULL is faster than COALESE because it is built-in function.
6. Validations for ISNULL and COALESCE are also different.
   For example, a NULL value for ISNULL is converted to int
   whereas for COALESCE, you must provide a data type.
7. COALESCE function is defined by the ANSI SQL standard and supported in all major databases e.g. MySQL, Oracle, PostgreSQL, DB2 etc
   while
   ISNULL() is a T-SQL (Transact SQL) function and only work with Microsoft products e.g. Microsoft SQL Server 2004, 2008, 2012, 2014 & above.

That's it, if you have any questions related to the above post feel free to ask them in the below comments or in the questions section.

You may also like to read:

Uniqueidentifier in SQL Server

Understanding SQL server switch case (With Example)