SQL Interview Questions

The COALESCE function in SQL is used to return the first non-null value from a list of expressions. It is particularly useful for handling null values and providing default values in queries.

Syntax

COALESCE(expression1, expression2, ..., expressionN)

• expression1, expression2, ..., expressionN: These are the expressions to be evaluated. The COALESCE function will return the first non-null value from this list. If all expressions are null, COALESCE will return null.

How COALESCE Works

1. Evaluate Expressions: The function evaluates each expression in the order they are listed.
2. Return First Non-Null Value: As soon as it finds the first non-null value, it returns that value and stops evaluating the remaining expressions.
3. Return Null if All are Null: If all expressions are null, the function returns null.

Examples

Example 1: Basic Usage

Scenario: You have a table employees where some employees have a middle name, but others do not. You want to display either the middle name or a placeholder text if the middle name is null.

SELECT first_name, 
       COALESCE(middle_name, 'No Middle Name') AS middle_name
FROM employees;

Here, if middle_name is null, the result will be 'No Middle Name'.

Example 2: Handling Multiple Potential Nulls

Scenario: You have a table orders where some orders may have different columns for discount. You want to display the discount if available, or a default discount if not.

SELECT order_id,
       COALESCE(discount_percentage, discount_amount, 0) AS effective_discount
FROM orders;

Here, the function will return discount_percentage if it’s not null; if discount_percentage is null, it will return discount_amount if it’s not null; otherwise, it will return 0.

Example 3: Use in Expressions

Scenario: You want to calculate the total price of orders considering a potential discount. If the discount is null, it should be treated as 0.

SELECT order_id,
       order_amount,
       COALESCE(discount_percentage, 0) AS discount_percentage,
       order_amount * (1 - COALESCE(discount_percentage, 0) / 100) AS final_amount
FROM orders;

Here, if discount_percentage is null, it will be treated as 0 in the final calculation.

Example 4: With Aggregates

Scenario: You want to display the maximum value from a column, but if all values are null, you want to return a default value.

SELECT COALESCE(MAX(column_name), 'Default Value') AS max_value
FROM table_name;

Here, if MAX(column_name) returns null (i.e., if all values in column_name are null), the result will be 'Default Value'.

Use Cases

1. Default Values: Provide default values when dealing with potentially null columns.
2. Data Cleaning: Replace null values with more meaningful data in reports and queries.
3. Conditional Logic: Simplify conditional logic in queries by handling null values more gracefully.

Conclusion

The COALESCE function is a versatile and useful tool in SQL for managing null values and providing default values. By returning the first non-null value from a list of expressions, it helps in handling cases where data might be missing or incomplete, thus enabling more robust and flexible querying and reporting.