COALESCECOALESCE function, a more generic form of NVL, returns the first non-null expression in the argument list. It takes minimum two mandatory parameters but maximum arguments has no limit.
Consider the below SELECT query. It selects the first not null value fed into address fields for an employee.
COALESCE (expr1, expr2, ... expr_n )
The working of COALESCE function is similar to IF..ELSIF..ENDIF construct. The query above can be re-written as:
SELECT COALESCE (address1, address2, address3) Address FROM employees;
Read more: Conv. Func.
IF address1 is not null THEN result := address1; ELSIF address2 is not null THEN result := address2; ELSIF address3 is not null THEN result := address3; ELSE result := null; END IF;