COALESCE vs. NVL

View All Scripts Login to Run Script

Script Name COALESCE vs. NVL
Description Demonstrates the differences between COALESCE and NVL COALESCE uses short-circuit evaluation; NVL always evaluates both arguments.
Area SQL General / Functions
Contributor danilopiazza
Created Thursday October 29, 2015

Statement 1

CREATE OR REPLACE FUNCTION FOO RETURN NUMBER AS
BEGIN
    RETURN 1;
END FOO;

Function created.

Statement 2

CREATE OR REPLACE FUNCTION BAR RETURN NUMBER AS
BEGIN
    RAISE_APPLICATION_ERROR(-20000, 'BAR');
END BAR;

Function created.

Statement 4

SELECT COALESCE(FOO, BAR) FROM DUAL

 
COALESCE(FOO,BAR)
1

COALESCE(FOO,BAR)
1

Additional Information