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. 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. yup realy great thanks from my side vijay raj gupta

    ReplyDelete
  3. 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. The best website ever for the concepts to understand in a solid way.... Thank you Venkat.

    ReplyDelete
  5. 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. Thanks
    This way make easy in practice

    ReplyDelete
  7. 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. but name should not a null value in name if any null value exist then use where name is not null

      Delete
  8. 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. Awesome explanation...toooooo gooooood..

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

    ReplyDelete
  11. Above Questions are using Fresher Or Experience candidate?

    ReplyDelete
  12. 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.

 
Disclaimer - Terms of use - Contact Us