SQL - COALESCE() with Examples
COALESCE() accepts multiple arguments and returns the first non-NULL value. The syntax is straightforward:
Key Insights
- COALESCE() returns the first non-NULL value from a list of expressions, making it essential for handling missing data and providing default values in SQL queries
- Unlike ISNULL() or NVL() which accept only two arguments, COALESCE() evaluates multiple expressions in order, offering greater flexibility for complex NULL-handling scenarios
- COALESCE() is ANSI SQL standard and works across all major database systems (PostgreSQL, MySQL, SQL Server, Oracle), making your queries portable
Understanding COALESCE() Syntax
COALESCE() accepts multiple arguments and returns the first non-NULL value. The syntax is straightforward:
COALESCE(expression1, expression2, ..., expressionN)
The function evaluates expressions from left to right and returns the first non-NULL result. If all expressions are NULL, it returns NULL.
SELECT COALESCE(NULL, NULL, 'first non-null', 'second non-null') AS result;
-- Returns: 'first non-null'
SELECT COALESCE(NULL, NULL, NULL) AS result;
-- Returns: NULL
Basic NULL Handling
The most common use case is providing default values for NULL columns:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
phone VARCHAR(20),
mobile VARCHAR(20),
department VARCHAR(50)
);
INSERT INTO employees VALUES
(1, 'John Doe', '555-0100', NULL, 'Engineering'),
(2, 'Jane Smith', NULL, '555-0200', 'Marketing'),
(3, 'Bob Johnson', NULL, NULL, NULL);
-- Display phone number with fallback to mobile or default message
SELECT
name,
COALESCE(phone, mobile, 'No contact') AS contact_number
FROM employees;
Results:
name | contact_number
--------------|---------------
John Doe | 555-0100
Jane Smith | 555-0200
Bob Johnson | No contact
Cascading Fallback Values
COALESCE() excels when you need multiple fallback options. Consider an e-commerce scenario with various discount types:
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100),
list_price DECIMAL(10,2),
member_price DECIMAL(10,2),
sale_price DECIMAL(10,2),
clearance_price DECIMAL(10,2)
);
INSERT INTO products VALUES
(1, 'Laptop', 1200.00, 1100.00, NULL, NULL),
(2, 'Monitor', 400.00, NULL, 350.00, NULL),
(3, 'Keyboard', 80.00, NULL, NULL, 45.00),
(4, 'Mouse', 50.00, NULL, NULL, NULL);
-- Get the best available price
SELECT
name,
list_price,
COALESCE(clearance_price, sale_price, member_price, list_price) AS final_price,
list_price - COALESCE(clearance_price, sale_price, member_price, list_price) AS savings
FROM products;
Results:
name | list_price | final_price | savings
----------|------------|-------------|--------
Laptop | 1200.00 | 1100.00 | 100.00
Monitor | 400.00 | 350.00 | 50.00
Keyboard | 80.00 | 45.00 | 35.00
Mouse | 50.00 | 50.00 | 0.00
Data Consolidation from Multiple Columns
When data might exist in different columns, COALESCE() consolidates it into a single output:
CREATE TABLE customer_contacts (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email_primary VARCHAR(100),
email_secondary VARCHAR(100),
email_backup VARCHAR(100)
);
INSERT INTO customer_contacts VALUES
(1, 'Alice Brown', 'alice@email.com', NULL, NULL),
(2, 'Charlie Davis', NULL, 'charlie@backup.com', NULL),
(3, 'Diana Evans', NULL, NULL, 'diana@old.com');
SELECT
name,
COALESCE(email_primary, email_secondary, email_backup, 'no-email@company.com') AS contact_email
FROM customer_contacts;
Handling Aggregations with NULL Values
COALESCE() is crucial when working with aggregations that might return NULL:
CREATE TABLE sales (
region VARCHAR(50),
quarter INT,
revenue DECIMAL(10,2)
);
INSERT INTO sales VALUES
('North', 1, 50000),
('North', 2, 60000),
('South', 1, 40000),
('West', 3, 30000);
-- Calculate total revenue by region with zero for missing data
SELECT
r.region,
COALESCE(SUM(s.revenue), 0) AS total_revenue,
COALESCE(AVG(s.revenue), 0) AS avg_revenue,
COALESCE(COUNT(s.quarter), 0) AS quarters_active
FROM (
SELECT DISTINCT region FROM sales
UNION SELECT 'East'
) r
LEFT JOIN sales s ON r.region = s.region
GROUP BY r.region;
Dynamic String Concatenation
When building strings from potentially NULL values, COALESCE() prevents the entire result from becoming NULL:
CREATE TABLE addresses (
id INT PRIMARY KEY,
street VARCHAR(100),
apt VARCHAR(20),
city VARCHAR(50),
state VARCHAR(2),
zip VARCHAR(10)
);
INSERT INTO addresses VALUES
(1, '123 Main St', 'Apt 4B', 'Boston', 'MA', '02101'),
(2, '456 Oak Ave', NULL, 'Portland', 'OR', '97201'),
(3, '789 Pine Rd', NULL, 'Austin', 'TX', NULL);
-- Build complete address with proper handling of NULL values
SELECT
CONCAT(
street,
COALESCE(CONCAT(', ', apt), ''),
', ',
city,
', ',
state,
' ',
COALESCE(zip, 'ZIP N/A')
) AS full_address
FROM addresses;
Results:
full_address
----------------------------------------
123 Main St, Apt 4B, Boston, MA 02101
456 Oak Ave, Portland, OR 97201
789 Pine Rd, Austin, TX ZIP N/A
COALESCE() in WHERE Clauses
Use COALESCE() to handle optional search parameters in dynamic queries:
-- Search with optional filters
DECLARE @search_dept VARCHAR(50) = NULL;
DECLARE @search_phone VARCHAR(20) = NULL;
SELECT
name,
department,
phone
FROM employees
WHERE
department = COALESCE(@search_dept, department)
AND phone = COALESCE(@search_phone, phone);
This pattern allows NULL parameters to match any value, effectively making filters optional.
Performance Considerations
COALESCE() is generally efficient, but consider these optimization strategies:
-- Less efficient: Multiple COALESCE calls
SELECT
COALESCE(col1, 0) + COALESCE(col2, 0) + COALESCE(col3, 0) AS total
FROM table_name;
-- More efficient: Single COALESCE with expression
SELECT
COALESCE(col1 + col2 + col3,
col1 + col2,
col1 + col3,
col2 + col3,
col1,
col2,
col3,
0) AS total
FROM table_name;
-- Best for simple cases: Handle NULLs in calculation
SELECT
COALESCE(col1, 0) + COALESCE(col2, 0) + COALESCE(col3, 0) AS total
FROM table_name;
For indexed columns, avoid wrapping them in COALESCE() within WHERE clauses as it prevents index usage:
-- Prevents index usage
WHERE COALESCE(indexed_column, 0) = 5;
-- Better: Allow index usage
WHERE indexed_column = 5 OR indexed_column IS NULL;
COALESCE() vs CASE Expressions
COALESCE() is syntactic sugar for a specific CASE pattern. These are equivalent:
-- Using COALESCE
SELECT COALESCE(col1, col2, col3, 'default') FROM table_name;
-- Using CASE
SELECT
CASE
WHEN col1 IS NOT NULL THEN col1
WHEN col2 IS NOT NULL THEN col2
WHEN col3 IS NOT NULL THEN col3
ELSE 'default'
END
FROM table_name;
Use COALESCE() for NULL handling—it’s clearer and more concise. Use CASE when you need complex conditional logic beyond simple NULL checks.
Common Pitfalls
Type compatibility: All arguments must be compatible types or implicitly convertible:
-- Error: incompatible types
SELECT COALESCE(numeric_column, 'N/A') FROM table_name;
-- Correct: explicit conversion
SELECT COALESCE(CAST(numeric_column AS VARCHAR), 'N/A') FROM table_name;
Short-circuit evaluation: COALESCE() stops evaluating once it finds a non-NULL value, which can affect performance with complex expressions:
-- Expensive function only called if first_col is NULL
SELECT COALESCE(first_col, expensive_function()) FROM table_name;
COALESCE() is a fundamental SQL function that transforms how you handle missing data. Master it to write cleaner, more robust queries across any database platform.