ISNULL VS COALESCE in SQL
In this article you will learn about ISNULL versus COALESCE in SQL.
Introduction to COALESCE
COALESCE is nothing but a version of ISNULL, that can take more than two parameters. COALESCE is also part of ANSI - 92. Basically it resturns the first non-null expression from its arguments. If all arguments that are passed to COALESCE are null then COALESCE will return null.
--This Returns 'A'
--This Returns 'B'
SELECT COALESCE(NULL,'B' , 'C', null,'D')
--This Returns 'B'
--This Returns 'C'
Practical use of COALESCE
For example we have a table called Employees and the definition of this table is as in the following and it has some dummy data.
CREATE TABLE #Employees
INSERT INTO #Employees VALUES(1,'Jignesh','3890001123','3890001124','(389)0001123')
INSERT INTO #Employees VALUES(1,'Tejas',NULL,'4590001124','(389)0001124')
INSERT INTO #Employees VALUES(1,'Rakesh',NULL,NULL,'(278)0001123')
INSERT INTO #Employees VALUES(1,'Punit',NULL,NULL,NULL)
Here the employee has three contact details, Phone Number (main contact), Mobile Number and work Phone Number. Now as per my requirement I must show the employee detail with the main contact detail (phone Number), if the main contact detail is null then I must show the secondary contact detail (Mobile Number), if the secondary contact detail is null than I must show the third contact detail (work Phone Number) and if all contacts are null then show the value null.
EmployeeId EmployeeName ContactDetail
So I initially thought to write the following type of query:
CASE WHEN (PhoneNumber IS NULL) THEN
(CASE WHEN (MobileNumber IS NULL) THEN WorkPhoneNumber
ELSE MobileNumber END)
END AS ContactDetail1
The same query result can be done using COALESCE.
COALESCE(PhoneNumber,MobileNumber,WorkPhoneNumber) AS ContactDetail
Since we all know what the use of ISNULL is, it replaces the null value with a specific value. It has two arguments (check expression and replace value).
It returns the check expression value if it is not null else it returns the second argument value.
--This Returns 'Jignesh'
--This Returns 'Tejas'
The preceding result can also be done by ISNULL:
ISNULL(ISNULL(PhoneNumber,MobileNumber),WorkPhoneNumber) AS ContactDetail
ISNULL VS COALESCE
ISNULL is not defined by ANSI - 92
COALESCE is defined by ANSI - 92
ISNULL accepts two parameters
COALESCE can accept more than two parameters, but it has at least two parameters.
ISNULL returns the check expression value if it is not null else it returns the second argument value
COALESCE returns the first non-null expression from its arguments. If all arguments that are passed in COALESCE are null then COALESCE will return null.
ISNULL always returns a NOT NULL value.
COALESCE may return a Null value.
NULL value for ISNULL is converted to INT
returns NULL and it is the INT type
For COAELSCE we must provide a type.
throws an exception, whereas:
SELECT COALESCE(NULL,CAST(NULL AS INT))
The ISNULL and COALESCE performance is nearly identical. but ISNULL is slightly faster than COALESCE.