Coalesce in SQL Server basically acts like a Case statement, in other words if the value in one of the columns is not available then use the value of the second column and if that is also not available, then use the third column. If none of the columns have the value, then null is returned for that value. It's syntax is:

COALESCE ( expression [ 1...n ] )

Here, expressions 1....n are like possible case statements. Let's discuss this in detail. Consider the following data in a table:



We have multiple values of alias names for all the users. But some of them have Alias1, some have Alias2 and some have Alias3. We are required to display alias names based on their availability in the database, in other words if we don't have Alias1, then display Alias2 and if it is also not available, then use Alias3. This can be easily done using COALESCE in the Select statement.

To do this, we simply write the following query with the COALESCE keyword.

SELECT EmpId, EmpName,
COALESCE (Empalias1, empalias2, empalias3) AS AliasName
FROM Employees

Execute the query and see the results.



COALESCE acts like case statements and checks whether or not the Alias1 is available. If not, then use the Alias2 column and even if that is not available, then use Alias3. This is what happens in the records of Ray, Chris, Jenny and Simi. For Norman, there was no value in any of the alias columns, so it returned null for it.

So this is how to use the COALESCE in SQL Server. Happy coding...!!