How To Use COALESCE In Oracle

How To Use COALESCE In Oracle

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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:

COALESCE(<variable1>,<variable2>,<variable3>,...)

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 );

dba oracle dba database management database management system oracle performance tuning coalesce oracle coalesce in oracle online courses oracle nvl oracle ORA-00938: not enough arguments for function personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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 );

dba oracle dba database management database management system oracle performance tuning coalesce oracle coalesce in oracle online courses oracle nvl oracle ORA-00938: not enough arguments for function personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

Tips:
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.

Oracle COALESCE needs minimum two variables or the function will raise error ORA-00938: not enough arguments for function see the following example.

SELECT COALESCE (dummy)
   FROM dual;

dba oracle dba database management database management system oracle performance tuning coalesce oracle coalesce in oracle online courses oracle nvl oracle ORA-00938: not enough arguments for function personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management database management system oracle performance tuning coalesce oracle coalesce in oracle online courses oracle nvl oracle ORA-00938: not enough arguments for function personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

Always double check your variables to avoid duplicated names.

See Also:
Oracle Select Oracle NVL Oracle NVL2 Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*