COALESCE

 The COALESCE function in Oracle is a powerful and flexible function used to return the first non-NULL value in a list of expressions. It is especially useful when dealing with NULL values and you want to replace them with a default value.

 

1. Syntax of COALESCE Function

The syntax of the COALESCE function is:

COALESCE(expression_1, expression_2, ..., expression_n)

• expression_1, expression_2, ..., expression_n: These are the expressions or columns that you want to evaluate. The function will return the first non-NULL expression from this list. If all the expressions are NULL, the function returns NULL.

 

2. How COALESCE Works

• The COALESCE function checks each expression in the order they are listed.
• It returns the first non-NULL value it encounters.
• If all expressions are NULL, it returns NULL.

Example:

SELECT COALESCE(NULL, NULL, 'apple', 'banana') FROM dual;

-- Output: 'apple'

In the example, the function checks the first two values (NULL), and then returns 'apple' because it's the first non-NULL value.

 

3. Key Points to Understand About COALESCE

·        COALESCE vs NVL: Both COALESCE and NVL functions deal with NULL values, but COALESCE can handle more than two arguments. NVL, on the other hand, only takes two arguments. COALESCE returns the first non-NULL value from a list of arguments, whereas NVL replaces NULL with a specific value when only two arguments are provided.

Example of NVL:

SELECT NVL(column_name, 'default_value') FROM table_name;

Example of COALESCE:

SELECT COALESCE(column1, column2, 'default_value') FROM table_name;

·        Order of Evaluation: COALESCE evaluates expressions in order, so if the first expression is not NULL, it will return that value without checking the remaining ones.

·        Return Type: The return type of COALESCE is the data type of the first non-NULL expression. If all expressions are of different data types, the result is implicitly cast to the data type that can accommodate the largest value or is compatible with all expressions.

---

4. Examples of Using COALESCE

a. Handling NULL values in Columns

Suppose you have a table with employees' salary details, and some employees have NULL values in their salary column. You can use COALESCE to provide a default salary value where it is missing.

Example:

SELECT employee_id, COALESCE(salary, 50000) AS salary

FROM employees;

In this example, if the salary column contains NULL for an employee, the query will return 50000 as the default salary.

b. Using COALESCE with Multiple Columns

If you have multiple columns that may contain NULL values and you want to return the first non-NULL value, you can use COALESCE to check these columns in sequence.

Example:

SELECT employee_id, COALESCE(phone_number, mobile_number, 'Not Available') AS contact

FROM employees;

Here, the query checks the phone_number first. If it's NULL, it checks the mobile_number. If both are NULL, it returns 'Not Available'.

c. Replacing NULL with Default Value in Expressions

You can use COALESCE in any expression, including when working with arithmetic operations or concatenations.

Example:

SELECT product_name, COALESCE(discount, 0) * price AS discounted_price

FROM products;

In this case, if the discount is NULL, the function returns 0, so the discount calculation does not result in a NULL value.

 

5. Performance Considerations

• Efficiency: COALESCE is generally more efficient than using multiple CASE statements or IF conditions to check for NULL values, especially when dealing with multiple columns.
• Index Usage: When using COALESCE in WHERE clauses or JOIN conditions, make sure the expressions involved are indexed, as it may affect query performance.
• Avoiding NULL in Calculations: One of the primary benefits of using COALESCE is to prevent NULL values from affecting calculations, aggregations, or concatenations.

 

6. Example Queries Using COALESCE

Example 1: Returning the First Non-NULL Value from Multiple Columns

Let's say you have a table with employee details and the columns for email, phone_number, and mobile_number. You want to return the first non-NULL value from these columns as the primary contact.

SELECT employee_id, COALESCE(email, phone_number, mobile_number) AS contact_info

FROM employees;

In this example, if email is NULL, the function will check phone_number next, and if that's also NULL, it will return mobile_number.

Example 2: Using COALESCE in Aggregations

When using COALESCE with aggregate functions, it ensures that NULL values are handled properly.

SELECT department_id, COALESCE(SUM(salary), 0) AS total_salary

FROM employees

GROUP BY department_id;

This query will return the sum of salaries per department. If no employees in a department have a salary (i.e., all are NULL), the query will return 0.

Example 3: Substituting Missing Data in Reports

In reporting applications, you often need to replace missing or NULL values with user-friendly data, such as N/A or Not Provided.

SELECT employee_id, COALESCE(address, 'N/A') AS address

FROM employees;

In this case, if the address field contains a NULL value, it will return 'N/A' instead.