MySQL COALESCE Function

The COALESCE function in MySQL returns the first non-NULL value from a list of expressions. It is commonly used for handling NULL values and providing default values.

Syntax

COALESCE(expr1, expr2, ..., exprN)

expr1, expr2, ..., exprN: A list of expressions to evaluate. The function returns the first non-NULL expression in this list.

How It Works

The function evaluates the expressions in the order they are provided.
It returns the first non-NULL value.
If all expressions are NULL, the function returns NULL.

Examples

1. Basic Usage

SELECT COALESCE(NULL, NULL, 'default_value') AS result;

Output:

+--------------+
| result       |
+--------------+
| default_value|
+--------------+

Explanation: Since the first two expressions are NULL, the function returns 'default_value'.

2. Handling NULL Values in Columns

Suppose you have a table users:

user_id	first_name	nickname
1	John	NULL
2	NULL	Johnny
3	NULL	NULL

Query to get the first non-NULL value between first_name and nickname:

SELECT user_id, COALESCE(first_name, nickname, 'No Name') AS display_name
FROM users;

Output:

+---------+--------------+
| user_id | display_name |
+---------+--------------+
| 1       | John         |
| 2       | Johnny       |
| 3       | No Name      |
+---------+--------------+

3. Using `COALESCE` for Calculations

SELECT COALESCE(NULL, 10, 20) AS result;

Output:

+--------+
| result |
+--------+
| 10     |
+--------+

4. Combine with Other Functions

SELECT COALESCE(SUM(salary), 0) AS total_salary
FROM employees;

Explanation: If all salaries are NULL, the function will return 0.

Practical Use Cases

Default Values: Use COALESCE to substitute NULL values with defaults.

SELECT COALESCE(email, 'not_provided@example.com') AS contact_email
FROM users;

Data Cleaning: Replace NULLs in datasets to maintain consistency.
Combining Columns: Display meaningful values by combining multiple fields.

Notes

You can provide any number of expressions to COALESCE.
The data type of the result is determined by the highest precedence data type among the expressions.
If all expressions are NULL, the result is NULL.

Conclusion

The COALESCE function is a powerful and flexible tool for handling NULL values in MySQL. It simplifies queries by providing default or alternative values when working with potentially incomplete data.

Souy Soeng

MySQL COALESCE Function