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);
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.
