Differences Between IsNull() and Coalesce() Functions in SQL Server

# Differences Between IsNull() and Coalesce() Functions in SQL Server 

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.The SQL Coalesce and IsNull functions are used to replace null value with another value of a column. 

1.The ISNULL() function is a Transact-SQL function whereas COALESCE() function is based on the ANSI SQL standard.  

2.The ISNULL() function contains only two parameters. The COALESCE() function contains multiple parameters.

ISNULL

Syntax:- ISNULL(expression,replacement_value)
example:- ISNULL(mobile_number, 'N/A'); output:- N/A

COALESCE

syntax:- COALESCE(val1, val2, ...., val_n)
example:- COALESCE(NULL, 1, 2, 'Google.com'); output:- 1

3.The ISNULL() function contains various types of parameters. The COALESCE() function doesn't limit the number of arguments, but they must all be of the same data type.

4.The ISNULL() function looks at the first value and the second parameter value is automatically limited to that length but COALESCE() does not have this restriction.

Example:- Create a table student and insert value in it.

create table student

(

    id int identity,

    rollnumber int,

    name nvarchar(50) not null,

    mobile_number nvarchar(10),

    city nvarchar(50) not null,

    hobby nvarchar(50)

)

 

insert into student
select 101, 'John','235546525','New York','Tennis'
union all
select 102, 'Rita',null,'Texas',null
union all
select null, 'Avira',null,'Missicippi','Shotting'
union all
select 101, 'Smith','5522551235','Washington-DC',null

      Output

           select * from student

   Now replace the NULL with some user friendly data using ISNULL function .

select ISNULL(rollnumber, 0) as rollnumber,

       name,

       ISNULL(mobile_number, 'N/A') as mobile_number,

       City,

       ISNULL(hobby, 'Unknown') as hobby

from student

        

              Now replace the NULL with some user friendly data using COALESCE function .

select COALESCE(rollnumber, 0) as rollnumber,

       name,

       COALESCE(mobile_number, 'N/A') as mobile_number,

       City,

       COALESCE(hobby, 'Unknown') as hobby

from student