Oracle COALESCE Function: A Simple Guide

The COALESCE function in Oracle SQL is a powerful and flexible function used to handle NULL values. Its job is to return the first non-NULL expression it finds in a list of arguments.

Think of it as a "waterfall" or a "fallback" system. It checks the first value. If it's not NULL, it returns that value. If it is NULL, it moves to the second value, and so on.

This is a more powerful version of the NVL function, which can only take two arguments. COALESCE can take many.

What is the COALESCE Function in Oracle?

The COALESCE(expr1, expr2, ...) function evaluates its arguments in order and returns the first one that is not NULL. If all arguments in the list are NULL, then COALESCE returns NULL.

This is extremely useful for:

• Providing default values for a column that might be NULL.
• Finding the first available contact method (e.g., mobile phone, then home phone, then work phone).
• Selecting a primary, secondary, or tertiary price for a product.

COALESCE Function Syntax

The syntax for COALESCE is:

COALESCE(expr1, expr2, ..., exprN)

Let's break that down:

• expr1: The first expression to check. If it's not NULL, the function returns this value and stops.
• expr2: The second expression to check. This is only evaluated if expr1 is NULL.
• ...exprN: You can have many expressions in the list.

Oracle COALESCE Function Examples

Here are two practical examples of how to use COALESCE.

Example 1: Providing a Default "Status" with COALESCE

This is a very common use case. Imagine you have a table where a status column can be NULL, but for your report, you want to display "Unknown" instead of a blank.

Query:

-- We'll simulate a table 'tasks'
WITH tasks AS (
  SELECT 'In Progress' AS status FROM DUAL UNION ALL
  SELECT NULL AS status FROM DUAL UNION ALL
  SELECT 'Completed' AS status FROM DUAL
)
SELECT
  status AS "Original_Status",
  COALESCE(status, 'Unknown') AS "Cleaned_Status"
FROM tasks;

Result: (The NULL value is replaced by 'Unknown'.)

Original_Status Cleaned_Status
--------------- ---------------
In Progress     In Progress
(NULL)          Unknown
Completed       Completed

Example 2: Finding the First Available Contact Number with COALESCE

This example shows the real power of COALESCE with multiple arguments. We want to find the first available phone number for a user, checking mobile_phone, then home_phone, then work_phone.

Query:

-- We'll simulate a 'users' table
WITH users AS (
  SELECT 'Alice' AS name, '555-1234' AS mobile_phone, '555-5678' AS home_phone FROM DUAL UNION ALL
  SELECT 'Bob' AS name, NULL AS mobile_phone, '555-8888' AS home_phone FROM DUAL UNION ALL
  SELECT 'Charlie' AS name, NULL AS mobile_phone, NULL AS home_phone FROM DUAL
)
SELECT
  name,
  COALESCE(mobile_phone, home_phone, 'No Phone Found') AS "Contact_Number"
FROM users;

Result: (The function finds the first non-NULL phone number in the list for each row.)

NAME      Contact_Number
-------   ---------------
Alice     555-1234
Bob       555-8888
Charlie   No Phone Found