SQL quiz. Which of the following <b>KEYWORDS</b> will return

Which of the following KEYWORDS will return the first NON-NULL value from a list of columns?

Explanation

COALESCE function evaluates the arguments one by one and returns the value of the first expression that does not evaluate to NULL.

Theory

General Functions
The objective of the general NULL handling functions is to replace the NULL values with an alternate value.
- NVL - The NVL function substitutes an alternate value for a NULL value.
- NVL2 - As an enhancement over NVL, Oracle introduced a function to substitute value not only for NULL columns values but also for NOT NULL columns.
- NULLIF - The NULLIF function compares two arguments expr1 and expr2. If expr1 and expr2 are equal, it returns NULL; else, it returns expr1. First argument can't be NULL.
- COALESCE - COALESCE function, a more generic form of NVL, returns the first non-null expression in the argument list.
Read more: Using Conversion Functions
COALESCE
COALESCE 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.
Syntax
```
COALESCE (expr1, expr2, ... expr_n )
```
Consider the below SELECT query. It selects the first not null value fed into address fields for an employee.
```
SELECT COALESCE (address1, address2, address3) Address
FROM  employees;
```
The working of COALESCE function is similar to IF..ELSIF..ENDIF construct. The query above can be re-written as:
```
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;
```
Read more: Conv. Func.