The COALESCE function in SQL is used to handle NULL values by returning the first non-NULL value in a list of expressions. This is particularly useful when you want to provide default values for NULL fields in your result set.
The syntax for COALESCE is as follows:
COALESCE(expression1, expression2, ..., expressionN)
Where expression1, expression2, ... are the values you want to check. The function will return the first of these expressions that is not NULL. If all expressions are NULL, then COALESCE will return NULL.
Example Usage
1. Basic Use Case:
Imagine you have a table called employees with columns id, name, and bonus, where bonus can be NULL if an employee hasn't received one. You want to display the bonus, but if it's NULL, you want to show 0 as a default value.
SELECT
id,
name,
COALESCE(bonus, 0) AS bonus
FROM
employees;
In this example, if the bonus is NULL, the query will return 0 instead.
2. Multiple Columns:
You can also use COALESCE to check multiple fields for non-NULL values. For instance, consider you have another column salary that may also be NULL, and you want to show the first non-NULL value between bonus and salary.
SELECT
id,
name,
COALESCE(bonus, salary, 0) AS compensation
FROM
employees;
Here, the query will return bonus if it has a value, otherwise, it will return salary, and if both are NULL, it will return 0.
3. Using COALESCE in WHERE Clauses:
You can also use COALESCE in a WHERE clause. For example, you might want to filter only those employees who have either a bonus or a salary greater than 0.
SELECT
id,
name
FROM
employees
WHERE
COALESCE(bonus, salary, 0) > 0;
Summary
The COALESCE function is a powerful tool in SQL to manage NULL values and provide default values in a result set. It helps ensure that your query results are more informative and user-friendly by substituting NULLs with meaningful values.