Difference between Isnull and Coalesce in SQL Server

Difference between Isnull and Coalesce

Both Isnull and Coalesce functions are work similarly in SQL Server as both functions used to work on Null values.

Now we will discuss about both isnull and coalesce functions individually.


What is Isnull function in SQL Server?

Isnull function is used to replace the Null values with some specified value.

Syntax of Isnull function in SQL Server:

ISNULL(expression, specified_value)

Get more details @ Isnull function with example in sql server

What is Coalesce function in SQL Server?

Coalesce function is used to get first not Null value among its arguments.

Syntax of Coalesce function in SQL Server:

COALESCE (expression1,expressions2, ...)

Get more details @ Coalesce function with example in sql server

I hope, now you understood how Isnull and Coalesce functions will work in sql server.

Now we discuss about what is the difference between Isnull and Coalesce in SQL Server

1st difference between Isnull and Coalesce in SQL Server:

Isnull function is used to replace the Null values with some specified value and Coalesce function is used to get first not Null value among its arguments

2nd difference between Isnull and Coalesce in SQL Server:

Isnull function is a specific to the sql server and Coalesce function is a ANSI standard

3rd difference between Isnull and Coalesce in SQL Server:

Isnull function returns the same data type as of first argument and coalesce function returns the data type as of its first argument

Example: We have table named as Employee_Contact_Number in sql server as below

Eid	Ename	Mobile_Number
101	Mahesh	9999999999
102	Pawan	NULL
103	Venkat	NULL
104	Vasu	123467890
105	Khadar	90767433893

Now we want to display ‘Not Available’ wherever the Mobile_Number value is null.

In this scenario, we will use isnull function, right?

Yes, we should use isnull function only. Ok let’s use isnull function as below

SELECT  [Eid]
      ,[Ename]
      ,isnull([Mobile_Number], 'Not Available')
  FROM [Employee_Contact_Number]

As of Isnull function definition, the above query should replace the null value as ‘Not Available’. But if you ran the above query, it will give error as “Error converting data type varchar to bigint”.

Cause of Error:  Isnull function returns the same data type as of first argument

So to resolve the above error you have to write the query as

SELECT  [Eid]
      ,[Ename]
      ,isnull(cast([Mobile_Number] as varchar(50)), 'Not Available')  as [Mobile_Number]
  FROM [Employee_Contact_Number]

4th difference between Isnull and Coalesce in SQL Server:

By observing the syntax of both Isnull and coalesce function, we can say that Isnull function will only two arguments whereas coalesce function will use ‘n’ number of arguments

That’s all, differences between Isnull and coalesce functions are got over.

Which is faster?

In the query performance point of view, we should know which function is faster. In both of Isnull and Coalesce functions Isnull is faster than coalesce function because of isnull function is specific built in function in sql server and coalesce translates to case statement

Note: If you know any more information about difference between Isnull and coalesce function is sql server then please comment here. That will help others.

If you want to get more topics in sql server via email, please subscribe with us.

Enter Your Mail ID here to Subscribe

 If you are job seeker and want to apply for software developer Jobs, Apply Here

You May Also search in google about this topic as:

isnull vs coalesce in sql server

difference between coalesce and isnull in sql server

isnull vs coalesce which is faster

what is the difference between isnull and coalesce in sql server

isnull and coalesce in sql server with example