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.
Top comments (0)