What is COALESCE() function?

The COALESCE() function in SQL (including MySQL) is used to return the first non-NULL value from a list of expressions. It’s very handy when you want to handle NULL values in queries.

Syntax:

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

• It evaluates the expressions from left to right.
• Returns the first non-NULL value.
• If all values are NULL, it returns NULL.

---

Examples:

1. Basic Example:

SELECT COALESCE(NULL, NULL, 'Hello', 'World');

Result:

Hello

Explanation: It skips the NULLs and returns the first non-NULL value 'Hello'.

---

2. With Table Data:
   Suppose you have a table employees:

id	first_name	middle_name	last_name
1	John	NULL	Doe
2	Jane	Ann	Smith
3	Bob	NULL	NULL

You want to get a full name, using the middle name if available, otherwise skip it:

SELECT first_name, COALESCE(middle_name, '') AS middle, last_name
FROM employees;

Result:

first_name	middle	last_name
John		Doe
Jane	Ann	Smith
Bob		NULL

---

3. Replacing NULL with a Default Value:

SELECT COALESCE(last_name, 'No Last Name') AS last_name
FROM employees;

Result:

last_name
Doe
Smith
No Last Name

---

✅ Key Points:

• COALESCE() can take two or more arguments.
• It’s ANSI SQL standard, so works in most databases.
• Useful for handling NULLs without complicated CASE statements.