SQL COALESCE Function: Complete Guide with Interactive Examples

When working with databases, dealing with NULL values is one of the most common challenges. Whether you’re building reports, cleaning data, or creating user-friendly displays, you need a reliable way to handle missing information. That’s where the SQL COALESCE function comes in.

The COALESCE function is your go-to tool for handling NULL values in SQL. It evaluates a list of values and returns the first non-NULL value it finds. Think of it as a safety net that ensures your queries always return something meaningful, even when data is missing.

What is COALESCE?

COALESCE is a SQL function that takes multiple arguments and returns the first non-NULL value. If all arguments are NULL, it returns NULL. The syntax is straightforward:

COALESCE(value1, value2, value3, ...)

The function evaluates the arguments from left to right and returns as soon as it encounters a non-NULL value. This makes it perfect for providing fallback values when data might be missing.

Basic COALESCE Example

Let’s start with a simple scenario. Imagine you have a users table where some users have nicknames and others don’t. You want to display either their nickname or their full name:

SELECT
  id,
  name,
  COALESCE(nickname, name) as display_name
FROM users;

In this query, if a user has a nickname, that’s what gets displayed. If the nickname is NULL, the function returns the name instead. Simple and effective.

Try it yourself:

[[ error ]]

[[ testData.solution ]]

COALESCE with Multiple Fallback Values

One of COALESCE’s most powerful features is its ability to handle multiple fallback values. You’re not limited to just two options—you can chain as many as you need.

Consider an e-commerce scenario where products might have a sale price, a regular price, or no price at all (perhaps for upcoming products). You want to show the sale price if available, otherwise the regular price, and if neither exists, display $0.00:

SELECT
  product_id,
  name,
  COALESCE(sale_price, regular_price, 0.00) as price
FROM products;

This query evaluates three possibilities in order:

1. First, check if there’s a sale_price
2. If that’s NULL, use the regular_price
3. If that’s also NULL, default to 0.00

Try it yourself:

[[ error ]]

[[ testData.solution ]]

COALESCE in Practice: Data Cleaning

COALESCE really shines when you’re cleaning data or creating derived fields. Here’s a real-world example: generating email addresses for employees who don’t have one recorded.

Suppose you have an employees table with first names, last names, and optional email addresses. You want to ensure every employee has an email displayed, even if it’s just a generated placeholder:

SELECT
  employee_id,
  first_name || ' ' || last_name as full_name,
  COALESCE(
    email,
    LOWER(first_name) || '.' || LOWER(last_name) || '@company.com'
  ) as email
FROM employees;

This query uses COALESCE to check if an email exists. If not, it constructs a standardized email format from the employee’s name. This is particularly useful for:

• Report generation: Ensuring all records display properly
• Data migration: Creating default values during system transitions
• User interfaces: Showing meaningful placeholders instead of NULL

Try it yourself:

[[ error ]]

[[ testData.solution ]]

COALESCE vs CASE WHEN

You might be wondering: when should I use COALESCE versus a CASE statement? Both can handle NULL values, but they serve different purposes.

Use COALESCE when:

• You simply need the first non-NULL value from a list
• Your logic is straightforward (if NULL, use this instead)
• You want cleaner, more readable code

Use CASE WHEN when:

• You need conditional logic beyond NULL checking
• You’re comparing values or applying different transformations
• You need to evaluate complex conditions

Here’s a comparison:

-- Using COALESCE (simple and clean)
SELECT COALESCE(nickname, name) as display_name
FROM users;

-- Using CASE (more verbose for the same result)
SELECT
  CASE
    WHEN nickname IS NOT NULL THEN nickname
    ELSE name
  END as display_name
FROM users;

For simple NULL handling, COALESCE is almost always the better choice. It’s more concise and its intent is immediately clear to anyone reading the code.

Performance Considerations

COALESCE is generally very efficient. It’s a built-in function that databases can optimize well. However, keep these tips in mind:

Short-circuit evaluation: COALESCE stops evaluating as soon as it finds a non-NULL value. Place the most likely non-NULL values first:

-- Good: check the column that's usually populated first
SELECT COALESCE(primary_phone, mobile_phone, work_phone)
FROM contacts;

Avoid complex expressions: While you can use subqueries or function calls in COALESCE, each one will be evaluated until a non-NULL value is found:

-- This might be slow if the subqueries are complex
SELECT COALESCE(
  (SELECT expensive_calculation_1()),
  (SELECT expensive_calculation_2()),
  'default'
);

For most use cases, COALESCE is plenty fast and won’t be a bottleneck in your queries.

Common Pitfalls

All NULL values

If all arguments to COALESCE are NULL, the result is NULL. Always include a non-NULL default if you need to guarantee a value:

-- Might return NULL if both columns are NULL
SELECT COALESCE(column1, column2) FROM table;

-- Always returns something
SELECT COALESCE(column1, column2, 'No data available') FROM table;

Data type mismatches

All arguments to COALESCE should be compatible types. The function will attempt to convert them to a common type, but it’s best to be explicit:

-- This might cause issues
SELECT COALESCE(numeric_column, 'N/A') FROM table;

-- Better: cast or use compatible types
SELECT COALESCE(CAST(numeric_column AS TEXT), 'N/A') FROM table;

Empty strings vs NULL

Remember that COALESCE only checks for NULL, not empty strings. An empty string (‘’) is not NULL:

-- This returns an empty string, not 'No name'
SELECT COALESCE('', 'No name');  -- Result: ''

-- To handle both NULL and empty strings, combine with NULLIF
SELECT COALESCE(NULLIF(name, ''), 'No name') FROM users;

Working with COALESCE in Beekeeper Studio

If you’re working with SQL queries regularly, having a good SQL editor makes all the difference. Beekeeper Studio provides a clean, intuitive interface for writing and testing queries with COALESCE and other SQL functions.

Features that help when working with COALESCE:

• Autocomplete: Suggests column names as you type, reducing errors
• Query history: Save and reuse your NULL-handling patterns
• Result formatting: Clearly displays NULL values versus empty strings
• Multi-database support: Use COALESCE across PostgreSQL, MySQL, SQLite, and more

The free version includes everything you need to master SQL functions like COALESCE, making it perfect for learning and everyday database work.

Key Takeaways

The SQL COALESCE function is an essential tool for handling NULL values effectively. Here’s what to remember:

• COALESCE returns the first non-NULL value from its arguments, making it perfect for providing fallback values
• You can chain multiple values to create a waterfall of fallback options
• It’s cleaner than CASE for simple NULL handling scenarios
• Always include a non-NULL default at the end if you need to guarantee a result
• Consider data types to avoid conversion issues
• Place likely non-NULL values first for optimal performance
• Empty strings are not NULL - use NULLIF if you need to treat them as such

By mastering COALESCE, you’ll write cleaner, more reliable SQL queries that gracefully handle missing data. Whether you’re building reports, cleaning datasets, or creating user-facing applications, COALESCE is a function you’ll reach for again and again.

Ready to practice more? Try the interactive examples above, or explore more SQL tutorials to level up your database skills.