Understanding SQL COALESCE for Null Handling

The SQL COALESCE function provides a practical solution for managing NULL values in database operations. NULL values, representing unknown or missing data, can cause issues if not handled correctly, leading to errors in calculations and inconsistencies in sorting. COALESCE allows you to set fallback values directly in your queries, returning the first non-NULL value among the specified columns or expressions.

This guide provides simple examples to illustrate how COALESCE can be used across different DBMS platforms like MySQL, PostgreSQL, and SQL Server.

SQL COALESCE examples

Default Values in Queries

When a column value is NULL, you can use COALESCE to fill it with a default:

SELECT
    name,
    COALESCE(age, 'N/A') AS age,
    department
FROM
    employee;

This approach replaces NULL values with "N/A" to ensure consistent and clear output in your reports.

Handling Math Operations

Avoid issues in numeric calculations by replacing NULL with a safe value:

SELECT
    name,
    price,
    discount,
    price * (1 - COALESCE(discount, 0)/100) AS final_price
FROM
    product;

This substitution keeps calculations intact, even when discounts are missing.

Sorting with Consistency

Control the sort order by using COALESCE to replace NULL values:

SELECT
    name,
    COALESCE(priority, 0) AS priority
FROM
    tasks
ORDER BY
    priority;

By substituting NULL with 0, this query guarantees predictable sorting.

FAQ

What does SQL COALESCE do?

It evaluates multiple expressions, returning the first non-NULL value. This is particularly useful for handling missing data.

Is COALESCE ANSI compliant?

Yes, COALESCE has been included in the ANSI SQL standard since 1992, making it widely supported.

COALESCE vs ISNULL?

While COALESCE is standard and supports multiple expressions, ISNULL is DBMS-specific and usually evaluates only one value against a fallback.

Summary

COALESCE is a straightforward yet powerful function for managing NULL values in SQL. For more examples and an in-depth explanation, see the complete SQL COALESCE guide.