The COALESCE function is used in SQL to return the first non-null value from a list of expressions. When using COALESCE with multiple columns, you can pass the column names as arguments, and it will evaluate them in the order you specify, returning the first one that is not null.
Here’s the basic syntax:
COALESCE(expression1, expression2, ..., expressionN)
Example Usage
Suppose you have a table named employees with the following columns: name, nickname, and preferred_name. You want to select the first non-null name for each employee. Here's how you can do it:
SELECT
COALESCE(preferred_name, nickname, name) AS effective_name
FROM
employees;
In this example:
- The COALESCE function will check preferred_name first. If it's not null, it returns that value.
- If preferred_name is null, it checks nickname.
- If both preferred_name and nickname are null, it will return name.
Additional Notes
- You can use COALESCE with any number of expressions, not just columns. It can also be used with literals, expressions, or function results.
- COALESCE is very useful for handling optional fields or creating fallback values in data processing.
- If all the arguments are null, COALESCE will return null.
Full Query Example
Here’s a more complete example that selects the effective name along with other relevant information from the employees table:
SELECT
employee_id,
COALESCE(preferred_name, nickname, name) AS effective_name,
department
FROM
employees;
This query retrieves each employee's ID, their effective name (the first non-null name), and their department from the employees table.