COALESCE() Function

The COALESCE() function in Oracle is used to return the first non-null value from a list of expressions. It’s a powerful function often used in data cleaning, conditional logic, and reporting.

Syntax

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

It returns the first non-null expression in the list.
If all expressions are null, it returns NULL.

Basic Example

SELECT COALESCE(NULL, NULL, 'Hello', 'World') AS result
FROM dual;

Output:

RESULT
Hello

Explanation: The first two values are NULL, so it returns 'Hello' (the first non-null value).

When to Use `COALESCE` in Oracle

1. Replace NULLs with a Default Value

SELECT employee_id, salary, COALESCE(commission_pct, 0) AS commission
FROM employees;

If commission_pct is NULL, it will be replaced by 0.

2. Return the First Available Column Value

If a person can have an email from multiple sources, and we want the most prioritized available one:

SELECT name, COALESCE(work_email, personal_email, backup_email, 'No Email') AS preferred_email
FROM contacts;

Returns the first available email in the preferred order.
If all emails are null, returns 'No Email'.

3. Use in `CASE`-like Logic

SELECT name, 
       COALESCE(nickname, username, 'Guest') AS display_name
FROM users;

Works like a simpler form of CASE when you just want the first available value.

Important Notes

All expressions in COALESCE() should be of compatible data types.
It evaluates the expressions left to right, and stops at the first non-null.
If all arguments are NULL, it returns NULL.

Difference Between `COALESCE` and `NVL`

Feature	`COALESCE(expr1, expr2)`	`NVL(expr1, expr2)`
Number of args	2 or more	Exactly 2
Short-circuits	Yes	Yes
Return data type	Highest precedence type	Type of first argument
ANSI standard	Yes	No (Oracle-specific)

Example:

-- COALESCE
SELECT COALESCE(NULL, 'X', 'Y') FROM dual;  -- returns 'X'

-- NVL
SELECT NVL(NULL, 'X') FROM dual;           -- returns 'X'

Use COALESCE when you have more than 2 values to check or want ANSI SQL compliance.

Real-World Example

Assume a students table:

CREATE TABLE students (
  student_id NUMBER,
  name VARCHAR2(50),
  test1_score NUMBER,
  test2_score NUMBER,
  test3_score NUMBER
);

INSERT INTO students VALUES (1, 'Alice', NULL, NULL, 85);
INSERT INTO students VALUES (2, 'Bob', 75, NULL, NULL);
INSERT INTO students VALUES (3, 'Charlie', NULL, NULL, NULL);

Query: Get the first non-null score

SELECT name,
       COALESCE(test1_score, test2_score, test3_score, 0) AS first_score
FROM students;