The COALESCE function in SQL is used to return the first non-null expression among its arguments. It is a very useful function for handling null values and providing default values when necessary.
Syntax
COALESCE(expression1, expression2, ..., expressionN)
expression1, expression2, ..., expressionN: These are the expressions to be evaluated. The COALESCEfunction returns the first non-null expression.
Example Usage
Let's consider a table employeeswith the following structure:
employees
emp_id first_name middle_name last_name
1 John NULL Doe
2 Jane A. Smith
3 Jim NULL Brown
Example 1: Handling Null Values
We want to create a full name for each employee. If the middle name is null, we should ignore it.
SELECT emp_id,
first_name,
COALESCE(middle_name, '') AS middle_name,
last_name,
CONCAT(first_name, ' ', COALESCE(middle_name + ' ', ''), last_name) AS full_name
FROM employees;
Result
emp_id first_name middle_name last_name full_name
1 John Doe John Doe
2 Jane A. Smith Jane A. Smith
3 Jim Brown Jim Brown
Example 2: Providing Default Values
We have a table orders where some ordersmight not have a discount applied. We want to calculate the final price, using a default discount of 0 if the discount is null.
orders
order_id product_name price discount
1 Product A 100 10
2 Product B 150 NULL
3 Product C 200 20
SELECT order_id,
product_name,
price,
COALESCE(discount, 0) AS discount,
price - COALESCE(discount, 0) AS final_price
FROM orders;
Result
order_id product_name price discount final_price
1 Product A 100 10 90
2 Product B 150 0 150
3 Product C 200 20 180
Example 3: Using COALESCEwith Multiple Columns
Consider a table contactswhere we want to find the first available contact method (phone, email, address) for each person.
contacts
contact_id phone email address
1 NULL john@example.com 123 Maple St
2 555-1234 NULL NULL
3 NULL NULL 456 Oak St
SELECT contact_id,
COALESCE(phone, email, address, 'No contact available') AS first_contact
FROM contacts;
Result
contact_id first_contact
1 john@example.com
2 555-1234
3 456 Oak St
Explanation
COALESCE(middle_name, ''): Returns the middle name if it's not null; otherwise, it returns an empty string.COALESCE(discount, 0): Returns the discount if it's not null; otherwise, it returns 0.- COALESCE(phone, email, address, 'No contact available'): Checks each contact method in order and returns the first non-null value. If all are null, it returns 'No contact available'.
The COALESCEfunction is very versatile and can be used in various scenarios to handle null values and provide default values, making it a powerful tool for data transformation and cleaning in SQL.
