.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

what is the exact use of coalesce function in sql server?

Posted By: Deco     Posted Date: September 14, 2010    Points:2   Category :Sql Server
please prompt the exact use. i googled and cameback with the mess...

Author: Syed Shakeer Hussain             
Posted Date: September 14, 2010     Points: 5   

Hi Alwyn,

Check the below link for more information with example.


Author: Deco             
Posted Date: September 14, 2010     Points: 5   


It seems like ISNULL and Coalesce function are same.

Can you please clarify my doubt?

Author: Majith             
Posted Date: September 14, 2010     Points: 5   

Hi Alwyn,

Coalesce returns the first nonnull expression among its arguments.

Example :


WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

ELSE expressionN

ISNULL and COALESCE both are equivalent but it behaves differently.

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.

Also check the link

Author: Rajshree             
Posted Date: September 15, 2010     Points: 5   

COALESCE(arg1,arg2,...) is a pretty useful function in SQL. Suppose we have a table A having 3 columns FullName, CompleteName and DisplayName. Any of these columns can contain null values. Now we want to select the DisplayName from this table, but if it is null, then return FullName, if that is also null then return CompleteName. We can easily perform the same in one select statement as:

SELECT COALESCE(DisplayName, FullName, CompleteName) From A

This will return the first non-null value. And best thing is that COALESCE is an ANSI standard.

Here is a nice comparison of CAOLESCE() and ISNULL() (which is a T SQL function):


Post Reply

You must Sign In To post reply
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend