Oracle COALESCE Function

Summary: in this tutorial, you will learn how to use the Oracle COALESCE() function to return the first non-null arguments in a list.

Introduction to the Oracle COALESCE() function

The Oracle COALESCE() function accepts a list of arguments and returns the first one that evaluates to a non-null value.

The following illustrates the syntax of the Oracle COALESCE() function:

COALESCE(e1, e2, ..., en)
Code language: SQL (Structured Query Language) (sql)

In this syntax, the COALESCE() function returns the first non-null expression in the list. It requires at least two expressions. In case all expressions evaluate to null, the function returns null.

The following example returns one because it is the first non-null argument:

SELECT
  COALESCE(NULL,1) -- return 1
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

    RESULT
----------
         1

The following example returns null because all arguments are null:

SELECT
  COALESCE(NULL,NULL,NULL) -- return NULL
FROM
  dual; 
Code language: SQL (Structured Query Language) (sql)

Return type

If all arguments have the same data type, the COALESCE() function returns a value of that data type.

If the arguments have different data types, the COALESCE() function implicitly converts all arguments to the data type of the first non-null argument. In case the conversion fails, then Oracle issues an error.

The following example returns a value with character type because all arguments are characters.

SELECT
  COALESCE(NULL,'A','B') result
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

RESULT
-------
ACode language: SQL (Structured Query Language) (sql)

Similarly, the following example returns a value with numeric type:

SELECT
  COALESCE(NULL,1,2) result
FROM
  dual;  
Code language: SQL (Structured Query Language) (sql)

    RESULT
----------
         1

However, the following example uses arguments of different types for the COALESCE() function:

SELECT
  COALESCE(NULL,1,'A')
FROM
  dual;  
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
Code language: SQL (Structured Query Language) (sql)

Because the COALESCE() function tried to convert the character type of the third argument to a numeric type of the second argument that resulted in an error.

Short-circuit evaluation

The COALESCE() function uses short-circuit evaluation. It means that the function stops evaluating the remaining expressions once it finds the first one evaluates to a non-null value.

Consider the following example:

SELECT
  COALESCE(1 + 1, 1/0)
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

In this example, the COALESCE() function only evaluated the first expression because the result of the first expression was two (1+1). It did not evaluate the second expression (1/0). If it had done so, Oracle would have issued the division by zero error.

Oracle COALESCE() example

Let’s set up a sample table for testing.

Suppose you have to record emergency contacts of employees with various phone numbers: work phone, home phone, and cell phone. To do it, you create a new table named emergency_contacts as follows:

CREATE  TABLE emergency_contacts  (
    contact_id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
    employee_id  NUMBER NOT NULL,
    first_name   VARCHAR2(100) NOT NULL,
    last_name    VARCHAR2(100) NOT NULL,
    relationship VARCHAR2(100),
    home_phone   VARCHAR2(25),
    work_phone   VARCHAR2(25),
    cell_phone   VARCHAR2(25),
    PRIMARY KEY (contact_id),
    FOREIGN KEY (employee_id) 
    REFERENCES employees(employee_id) ON DELETE CASCADE
); 
Code language: SQL (Structured Query Language) (sql)

The following statements insert some emergency contacts into the table:

INSERT INTO emergency_contacts ( employee_id, first_name, last_name, relationship, home_phone, work_phone, cell_phone )
VALUES ( 1,
         'Mary',
         'Bailey',
         'Wife',
         NULL,
         '515.123.4568',
         '515.123.4569' );


INSERT INTO emergency_contacts ( employee_id, first_name, last_name, relationship, home_phone, work_phone, cell_phone )
VALUES ( 2,
         'John',
         'Rivera',
         'Husband',
         NULL,
         NULL,
         '515.123.3563' );


INSERT INTO emergency_contacts ( employee_id, first_name, last_name, relationship, home_phone, work_phone, cell_phone )
VALUES ( 3,
         'Joan',
         'Cooper',
         'Mother',
         NULL,
         NULL,
         NULL );Code language: SQL (Structured Query Language) (sql)

Some contacts have only work phone while others may have a home phone and cell phone or don’t have any phone number at all.

The following query retrieves the employees and their emergency contacts with phone numbers:

SELECT
  e.first_name  || ' '  || e.last_name employee,
  c.first_name  || ' '  || c.last_name contact,
  relationship,
  COALESCE(home_phone, work_phone, cell_phone, 'N/A') phone
FROM
  emergency_contacts c
INNER JOIN employees e  USING (employee_id); 
Code language: SQL (Structured Query Language) (sql)

In this example, we used the COALESCE() function to select home phone, work phone, or cell phone if each is available. In case no phone numbers are available, we returned N/A  literal string which stands for not available.

The following shows the result of the query:

Oracle COALESCE() and CASE expression

You can use the COALESCE() function instead of the longer CASE expression when it comes to test for null in multiple expressions. The COALESCE() function is more concise than a CASE expression that involves null evaluations.

If you check for NULL in two expressions, the COALESCE() function is equivalent to the CASE expression.

For example, the following COALESCE() function:

COALESCE(e1, e2)
Code language: SQL (Structured Query Language) (sql)

is equivalent to:

CASE
WHEN e1 IS NOT NULL THEN
  e1
ELSE
  e2
END
Code language: SQL (Structured Query Language) (sql)

Likewise,

COALESCE(e1, e2,..., en);
Code language: SQL (Structured Query Language) (sql)

is equivalent to

CASE
WHEN e1 IS NOT NULL THEN
  e1 
ELSE
  COALESCE (e2, ..., en)
END
Code language: SQL (Structured Query Language) (sql)

Oracle COALESCE() vs. NVL()

The COALESCE() function is a part of SQL ANSI-92 standard while NVL() function is Oracle specific.

In case of two expressions, the COALESCE() function and NVL() seems to be similar but their implementations are different. See the following statements:

SELECT
  COALESCE(1,NULL)
FROM
  dual;

SELECT
  NVL(1,NULL)
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

Both statements return the same result which is one. However, the COALESCE() function only evaluates the first expression to determine the result while the NVL() function evaluates both expressions.

Let’s see the following example:

SELECT
  COALESCE(1,1/0)
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

The statement above returned 1 whereas the following example causes an error:

SELECT
  NVL(1,1/0)
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

The error is:

"ORA-01476: divisor is equal to zero"Code language: SQL (Structured Query Language) (sql)

Because the NVL() function evaluated the second expression 1/0 that causes the error.

In this tutorial, you have learned how to use the Oracle COALESCE() function to return the first non-null expression in a list of expressions.