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.