How to Use COALESCE in MySQL: Syntax and Examples

In MySQL, the COALESCE function returns the first non-NULL value from a list of expressions. It is useful to replace NULLs with a default value or to select the first available value among multiple columns.

Syntax

The COALESCE function takes two or more arguments and returns the first argument that is not NULL.

Syntax parts:

• COALESCE(expr1, expr2, ..., exprN): List of expressions to check.
• Returns the first non-NULL expression from left to right.
• If all expressions are NULL, it returns NULL.

sql

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

Example

This example shows how COALESCE returns the first non-NULL value from multiple columns in a table.

sql

CREATE TABLE employees (
  id INT,
  first_name VARCHAR(50),
  middle_name VARCHAR(50),
  last_name VARCHAR(50)
);

INSERT INTO employees VALUES
(1, 'John', NULL, 'Doe'),
(2, NULL, 'Michael', 'Smith'),
(3, NULL, NULL, NULL);

SELECT id, 
       COALESCE(first_name, middle_name, last_name, 'No Name') AS display_name
FROM employees;

Output

id | display_name
---|--------------
1  | John
2  | Michael
3  | No Name

Common Pitfalls

Common mistakes when using COALESCE include:

• Using it with only one argument, which is unnecessary.
• Expecting it to replace all NULLs in a column instead of just returning the first non-NULL value.
• Confusing COALESCE with IFNULL, which only takes two arguments.

sql

/* Wrong: COALESCE with one argument does nothing useful */
SELECT COALESCE(first_name) FROM employees;

/* Right: Provide multiple arguments to handle NULLs */
SELECT COALESCE(first_name, 'Unknown') FROM employees;

Quick Reference

Feature	Description
Function	Returns first non-NULL value from arguments
Arguments	Two or more expressions
Returns	First non-NULL expression or NULL if all are NULL
Use case	Replace NULLs with default values or select first available value
Difference from IFNULL	IFNULL takes exactly two arguments; COALESCE can take many

Key Takeaways

Use COALESCE to return the first non-NULL value from multiple expressions.

Provide two or more arguments to COALESCE for it to work properly.

COALESCE can replace NULLs with default values in queries.

It differs from IFNULL by supporting multiple arguments.

If all arguments are NULL, COALESCE returns NULL.