What is the use of COALESCE in SQL Server



Let us understand the use of COALESCE with the help of an example.


In this example, the Candidate table is shown to include three columns with information about a Candidate:
1. Candidate_id
2. PrimaryEmail
3. SecondaryEmail


COALESCE in the SELECT statement below, selects the PrimaryEmail if it is not null. If the PrimaryEmail is null then SecondaryEmail will be selected. If both PrimaryEmail and SecondaryEmail is present then only PrimaryEmail is selected. So, COALESCE returns the first nonnull column among the list of columns passed. If both PrimaryEmail and SecondaryEmail is NULL, COALESCE returns NULL.




COALESCE can also be used in joins as shown in the example below. If the Candidate table has a non null value in the Email column, then the value is selected. If the Email column is null in the Candidate Table then, CompanyEmail from CandidateCompany Table is selected.

13 comments:

  1. AnonymousAugust 22, 2011 at 10:45 PM

    Thanks venkat! this is the thing which i came across for the first time now i will start make use of it in real time scenarios

    ReplyDelete
  2. AnonymousAugust 31, 2011 at 6:44 PM

    yup realy great thanks from my side vijay raj gupta

    ReplyDelete
  3. kakuSeptember 26, 2011 at 3:42 AM

    but the problem is that it also displays null columns in that table if some of the columns in having both primary and sec is null.....

    ReplyDelete
  4. UnknownMarch 31, 2013 at 8:51 PM

    The best website ever for the concepts to understand in a solid way.... Thank you Venkat.

    ReplyDelete
  5. gjkOctober 20, 2013 at 9:59 AM

    COALESCE can also be used to concatenate multiple rows as a single comma separated row.

    declare @sample varchar(50)
    select @sample = @sample + COALESCE(@sample, ',') + colname
    from tablename

    ReplyDelete
  6. UnknownJanuary 6, 2014 at 6:27 AM

    Thanks
    This way make easy in practice

    ReplyDelete
  7. AnonymousApril 27, 2014 at 8:43 PM

    COALESCE can also be used to concatenate multiple rows as a single comma separated row use bellow query(SLIGHT MODIFIED ABOVE QUERY)

    DECLARE @Names VARCHAR(MAX)
    SELECT @Names = COALESCE(@Names + ', ','') + NAME FROM EMPLOYEE
    SELECT @Names

    ReplyDelete
    Replies
    1. AnonymousFebruary 14, 2017 at 9:24 PM

      but name should not a null value in name if any null value exist then use where name is not null

      Delete
  8. AnonymousDecember 3, 2014 at 4:47 AM

    Thanks Venkat..I have never use COALESCE as I was not aware of it. but now I came across it through you article.

    ReplyDelete
  9. DipaliFebruary 19, 2016 at 10:06 AM

    Awesome explanation...toooooo gooooood..

    ReplyDelete
  10. AnonymousDecember 28, 2016 at 3:31 AM

    We also can use CASE instead of using COALESCE function, so my concern which one is fast

    ReplyDelete
  11. AnonymousApril 5, 2017 at 3:41 AM

    Above Questions are using Fresher Or Experience candidate?

    ReplyDelete
  12. UnknownApril 24, 2018 at 7:14 AM

    DECLARE @Names VARCHAR(MAX)
    SELECT @Names = COALESCE(@Names + ', ','') + St_Fname FROM
    ( select s.St_Fname from Student as s)AS D
    SELECT @Names as first_Name


    DECLARE @Names VARCHAR(MAX)
    SELECT @Names = COALESCE(@Names + ', ','') +
    cast(ISNULL(St_Fname, '') as VARCHAR) FROM
    ( select s.St_Fname from Student as s)AS D
    SELECT @Names as first_Name

    Why do the results of these two queries different?

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

Subscribe to: Post Comments (Atom)
 
Disclaimer - Terms of use - Contact Us