COALESCE and ISNULL Functions in SQL Server

feature-sql1-618x350

SQL Server has introduced multiple ways to handle NULL values. The functions that are most used to handle NULLs are COALESCE and ISNULL in SQL Server. These functions are used to find Non-NULL values from a list of arguments. In this post, we will see the uses of the ISNULL and COALESCE functions and their differences with examples.

COALESCE function in SQL Server

1. The COALESCE function in SQL server is used to return the first Non-NULL value from the list of columns/arguments given in order.

2. COALESCE is considered similar to writing a CASE statement expression in SQL.

3. COALESCE is an ANSI SQL standard function. It is available with all major RDBMS including Oracle, MySQL.

4. Syntax – COALESCE ( expression [ ,…n ])

ISNULL function in SQL Server

1. ISNULL is a function introduced especially in T-SQL to replace a NULL value with a specified replacement value.

2. Syntax – ISNULL ( check_expression , replacement_value )

The Difference between ISNULL and COALESCE

1. ISNULL is a function. Hence, it is evaluated only once. COALESCE is an expression similar to a case statement. It would be evaluated multiple times.

2. The ISNULL function is specific to Microsoft and was introduced in SQL Server. COALESCE is an ANSI standard function which is used by all major RDBMSs (e.g., Oracle, MySQL).

3. The return data type for an ISNULL function uses the data type of the first argument or second argument if the first argument is NULL. If both the arguments are NULL, then it will return an INTEGER data type. COALESCE, following case statement rules, uses the highest of the precedence principal to return the data type. COALESCE returns an error if all the input parameters are NULL.

4. ISNULL takes only two parameters. COALESCE takes a variable number of parameters.

Examples of COALESCE and ISNULL functions

We will create an employee table with Employee Id, first name, middle name, last name, contact number and salary columns.

Except for the Employee (Id) column, every other column is considered NULLable.

IF OBJECT_ID ('Employee') IS NOT NULL
DROP TABLE Employee;

CREATE TABLE Employee
(
 Id INT NOT NULL IDENTITY ( 1,1 ) PRIMARY KEY
,FirstName VARCHAR (50) NULL
,MiddleName VARCHAR (50) NULL
,LastName VARCHAR (50) NULL
,ContactNo VARCHAR (10) NULL
,Salary INT NULL
);

Insert a few records in the employee table

Insert into Employee values ( 'Vishwanath', NULL, NULL, '9999955555', 12000);
Insert into Employee values (NULL, 'Niraj', NULL, '9911223344', 14000 );
Insert into Employee values (NULL, NULL, 'Chetan', NULL, NULL);
Insert into Employee values ( 'Atul', 'K', NULL, '9876780987' , NULL);
Insert into Employee values ( 'Vishal', 'M', 'P', NULL, 12000); 

 

ISNULL vs COALESCE in SQL Server

1. Using ISNULL – Display ‘Not Given’ if ContactNo is NULL in the employee table

The following examples retrieve ID and ContactNo columns from the employee table. If the ContactNo column is NULL, the ContactNo is shown in the result set as ‘Not Given,’ using ISNULL function to test for NULL values in column ContactNo.

SELECT Id, ISNULL( ContactNo, 'Not Given')
FROM   Employee ;

 

Output - Result Set

Id    ContactNo
1    9999955555
2    9911223344
3    Not Given
4    9876780987
5    Not Given

 

2. Using ISNULL – Setting salary to 0, if no salary is provided

The following examples retrieve the ID and yearly salary (Monthly salary * 12) column. If Salary is NULL, then substitute it with 0. Use ISNULL function to test for NULL values in salary column.

SELECT Id, ISNULL( Salary, 0 ) * 12 as ContactNo
FROM   Employee;

 

Output - Result Set

Id    ContactNo
1    144000
2    168000
3    0
4    0
5    144000

 

3. Using IS NULL – Finding employees IDs where Contact number is NULL

The following example uses IS NULL (space between IS and NULL) to filter out records on the Contact number column. Where the contact number is not provided means they have NULL values.

SELECT Id, ContactNo as ContactNo
FROM   Employee
WHERE  ContactNo IS NULL;

 

Output - Result Set

Id    ContactNo
3    NULL
5    NULL

 

4. Using IS NOT NULL – Finding employees IDs where Salary is NOT NULL

The following example uses IS NOT NULL to filter out records on Salary column where Salary is NOT NULL values or salary is present.

SELECT Id, Salary as ContactNo
FROM   Employee
WHERE  Salary IS NOT NULL;

 

Output - Result Set

Id    ContactNo
1    12000
2    14000
5    12000

 

5. Using ISNULL function with AVG – To calculate Average salary of all employees

The following example finds the average salary of all employees, including those who have not been assigned a salary figure (i.e., NULL values).

SELECT AVG (ISNULL( salary,0 ))
FROM  Employee;

 

Output - Result Set

AvgEmpSal
7600

 

6. Using COALESCE – Get the first NOT NULL value

The following example uses COALESCE to return the first Not NULL value between the first name, middle name and last name columns.

SELECT Id, COALESCE( FirstName, MiddleName , LastName) as FirstNotNullName
FROM   Employee;

 

Output - Result Set

Id    FirstNotNullName
1    Vishwanath
2    Niraj
3    Chetan
4    Atul
5    Vishal

 

7. Using COALESCE – Get the First NOT NULL value from variable arguments

The following example returns the first NOT NULL value (i.e., ‘Hi There’).

SELECT COALESCE(NULL,NULL,'Hi There',NULL, 'Hello There') as FirstNotNullValue;

 

Output - Result Set

FirstNotNullValue
Hi There

 

8. Using COALESCE – Passing all NULL values as Argument

If we pass all the arguments as NULL to a COALESCE function, it will raise an error stating that at least one argument should not be the null constant.

SELECT COALESCE (NULL, NULL, NULL, NULL) as FirstNotNullValue

Error Message
Msg 4127, Level 16, State 1, Line 22
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

Read more about Database and SQL programming from Tech-Recipes.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.