Difference between COALESCE and ISNULL function in SQL Server

ISNULL()

Syntax: ISNULL(expression, Replace_value )

--It takes two parameter

--SQL Server Specific

--Return 1^st parameter if it is not null.

Example:

SELECT ISNULL(NULL, 'SQLServer');

Result: SQLServer

SELECT ISNULL('Inside', 'Forum');

Result: Inside

SELECT ISNULL(NULL, 10);

Result: 10

SELECT ISNULL(18, 88);

Result: 18

SELECT ISNULL(NULL, '2015-10-20');

Result: '2015-10-20'

COALESCE()

Syntax: COALESCE( expression1, expression2, ... expression_n )

-It takes multiple parameters

-ANSI Standard

-Return 1st parameter if it is Not NULL.

Example:

SELECT COALESCE(NULL, 'SQL Server', 'Inside');

Result: SQL Server

SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4);

Result: 1

Difference:

COALESCE and ISNULL function is used to return the first non-null expression.

Here is the few differences:

1. COALESCE is a ANSI standard where ISNULL is SQL Server Specific

2. COALESCE can accept multiple parameters whereas, ISNULL can accept only two parameters

declare @first as int=NULL

declare @sec as int=1234

declare @third as int=12

select COALESCE(@first,@sec,@third) as ColParam, ISNULL(@first,@sec) as IsNUllParam

3. COALESCE will not support two NULL Parameters while ISNULL will support

select COALESCE(NULL,NULL) as ColParam, ISNULL(NULL,NULL) as IsNUllParam

4. COALESCE will not depend on the length of the datatype, it will return the whole string

ISNULL depends on the length of the first datatype.

declare @first as varchar(3)=NULL

declare @sec as varchar(10)='123456'

declare @third as varchar(4)='5689'

select COALESCE(@first,@sec,@third) as ColParam, ISNULL(@first,@sec) as IsNUllParam, ISNULL(@first,@third) as IsNUllParam1