ISNULL VS COALESCE, Nested ISNULL

There is a myth bound to COALESCE function that it is just a replacement of ISNULL. Many candidate in interview also agree to this statement that COALESCE is just replacement of ISNULL but most of them were not aware of advantage of using COALESCE in place of ISNULL.

So Lets See ISNULL VS COALESCE

---

Test 1 : Similar Functionality Of ISNULL and COALESCE

Use of ISNULL

Declare @Id1 Int
Declare @Id2 Int
Set @Id1=10
Select ISNULL(@Id2,@Id1) as Value

Returns 10 as Expected

Use of COLESCE

Declare @Id1 Int
Declare @Id2 Int
Set @Id1=10
Select COALESCE(@Id2,@Id1) as Value

Returns 10 as well.

So Its Proved That Both Provided Same Functionality

---

Test 2 : Mixing DataType

Use ISNULL with Mixed DataType

Declare @Var1 Int
Declare @Var2 DateTime
Set @Var2=GETDATE()
Select ISNULL(@Var1,@Var2) as Value

Output : Throws Error

Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Use COALESCE with Mixed DataType

Declare @Var1 Int
Declare @Var2 DateTime
Set @Var2=GETDATE()
Select ISNULL(@Var1,@Var2) as Value

Output : Return CurrentDate

So its clear that ISNULL only support checking of similar DataType whereas COALESCE can be used with different data type as you saw in example above.

---

Test 3 : Nested ISNULL VS NESTED COALESCE

To perform TEST 3 create table and populate data with following script.

Create Table NextedNull
(
Id Int Identity(1,1),
Slot1 Varchar(10),
Slot2 Varchar(10),
Slot3 Varchar(10),
Slot4 Varchar(10),
Slot5 Varchar(10)
)
Go

Insert Into NextedNull Values 
(null,null,null,null,'Active'),
(null,'Active',null,null,null),
('Active',null,null,null,null),
(null,null,'Active',null,null),
(null,null,null,null,null)
Go

Select * From NextedNull
Go

Output :

Now the task is to check Active slot against each Id in this table and if any slot is active then result should be Active else it should return ‘No Active Slot’.

Using ISNULL

Select Id,ISNULL(Slot1,Isnull(Slot2,ISnull(Slot3,Isnull(Slot4,Isnull(Slot5,'No Active Slot'))))) IsActive 
from NextedNull
Go

Ouptut :

lets try to achieve above task with COALESCE.

Select Id,COALESCE(Slot1,Slot2,Slot3,Slot4,Slot5,'No Active Slot') IsActive
From NextedNull
Go

Output :

So we have seen the flexibility and benefits of using COALESCE over ISNULL and we found that COALESCE provides more functionality then just replacing role of ISNULL.