How to use the Coalesce function in SQL

The COALESCE function in SQL is a versatile tool for handling NULL values. It ensures that query results remain meaningful and readable, even when data is incomplete. This enhanced guide explains how COALESCE works, includes a clear sketch-style diagram, highlights common pitfalls, and provides real-world examples.

What Is the COALESCE Function?

Definition:
COALESCE returns the first non-NULL value from a list of expressions.

Syntax:

COALESCE(expression1, expression2, ..., expressionN)

Concept:
COALESCE checks each expression in order and returns the first one that isn’t NULL. If all are NULL, it returns NULL.

How COALESCE Works (Sketchy Flow Diagram)

┌─────────────────────────────────────┐
│ COALESCE(expr1, expr2, expr3) │
└─────────────────────────────────────┘
↓
┌──────────────┐
│ Check expr1 │──→ NOT NULL? ──→ ✅ RETURN expr1 (STOP!)
└──────────────┘
↓ NULL
┌──────────────┐
│ Check expr2 │──→ NOT NULL? ──→ ✅ RETURN expr2 (STOP!)
└──────────────┘
↓ NULL
┌──────────────┐
│ Check expr3 │──→ NOT NULL? ──→ ✅ RETURN expr3 (STOP!)
└──────────────┘
↓ NULL
❌ RETURN NULL

This diagram shows that COALESCE stops evaluating as soon as it finds a non-NULL value.

🧩 Example Table

EmployeeID	FirstName	MiddleName	LastName
1	John	NULL	Doe
2	NULL	A.	Smith
3	Emily	Rose	NULL

Practical Examples

1. Replace NULL with Default Text

SELECT
EmployeeID,
COALESCE(PhoneNumber, 'No Phone Provided') AS ContactNumber
FROM Employees;

EmployeeID	ContactNumber
1	555-1234
2	No Phone Provided
3	555-9876

2. Combine Multiple Columns

SELECT
COALESCE(Email, AlternateEmail, 'No Email Available') AS PrimaryEmail
FROM Employees;

Email	AlternateEmail	PrimaryEmail
john@company.com	NULL	john@company.com
NULL	a.smith@company.com	a.smith@company.com
NULL	NULL	No Email Available

---

3. Handle Calculations with NULL Values

SELECT
COALESCE(Salary, 0) + COALESCE(Bonus, 0) AS TotalCompensation
FROM Employees;

Salary	Bonus	TotalCompensation
50000	5000	55000
NULL	3000	3000
60000	NULL	60000

Clarifying the “Temporary” Nature of COALESCE

COALESCE only affects query results, not the underlying data.

• COALESCE doesn’t change your actual data!
  • SELECT COALESCE(MiddleName, ‘N/A’) AS DisplayName FROM Employees;
• The MiddleName column in the table is still NULL
• Only the query result shows ‘N/A’

Note – In order to permanently replace NULL values, use an UPDATE statement instead.

COALESCE vs. ISNULL

Feature	COALESCE	ISNULL
SQL Standard	✅ Yes	❌ No
Arguments	Multiple	Two
Data Type Handling	Uses the highest precedence type	Uses the first parameter’s type
Performance	Slightly slower (translates to CASE)	Faster
Nullability	Result can be NULL	Result matches the first parameter
Portability	High	Low

Example:

-- ISNULL forces INT
SELECT ISNULL(NULL, 1.5); -- Returns: 1 (truncated!)

-- COALESCE preserves DECIMAL
SELECT COALESCE(NULL, 1.5); -- Returns: 1.5

Performance Consideration

Evaluation Order Matters

COALESCE stops evaluating as soon as it finds a non-NULL value. To optimise performance:

1. Place the most likely non-NULL expression first
2. Place the cheapest to compute expression before expensive operations

-- ✅ GOOD: Check simple column before expensive calculation
SELECT COALESCE(CachedPrice, CalculatePrice(ProductID), 0) 
FROM Products;

-- ❌ BAD: Always runs expensive function first
SELECT COALESCE(CalculatePrice(ProductID), CachedPrice, 0) 
FROM Products;

How COALESCE Actually Works Under the Hood?

In most SQL databases, COALESCE is translated into a CASE statement:

-- This COALESCE:
COALESCE(expr1, expr2, expr3)

