Handling NULL values in GREATEST() function in Oracle Database

We can use GREATEST function to return the greatest value in a list of expressions. For example, below statement will return the value 3.

SELECT GREATEST(1, 2, 3) FROM DUAL;

However, if there is a NULL value in it, the return value will be NULL. As NULL is unknown, there is no way to do the comparison. If you call a SQL function with a null argument, then the SQL function automatically returns null.

The workaround here is to use NVL to return an alternative value when an expression is NULL.

Let's say we want to find out the max value between DATE 1 - 3. We can change the statement from

SELECT GREATEST(
DATE1,
DATE2,
DATE3
) FROM MY_TABLE;

to

SELECT GREATEST(
NVL(DATE1, TO_DATE('1970-01-01', 'YYYY-MM-DD')),
NVL(DATE2, TO_DATE('1970-01-01', 'YYYY-MM-DD')),
NVL(DATE3, TO_DATE('1970-01-01', 'YYYY-MM-DD'))
) FROM MY_TABLE;

By adding NVL, we can ensure that the expected return value is not NULL.