Difference between ISNULL and COALESCE

ISNULL

ISNULL  function returns null value with replacement value.

Syntax : ISNULL(expression_value,replacement_value)

It will take only 2 parameters or arguments.First it will check the expression value if its null then it will return replacement value.

Eg :

Declare @value varchar(10)
Set @value = 10
Select ISNULL(@value,’n/a’) as Demo

In the above scenario the output is 10

Declare @value varchar(10)
Select ISNULL(@value,’n/a’) as Demo

In this scenario the output is ‘n/a’

COALESEC

The Coalesce() function returns the first non-null value among its arguments. This function doesn’t limit the number of arguments, but they must all be of the same data type.

Syntax

COALESCE ( expression [ ,…n ] )
COALESCE() function is equivalent to the following CASE expression.CASE
WHEN (exp1 IS NOT NULL) THEN exp1
WHEN (exp2 IS NOT NULL) THEN exp2

ELSE expN

Eg:

Declare @value varchar(20)
set @value = ‘Hello world’
Declare @test varchar(20)
Declare @example varchar(20)
Select COALESCE (@test,@example,@value,’n/a’) as Demo

It will return ‘Hello World’.

Thank You!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: