- The SQL COALESCE function is used to handle NULL values and return the first, non-null value from the series of expressions.
- The SQL Coalesce function evaluates the arguments in order and always returns the first non-null value from the defined argument list.
- During the expression evaluation process, the NULL value is replaced with user-defined value.
- The COALESCE function evaluates the arguments from left to right and It stops evaluating until it finds the first non-null argument. It means that all the remaining arguments are not evaluated at all.
- The COALESCE function returns NULL if all arguments are NULL.
Syntax:-
COALESCE (expression1, expression2,expression3, ..., expressionN)
- In Coalesce function Expressions should be same data-type and Coalesce function can contain multiple expressions
Let’s take an example to understand more clearly how coalesce function works :-
SELECT COALESCE (NULL,'Apple','Orange') SELECT COALESCE (NULL,50,60,70,80) select coalesce(null, null, 1, null) select coalesce(1, 2) SELECT COALESCE (NULL,NULL,40,NULL,NULL) SELECT COALESCE (NULL,NULL,NULL,NULL,NULL,'Steve Jobs') SELECT COALESCE (NULL,NULL,NULL,NULL,101,'Steve Jobs')
Output:-
Here I have created one table with name Employees and inserted some records with NULL values.
- Now going to write a query that display the employee’s name. If an employee has all the columns filled out – first, middle and last name, then we only want the first name. If FirstName is NULL, and if the middle and last names are filled out, then we only want the middle name.
SELECT Id, COALESCE(FirstName, MiddleName, LastName) AS Name FROM Employees
Output:-
