ISNULL VS 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 returns the first non-null expression from its arguments. If all arguments that are passed to COALESCE are null then COALESCE will return null.

SELECT COALESCE('A','B')
--This Returns 'A'

SELECT COALESCE(NULL,'B')
--This Returns 'B'

SELECT COALESCE(NULL,'B' , 'C', null,'D')
--This Returns 'B'

SELECT COALESCE(NULL,NULL,NULL,'C')
--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
(

               EmployeeId INT,
               EmployeeName VARCHAR(50),
               PhoneNumber VARCHAR(12),
               MobileNumber VARCHAR(12),
               WorkPhoneNumber VARCHAR(12)
)

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 then I must show the third contact detail (work Phone Number) and if all contacts are null then show the value null.

EmployeeId EmployeeName ContactDetail

Jignesh 3890001123
Tejas 4590001124
Rakesh (278)0001123
Punit NULL

So I initially thought to write the following type of query:

SELECT EmployeeId,EmployeeName,
CASE WHEN (PhoneNumber IS NULL) THEN

      (CASE WHEN (MobileNumber IS NULL) THEN WorkPhoneNumber
      ELSE MobileNumber END)

ELSE PhoneNumber
END AS ContactDetail1
FROM #Employees

The same query result can be done using COALESCE.

SELECT EmployeeId,EmployeeName,
COALESCE(PhoneNumber,MobileNumber,WorkPhoneNumber) AS ContactDetail
FROM #Employees

ISNULL

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.

SELECT ISNULL('Jignesh','Test')
--This Returns 'Jignesh'

SELECT ISNULL(NULL,'Tejas')
--This Returns 'Tejas'

The preceding result can also be done by ISNULL.

SELECT EmployeeId,EmployeeName,
ISNULL(ISNULL(PhoneNumber,MobileNumber),WorkPhoneNumber) AS ContactDetail

FROM #Employees

ISNULL VS COALESCE

ISNULL	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.
A NULL value for ISNULL is converted to INT Example SELECT ISNULL(NULL,NULL) returns NULL and it is the INT type	For COALESCE we must provide a type. Example SELECT COALESCE(NULL,NULL) throws an exception, whereas: SELECT COALESCE(NULL,CAST(NULL AS INT)) works fine.
The ISNULL and COALESCE performance is nearly identical. but ISNULL is slightly faster than COALESCE.

Conclusion

In this article learned about ISNULL versus COALESCE in SQL.

5 Comments

Join the conversation! Your thoughts help the community grow.

Vijay Pratap Singh3y ago
Helpful! Good Article Sir
Shakti Saxena11y ago
Very Good Article
Shiv kumar13y ago
Thanks Jignesh great explanation
Keyur Patel13y ago
Thanks Jignesh, Very nice explanation in detail about the ISNULL VS COALESCE
Gaurav Gupta13y ago
Very Well Explanation...