Can any one explain about Coalesce()?

Yashi

7y970

Can any one explain about this statements?

The Coalesce() function returns the first non-null value among its arguments.
The COALESCE() function is based on the ANSI SQL standard whereas ISNULL function is a Transact-SQL function.
Can any one explain about ANSI SQL standard and Transact-SQL function.
An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.
If we use more than two parameters with the ISNULL function then we must use nested ISNULL functions.
can any one explain nested ISNULL function
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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar7y ago
Accepted answer
Hi Yashi,

If you want to use get value of column but that column is null then you want pick value fron another column then you can go for coalesce(). It will pick first Non null value

IsNull() funcation you can apply when first column is null then you want to pick value from second column.

Diff : coalesce() you can pass more then Two params but Isnull() you can check for two values only.

Example :
Declare @FirstName varchar(100)=null
Declare @LastName varchar(100)=null
Declare @FullName varchar (100)='Full Name'


select  coalesce(@FirstName, @LastName, @FullName)
--Result Full Name
select Isnull(@LastName,@FullName)

Declare @FirstName varchar(100)=null
Declare @LastName varchar(100)='Last Name'
Declare @FullName varchar (100)
set @FullName='FullName'

select  coalesce(@FirstName, @LastName, @FullName)
--Result Last Name
Sourav Mukherjee7y ago
ISNULL is a function, it is evaluated only once. However, the input values for the COALESCE expression can be evaluated multiple times. This is important and also the definition is again very straightforward of Coalesce and IsNull.

The ISNULL return value is always considered NOT NULLable. whereas COALESCE with non-null parameters is considered to be NULL.