Post
Ask Question

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)

Sql.jpg

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

  1. Jignesh 3890001123

  2. Tejas 4590001124

  3. Rakesh (278)0001123

  4. 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.


Similar Articles