Can any one explain about Coalesce()?

Question

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.

Jignesh Kumar · 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 Mukherjee · Answer

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.

Can any one explain about Coalesce()?

Answers (2)