-- Becomes this CASE:
CASE 
  WHEN expr1 IS NOT NULL THEN expr1
  WHEN expr2 IS NOT NULL THEN expr2
  ELSE expr3
END

Performance Impact:

• COALESCE adds minimal overhead for simple column checks
• Performance matters more with functions, subqueries, or calculations
• ISNULL (SQL Server) is slightly faster for 2-argument scenarios

Indexing Considerations

❌ COALESCE in WHERE Clause Can Prevent Index Usage:

-- Index on StatusCode won't be used efficiently
WHERE COALESCE(StatusCode, 'UNKNOWN') = 'ACTIVE'

✅ Better Alternatives:

-- Option 1: Use OR (may use index)
WHERE StatusCode = 'ACTIVE' OR (StatusCode IS NULL AND 'UNKNOWN' = 'ACTIVE')

-- Option 2: Separate conditions
WHERE StatusCode = 'ACTIVE'

-- Option 3: Use computed/persisted column with index
ALTER TABLE Orders ADD StatusDisplay AS COALESCE(StatusCode, 'UNKNOWN') PERSISTED;
CREATE INDEX idx_StatusDisplay ON Orders(StatusDisplay);

Large Dataset Performance Tips

For Million+ Row Tables:

-- ❌ AVOID: Multiple COALESCEs with subqueries
SELECT 
  COALESCE(
    (SELECT Price FROM PriceHistory WHERE...),
    (SELECT Price FROM DefaultPrices WHERE...),
    0
  )
FROM Products;

-- ✅ BETTER: Use JOINs instead
SELECT COALESCE(ph.Price, dp.Price, 0)
FROM Products p
LEFT JOIN PriceHistory ph ON p.ProductID = ph.ProductID
LEFT JOIN DefaultPrices dp ON p.Category = dp.Category;

When Performance Really Matters?

COALESCE performance becomes significant when:

• ✅ Used in WHERE/JOIN conditions on large tables
• ✅ Expression includes functions, calculations, or subqueries
• ✅ Query runs millions of times (batch processing, reports)

COALESCE overhead is negligible when:

• ❌ Simply checking column values
• ❌ Used in the SELECT clause for display purposes
• ❌ Dataset is small (<10,000 rows)

Quick Performance Comparison

Scenario	Impact	Recommendation
COALESCE(col1, col2)	Negligible	Use freely
COALESCE(col1, 'default')	Negligible	Use freely
COALESCE(function(), col1)	Medium	Reorder arguments
COALESCE(subquery, subquery)	High	Consider JOINs or CTEs
In the WHERE clause	High	Avoid; use OR conditions

Monitoring Performance

-- Use EXPLAIN/Execution Plan to check impact
EXPLAIN SELECT 
  COALESCE(ExpensiveCalc(), CheapColumn, 0) 
FROM LargeTable;

-- Compare execution time
SET STATISTICS TIME ON;
-- Run your query variants here
SET STATISTICS TIME OFF;

Bottom Line

Don’t over-optimise! COALESCE is efficient for typical use cases. Focus on:

1. Argument order when using functions/subqueries
2. Avoiding COALESCE in WHERE clauses on large indexed tables
3. Actual performance testing for your specific scenario

Common Mistakes to Avoid

Mistake 1: Using COALESCE in WHERE Clauses on Indexed Columns

— ❌ Prevents index usage
WHERE COALESCE(StatusCode, ‘UNKNOWN’) = ‘ACTIVE’

— ✅ Better approach
WHERE StatusCode = ‘ACTIVE’ OR StatusCode IS NULL

Mistake 2: Assuming COALESCE Changes Data
COALESCE only affects query output. Use UPDATE to modify stored data.

Mistake 3: Too Many Arguments
Using COALESCE with many arguments (10+) may indicate poor schema design.

Benefits of Using COALESCE

• Keeps data clean and readable
• Simplifies query logic
• Works across all major SQL databases
• Evaluates efficiently by stopping at the first non-NULL value (doesn’t improve overall query performance)
• Reduces the need for complex CASE statements

The COALESCE function is an essential SQL feature for managing NULL values efficiently. It ensures that query results remain consistent and readable without altering the underlying data. By understanding its evaluation order, performance behaviour, and common pitfalls, COALESCE can be used confidently in both reporting and data transformation tasks.

Thank you for reading 🙂