SQL COALESCE
SQL COALESCE function returns the first non-null value from the list of values passed as arguments to this function. SQL COALESCE function is very much similar to NVL except that SQL COALESCE function can take multiple arguments instead of just single argument used in NVL. SQL COALESCE function can be used to replace NVL functions, but the best practice is to use NVL for single argument values.
The syntax is
COALESCE (expr1, expr2, expr3… …….exprN)
There is no limit on number of arguments which can be passed to SQL COALESCE function. If value for all the arguments passed is NULL, then SQL COALESCE function will return NULL as result.
Example:
The following example uses SQL COALESCE function to return hire_date or birth_date or sysdate if both columns are NULL in employee table:
SELECT full_name, COALESCE (hire_date, birth_date, sysdate) FROM employee;
Output:
