Oracle Differences between NVL and Coalesce

by

COALESCE is more modern function that is a part of ANSI-92 standard.

NVL is Oracle specific, it was introduced in 80‘s before there were any standards.

In case of two values, they are synonyms.

However, they are implemented differently.

NVL always evaluates both arguments, while COALESCE usually stops evaluation whenever it finds the first non-NULL (there are some exceptions, such as sequence NEXTVAL):

SELECT  SUM(val)
FROM    (
        SELECT  NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

This runs for almost 0.5 seconds, since it generates SYS_GUID()‘s, despite 1 being not a NULL.

SELECT  SUM(val)
FROM    (
        SELECT  COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

This understands that 1 is not a NULL and does not evaluate the second argument.

SYS_GUID‘s are not generated and the query is instant.

More Related Contents:

  1. Receive an unexpected symbol error in my PLSQL query [closed]
  2. SQL Query to concatenate column values from multiple rows in Oracle
  3. Search All Fields In All Tables For A Specific Value (Oracle)
  4. Select First Row of Every Group in sql [duplicate]
  5. Oracle: how to UPSERT (update or insert into a table?)
  6. Paging with Oracle
  7. Comparing Dates in Oracle SQL
  8. How do I ignore ampersands in a SQL script running from SQL Plus?
  9. How can I combine multiple rows into a comma-delimited list in Oracle? [duplicate]
  10. Is it possible to output a SELECT statement from a PL/SQL block?
  11. Oracle SQL, concatenate multiple columns + add text
  12. Oracle query to fetch column names
  13. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  14. SQL Error: ORA-01861: literal does not match format string 01861
  15. Determine Oracle null == null
  16. How to anticipate and escape single quote ‘ in oracle
  17. Oracle insert if row does not exist
  18. SELECT INTO using Oracle
  19. Display names of all constraints for a table in Oracle SQL
  20. `show create table` equivalent in oracle sql
  21. Does ‘Select’ always order by primary key?
  22. Query json dictionary data in SQL
  23. ORA-00972 identifier is too long alias column name
  24. How to use a dynamic parameter in a IN clause of a JPA named query?
  25. How to check if a column exists before adding it to an existing table in PL/SQL?
  26. How can I solve ORA-00911: invalid character error?
  27. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  28. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  29. Exporting a CLOB to a text file using Oracle SQL Developer
  30. Oracle ‘Partition By’ and ‘Row_Number’ keyword

Leave a Comment