This online tutorial is based on examples to make it easier to follow. Oracle COALESCE function returns first non-empty (not null) value that is declared in the function list. The function is inherited from ANSI standard and it works in the same way in other system as in Oracle. The COALESCE in Oracle works in similar way as Oracle NVL but with only one exception – you can declare as many variables you need in the function. Even Oracle NVL is limited to only two variables we do recommend to use in cases where you have only 2 values to compare. Oracle NVL has fixed input parameters and this makes it faster than COALESCE. Oracle started to support Oracle COALESCE from Oracle 9i version and syntax is following:
The next Oracle DBA sql statement has Oracle COALESCE function with 4 variables. The third and fourth variables are not null and the first and second are null (empty). As you see the output returns value “3” from the third column and not the fourth value “Y“. If the third variable would be set null then the COALESCE function would return the fourth value “Y“.
SELECT COALESCE(my_value1, my_value2, my_value3,'Y') FROM (SELECT NULL AS my_value1, NULL AS my_value2, 3 AS my_value3 FROM dual );
In the second example all first three values are empty and the COALESCE function returns the last variable “Y“.
SELECT COALESCE(my_value1, my_value2, my_value3,'Y') FROM (SELECT NULL AS my_value1, NULL AS my_value2, NULL AS my_value3 FROM dual );
When your need to compare only two values then we do recommend to use Oracle NVL since it has two fixed input places and it works faster than Oracle COALESCE.
Do not use NVL function in NVL. Use instead Oracle COALESCE because using two functions instead of one is slower. Use Oracle COALESCE when you need to compare more then two values.
SELECT COALESCE (dummy) FROM dual;
You can declare the same name column twice (even it does not make any sense), but by mistaken you could write two columns with the same name and it is important to know that COALESCE in Oracle would not warn you about that.
SELECT COALESCE (dummy,dummy) FROM dual;
Always double check your variables to avoid duplicated names.