COALESCE function in oracle with examples

The COALESCE function in Oracle is used to return the first non-NULL expression among its arguments. It is a very useful function for handling NULL values and ensuring that some meaningful data is returned even when some inputs might be NULL.

Syntax

COALESCE(expr1, expr2, ..., exprn)

• expr1, expr2, ..., exprn: These are the expressions to be evaluated. COALESCE will return the first non-NULL expression in this list. If all expressions evaluate to NULL, the COALESCE function will return NULL.

Key Points

• COALESCE can take two or more arguments.
• The expressions can be of any data type, but they must all be compatible.
• It stops evaluating expressions as soon as it finds the first non-NULL expression.

Examples

Basic Usage

Suppose you have a table employees with the columns first_name, middle_name, and last_name. You want to get the first non-NULL name from these columns for each employee.

SELECT employee_id,
       COALESCE(first_name, middle_name, last_name) AS primary_name
FROM employees;

Handling NULL Values in Aggregation

If you have a sales table and you want to sum up the sales but treat NULL values as 0, you can use COALESCE:

SELECT COALESCE(SUM(sales_amount), 0) AS total_sales
FROM sales;

Providing Default Values

You can use COALESCE to provide a default value for a column that might contain NULL values.

SELECT employee_id,
       COALESCE(phone_number, 'N/A') AS contact_number
FROM employees;

Using COALESCE in INSERT Statements

When inserting data into a table, you can use COALESCE to ensure that NULL values are replaced with a default value.

INSERT INTO employees (employee_id, first_name, last_name, phone_number)
VALUES (1, 'John', 'Doe', COALESCE(NULL, 'No Phone'));

Comparison with NVL

COALESCE is similar to NVL, but more flexible:

• NVL(expr1, expr2): If expr1 is NULL, NVL returns expr2. Otherwise, it returns expr1.
• COALESCE(expr1, expr2, ..., exprn): Returns the first non-NULL expression in the list.

Example using NVL:

SELECT employee_id,
       NVL(middle_name, 'N/A') AS middle_name
FROM employees;

Example using COALESCE (which can handle more than two expressions):

SELECT employee_id,

       COALESCE(middle_name, first_name, last_name, 'N/A') AS name

FROM employees;

Conclusion

The COALESCE function is a powerful tool for handling NULL values in Oracle SQL. It can be used to provide default values, ensure non-NULL results in expressions, and simplify handling of multiple potential NULL values in a list of expressions. It is particularly useful in complex queries and data transformation processes where ensuring non-NULL values is critical.