Coalesce in SQL server basically acts like a Case statement i.e. if value in one of the column 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 column has 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 different 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 i.e. if we don’t have Alias1, then display Alias2 and if it is also not available, then use Alias3. This can be easily achieved 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
COALESCE acts like case statements and checks if the Alias1 is available or not. 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 we can use the COALESCE in SQL server. Happy coding…!!!