COALESCE() Function in Oracle

by

In Oracle Database, the COALESCE() function returns the first non-null expression the expression list.

Syntax

The syntax goes like this:

COALESCE(expr [, expr ]...)

At least two expressions must be passed.

Example

Here’s an example to demonstrate:

SELECT COALESCE(null, 7)
FROM DUAL;

Result:

7

Here are some more examples:

SET NULL 'null';
SELECT
    COALESCE(null, null, 1, 2, 3) AS "r1",
    COALESCE(1, null, 2, 3) AS "r2",
    COALESCE(null, 3, 2, 1) AS "r3",
    COALESCE(1, 2, 3, null) AS "r4",
    COALESCE(null, null) AS "r5"
FROM DUAL;

Result:

   r1    r2    r3    r4      r5 
_____ _____ _____ _____ _______ 
    1     1     3     1 null

We can see that COALESCE() returns null when all arguments are null.

Regarding the first line SET NULL 'null';, I added that so that my SQLcl session returns null whenever the result is null.

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Database Example

Suppose we run the following query:

SET NULL 'null';
SELECT 
    LOCATION_ID,
    STATE_PROVINCE
FROM LOCATIONS
ORDER BY LOCATION_ID ASC
FETCH FIRST 6 ROWS ONLY;

Result:

   LOCATION_ID      STATE_PROVINCE 
______________ ___________________ 
          1000 null                
          1100 null                
          1200 Tokyo Prefecture    
          1300 null                
          1400 Texas               
          1500 California

We can see that several of the rows are null in the STATE_PROVINCE column.

Here’s the query again, except this time we use COALESCE() against the STATE_PROVINCE column:

SELECT 
    LOCATION_ID,
    COALESCE(STATE_PROVINCE, 'N/A')
FROM LOCATIONS
ORDER BY LOCATION_ID ASC
FETCH FIRST 6 ROWS ONLY;

Result:

   LOCATION_ID    COALESCE(STATE_PROVINCE,'N/A') 
______________ _________________________________ 
          1000 N/A                               
          1100 N/A                               
          1200 Tokyo Prefecture                  
          1300 N/A                               
          1400 Texas                             
          1500 California

So we used COALESCE() to return N/A whenever a null value occurred.

COALESCE() vs CASE

The following:

COALESCE(expr1, expr2)

Is equivalent to this:

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Invalid Argument Count

Calling the function without passing any arguments results in an error:

SELECT COALESCE()
FROM DUAL;

Result:

SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"