Saturday, 12 August 2017

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.
Difference-between-Isnull-and-Coalesce-in-SQL-Server

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

Freshers, Register Here

share facebook

Enter Your Email below to get Daily Job Alert in Your Mailbox