🧚 Listen! Beekeeper Studio is a fast, modern, and open source MySQL GUI Download
July 24, 2025 By Matthew Rathbone

Handling NULL values effectively is a common challenge in SQL. MySQL provides various functions to deal with NULLs, one of which is the COALESCE function. In this article, we’ll explore how to use COALESCE in MySQL and understand its practical applications.

The COALESCE function is part of MySQL’s robust set of functions for handling NULL values and data manipulation.

Before you keep reading, I just wanted to let you know about our modern and fast MySQL database manager, Beekeeper Studio. Its data grid shows NULLs as (NULL), so it’s easy to see which values COALESCE fills in, and it’s free to use for MySQL, download it here.

What is COALESCE?

The COALESCE function returns the first non-NULL value in a list. It’s useful when you want to ensure you?re working with actual data rather than unknowns.

Syntax

COALESCE(val1, val2, ..., valN)
  • val1, val2, …, valN are the values to evaluate.
  • The return type will be the same as the first non-NULL value in the list.

Basic Usage of COALESCE

Let’s start with a simple example to see how COALESCE works:

SELECT COALESCE(NULL, NULL, 'Apples', 'Bananas') AS Result;

Expected Output

+--------+
| Result |
+--------+
| Apples |
+--------+

In this example, COALESCE returns ‘Apples’ because it is the first non-NULL value.

Handling NULLs in Table Data

Imagine you have an employees table with optional contact columns:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(15)
);

INSERT INTO employees (name, email, phone) VALUES
('John Doe', 'john@example.com', NULL),
('Jane Smith', NULL, '123-456-7890'),
('Alice Johnson', NULL, NULL);

Query with COALESCE

You want to retrieve the preferred contact method for each employee. Here’s how COALESCE can help:

SELECT 
    name, 
    COALESCE(email, phone, 'No Contact Available') AS contact_method
FROM 
    employees;

Expected Output

+---------------+--------------------+
| name          | contact_method     |
+---------------+--------------------+
| John Doe      | john@example.com   |
| Jane Smith    | 123-456-7890       |
| Alice Johnson | No Contact Available |
+---------------+--------------------+

In this case, the query checks email first, then phone, and defaults to ‘No Contact Available’ if both are NULL.

Combining with Other SQL Functions

COALESCE can be combined with other functions to transform data more robustly. Consider the following example which uses IFNULL along with COALESCE:

SELECT 
    id,
    name,
    COALESCE(email, phone, 'No Contact') AS contact,
    IFNULL(phone, 'N/A') AS phone_number
FROM 
    employees;

Expected Output

+----+---------------+--------------------+-------------+
| id | name          | contact            | phone_number|
+----+---------------+--------------------+-------------+
| 1  | John Doe      | john@example.com   | N/A         |
| 2  | Jane Smith    | 123-456-7890       | 123-456-7890|
| 3  | Alice Johnson | No Contact         | N/A         |
+----+---------------+--------------------+-------------+

Here, COALESCE determines the best contact method while IFNULL provides a default value specifically for the phone_number column.

Conclusion

Using COALESCE in MySQL is a powerful way to handle NULL values, ensuring your queries produce meaningful results even when some data is missing. It provides flexibility in selecting the first non-NULL value across columns, helping maintain data integrity and usability.

For more advanced MySQL topics, check out our guides on MySQL Full Outer Join and MySQL Subtraction Techniques. Learn to harness SQL functions to build robust applications and insightful data analyses.

If you’re working with complex MySQL queries, Beekeeper Studio provides an intuitive interface for testing and debugging your COALESCE expressions with real-time results.

Beekeeper Studio Is A Free & Open Source MySQL GUI

I’ve been using Beekeeper Studio for my MySQL work lately, and it has quickly become one of my favorite database tools. The interface is clean, modern, and distraction-free, making it easy to focus on queries without the clutter you get in some other tools. - Surendra Singh

Beekeeper Studio is a fast, intuitive and easy to use MySQL client. It supports loads of other databases too, and works great on Windows, Mac and Linux.

Beekeeper's Linux version is 100% full-featured, no cut corners, no feature compromises.

What MySQL Users Say About Beekeeper Studio

Beekeeper Beekeeper Still a Newbie

I chose this program after years of using phpMyAdmin—it feels like stepping into a world of higher quality; here, I truly felt that the user is genuinely cared for. The concept itself, the implementation, the practical experience—I am sincerely grateful to Matthew for all of it.

Alex M

Fabulous tool, the most practical IDE I’ve found to date for Postgres and MySql db admin - it just works, no fuss :-)

Ready to Improve Your MySQL Workflow?

download Download Free