SQL COALESCE vs ISNULL Difference

If you have worked with SQL databases for any length of time, you have almost certainly run into NULL values: those mysterious empty cells that represent missing, unknown, or inapplicable data.

And if you have tried to handle NULLs in your queries, you have probably come across two functions that seem to do the same thing:

• COALESCE
• ISNULL

Both functions help you deal with NULL values by replacing them with something meaningful. But they work differently, have different capabilities, and are suited for different situations.

Knowing which one to use = and why is an important skill for anyone writing SQL professionally.

In this guide, we will break down exactly what COALESCE and ISNULL do, how they differ, when to use each one, and how to handle NULL values effectively in real-world SQL queries.

What Is NULL in SQL?

Before comparing the two functions, it is important to understand what NULL actually means in SQL.

NULL is not zero. NULL is not an empty string. NULL is the absence of any value. It represents missing, unknown, or inapplicable data.

This creates some surprising behavior:

sql

-- These all return NULL, not the expected result
SELECT NULL + 5;        -- Returns NULL
SELECT NULL = NULL;     -- Returns NULL (not TRUE)
SELECT NULL <> NULL;    -- Returns NULL (not FALSE)
SELECT 'Hello' + NULL;  -- Returns NULL

Any arithmetic or comparison involving NULL produces NULL. This is why you need special functions to handle NULL values safely.

What Is ISNULL?

ISNULL is a function that checks if a value is NULL and replaces it with a specified substitute value if it is.

Syntax

sql

ISNULL(check_expression, replacement_value)

• check_expression — The value or column to check for NULL
• replacement_value — The value to return if check_expression is NULL

Key Characteristics

• Accepts exactly two arguments — no more, no less
• The data type of the result is determined by the check_expression data type
• If the replacement_value has a different data type, it is implicitly converted to match the check_expression type
• Not part of the SQL standard — available in SQL Server and MySQL (with different behavior), but not in PostgreSQL or Oracle

Basic ISNULL Example

sql

SELECT ISNULL(NULL, 'Not Available');
-- Result: Not Available

SELECT ISNULL('Hello', 'Not Available');
-- Result: Hello

SELECT ISNULL(NULL, 0);
-- Result: 0

What Is COALESCE?

COALESCE is a function that evaluates a list of expressions and returns the first non-NULL value it finds.

Syntax

sql

COALESCE(expression1, expression2, expression3, ...)

• Accepts two or more arguments
• Returns the first expression in the list that is NOT NULL
• If all expressions are NULL, returns NULL
• Part of the ANSI SQL standard — works in SQL Server, MySQL, PostgreSQL, Oracle, SQLite, and virtually every other SQL database

Basic COALESCE Example

sql

SELECT COALESCE(NULL, NULL, 'Third Value', 'Fourth Value');
-- Result: Third Value (first non-NULL value)

SELECT COALESCE(NULL, 'Second', 'Third');
-- Result: Second

SELECT COALESCE('First', 'Second', 'Third');
-- Result: First (already not NULL)

SELECT COALESCE(NULL, NULL, NULL);
-- Result: NULL (all values are NULL)

Setting Up the Example Dataset

We will use a consistent employees table throughout this guide.

sql

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    primary_phone VARCHAR(20),
    secondary_phone VARCHAR(20),
    mobile_phone VARCHAR(20),
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'Alice', 'Engineering', '555-0101', NULL, '555-9901', 95000, 10000, NULL),
(2, 'Bob', 'Marketing', NULL, '555-0202', NULL, 62000, NULL, 1),
(3, 'Charlie', 'Sales', NULL, NULL, '555-9903', 88000, 5000, 1),
(4, 'Diana', 'HR', '555-0104', '555-0204', NULL, 55000, NULL, 2),
(5, 'Eve', 'Engineering', NULL, NULL, NULL, 71000, 8000, 1);

Table Preview:

id	name	department	primary_phone	secondary_phone	mobile_phone	salary	bonus	manager_id
1	Alice	Engineering	555-0101	NULL	555-9901	95000	10000	NULL
2	Bob	Marketing	NULL	555-0202	NULL	62000	NULL	1
3	Charlie	Sales	NULL	NULL	555-9903	88000	5000	1
4	Diana	HR	555-0104	555-0204	NULL	55000	NULL	2
5	Eve	Engineering	NULL	NULL	NULL	71000	8000	1

ISNULL in Action — Practical Examples

Example 1: Replace NULL Bonus With Zero

sql

SELECT
    name,
    salary,
    ISNULL(bonus, 0) AS bonus,
    salary + ISNULL(bonus, 0) AS total_compensation
FROM employees;

Result:

name	salary	bonus	total_compensation
Alice	95000	10000	105000
Bob	62000	0	62000
Charlie	88000	5000	93000
Diana	55000	0	55000
Eve	71000	8000	79000

Without ISNULL, adding NULL bonus to salary would return NULL for Bob and Diana making the total_compensation column useless.

Example 2: Replace NULL Phone With a Default Message

sql

SELECT
    name,
    ISNULL(primary_phone, 'No phone on file') AS contact_number
FROM employees;

Result:

name	contact_number
Alice	555-0101
Bob	No phone on file
Charlie	No phone on file
Diana	555-0104
Eve	No phone on file

Example 3: Replace NULL Manager ID With Zero

sql

SELECT
    name,
    ISNULL(manager_id, 0) AS manager_id
FROM employees;

Result:

name	manager_id
Alice	0
Bob	1
Charlie	1
Diana	2
Eve	1

Alice has no manager (she is the top of the hierarchy), so her NULL manager_id is replaced with 0.

COALESCE in Action — Practical Examples

Example 1: Find the First Available Phone Number

This is where COALESCE really shines over ISNULL. It checks multiple columns in order and returns the first non-NULL value.

sql

SELECT
    name,
    COALESCE(primary_phone, secondary_phone, mobile_phone, 'No contact available') AS best_contact
FROM employees;

Result:

name	best_contact
Alice	555-0101
Bob	555-0202
Charlie	555-9903
Diana	555-0104
Eve	No contact available

How It Works:

• Alice — Has a primary phone → returns primary_phone (555-0101)
• Bob — No primary phone, has secondary → returns secondary_phone (555-0202)
• Charlie — No primary or secondary, has mobile → returns mobile_phone (555-9903)
• Diana — Has a primary phone → returns primary_phone (555-0104)
• Eve — All three phones are NULL → returns the fallback string ‘No contact available’

Achieving this with ISNULL would require nesting: ISNULL(ISNULL(ISNULL(primary_phone, secondary_phone), mobile_phone), 'No contact available') significantly harder to read and maintain.

Example 2: Replace NULL Bonus With Zero

sql

SELECT
    name,
    salary,
    COALESCE(bonus, 0) AS bonus,
    salary + COALESCE(bonus, 0) AS total_compensation
FROM employees;

This produces the same result as the ISNULL example — when you only need to check one value, they are functionally equivalent.

Example 3: Priority-Based Value Selection

COALESCE is perfect for business logic where you want to use a value from one source and fall back to another if it is missing.

sql

-- Use the override salary if set, otherwise use the standard salary
SELECT
    name,
    COALESCE(salary_override, standard_salary, 50000) AS effective_salary
FROM salary_table;

This reads as: “Use salary_override if available. If not, use standard_salary. If that is also NULL, default to 50000.”

Example 4: COALESCE in a WHERE Clause

sql

-- Find employees where the effective contact is a specific number
SELECT name
FROM employees
WHERE COALESCE(primary_phone, secondary_phone, mobile_phone) = '555-0202';

Result:

name
Bob

Example 5: COALESCE With Calculations

sql

SELECT
    name,
    COALESCE(bonus, salary * 0.05, 0) AS effective_bonus
FROM employees;

How It Works:

• If bonus is not NULL → use the actual bonus
• If bonus is NULL but salary is not NULL → use 5% of salary as a default bonus
• If both are NULL → use 0

This kind of multi-level fallback logic with expressions is impossible with ISNULL in a single call.

The Key Differences Between COALESCE and ISNULL

Difference 1: Number of Arguments

This is the most fundamental difference.

sql

-- ISNULL: exactly 2 arguments
ISNULL(expression, replacement)

-- COALESCE: 2 or more arguments
COALESCE(expr1, expr2, expr3, expr4, ...)

COALESCE can check as many expressions as you need. ISNULL is limited to checking one expression and providing one replacement.

Difference 2: SQL Standard Compliance

sql

-- COALESCE — ANSI SQL standard, works everywhere
SELECT COALESCE(column1, 'default') FROM table1;  -- SQL Server, MySQL, PostgreSQL, Oracle, SQLite

-- ISNULL — NOT standard SQL, database-specific
SELECT ISNULL(column1, 'default') FROM table1;     -- SQL Server, MySQL only

If you write queries that need to run on multiple database platforms, always use COALESCE.

Difference 3: Return Data Type

This is a subtle but important difference that can cause unexpected behavior.

ISNULL — The return type is determined by the first argument (check_expression). If the replacement value has a different type, it is cast to match the first argument.

sql

-- ISNULL example — return type is determined by check_expression
DECLARE @val VARCHAR(3) = NULL;
SELECT ISNULL(@val, 'This is a long string');
-- Result: 'Thi' — truncated to 3 characters (VARCHAR(3) from @val)

COALESCE — The return type is determined by the highest precedence data type among all arguments, following standard SQL type precedence rules.

sql

-- COALESCE example — return type follows type precedence
DECLARE @val VARCHAR(3) = NULL;
SELECT COALESCE(@val, 'This is a long string');
-- Result: 'This is a long string' — full string returned

This difference in data type handling is one of the most common sources of subtle bugs when switching between the two functions.

Difference 4: Performance and Evaluation

ISNULL evaluates its check_expression once.

COALESCE is equivalent to a CASE expression in SQL standard:

sql

-- COALESCE(a, b, c) is equivalent to:
CASE WHEN a IS NOT NULL THEN a
     WHEN b IS NOT NULL THEN b
     ELSE c
END

In practice, modern SQL engines optimize both functions well. But when COALESCE is used with subqueries or expensive expressions, the subquery may be evaluated multiple times — once to check for NULL and once to return the value.

sql

-- This subquery might execute twice
SELECT COALESCE((SELECT MAX(salary) FROM employees WHERE department = 'X'), 0);

-- ISNULL evaluates the subquery only once
SELECT ISNULL((SELECT MAX(salary) FROM employees WHERE department = 'X'), 0);

For performance-critical queries with expensive subqueries, ISNULL can be faster. But for most everyday queries, the difference is negligible.

Difference 5: Database Availability

Database	ISNULL Support	COALESCE Support
SQL Server	Yes	Yes
MySQL	Yes (same behavior)	Yes
PostgreSQL	No	Yes
Oracle	No (use NVL instead)	Yes
SQLite	No	Yes
IBM DB2	No	Yes

COALESCE is the only NULL-handling function that works across all major SQL databases.

Full Comparison Table

Feature	ISNULL	COALESCE
Number of Arguments	Exactly 2	2 or more
SQL Standard	No — database specific	Yes — ANSI SQL
Works in SQL Server	Yes	Yes
Works in MySQL	Yes	Yes
Works in PostgreSQL	No	Yes
Works in Oracle	No	Yes
Return Type Logic	Determined by first argument	Highest precedence type
Multiple Fallback Values	No — nesting required	Yes — native
Performance with Subqueries	Slightly faster (evaluates once)	May evaluate twice
Handles Expressions	Yes	Yes
Best For	Quick SQL Server NULL replacement	Cross-database, multiple fallbacks

When to Use ISNULL

Use ISNULL when:

• You are only working in SQL Server and portability to other databases is not a concern
• You need to replace a single NULL value with a single replacement
• You are using an expensive subquery where evaluating the expression twice would be a performance concern
• Your team follows a SQL Server-specific style guide that prefers ISNULL

sql

-- Good use of ISNULL — simple SQL Server query
SELECT
    name,
    ISNULL(bonus, 0) AS bonus,
    ISNULL(manager_id, 0) AS manager_id
FROM employees;

When to Use COALESCE

Use COALESCE when:

• You need to check multiple columns or expressions for the first non-NULL value
• You are writing queries that need to run on multiple database platforms
• You want full data type flexibility without unexpected truncation
• You need to provide multiple fallback values in priority order
• You are writing ANSI-compliant SQL that follows open standards

sql

-- Good use of COALESCE — multiple fallbacks, cross-database compatible
SELECT
    name,
    COALESCE(primary_phone, secondary_phone, mobile_phone, 'No contact') AS best_contact,
    COALESCE(bonus, salary * 0.05, 0) AS effective_bonus
FROM employees;

Advanced Patterns Using COALESCE and ISNULL

Pattern 1: COALESCE in GROUP BY and Aggregations

sql

-- Group by department, treating NULL department as 'Unassigned'
SELECT
    COALESCE(department, 'Unassigned') AS department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY COALESCE(department, 'Unassigned');

Pattern 2: COALESCE for Dynamic Default Values

sql

-- Use company average salary as fallback if individual salary is NULL
SELECT
    name,
    COALESCE(
        salary,
        (SELECT AVG(salary) FROM employees),
        50000
    ) AS effective_salary
FROM employees;

Pattern 3: ISNULL in Calculated Columns

sql

-- Safe division that handles NULL denominators
SELECT
    name,
    salary / ISNULL(NULLIF(working_hours, 0), 1) AS hourly_rate
FROM employees;

Pattern 4: COALESCE for Optional Filter Parameters

A powerful pattern in stored procedures where parameters may or may not be provided.

sql

-- Return all employees, or filter by department if parameter is provided
CREATE PROCEDURE GetEmployees
    @department VARCHAR(50) = NULL
AS
BEGIN
    SELECT *
    FROM employees
    WHERE department = COALESCE(@department, department);
END

When @department is NULL, COALESCE(@department, department) evaluates to the department column itself — so department = department is always true and returns all rows. When @department is provided, it filters normally.

Pattern 5: COALESCE for Self-Referencing Hierarchies

sql

-- Display manager name, showing 'Top Level' for employees with no manager
SELECT
    e.name AS employee,
    COALESCE(m.name, 'Top Level') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Result:

employee	manager
Alice	Top Level
Bob	Alice
Charlie	Alice
Diana	Bob
Eve	Alice

Real-World Use Cases

Financial Reporting

Replace NULL values in financial columns so aggregations work correctly and reports display meaningful values instead of blank cells.

sql

SELECT
    department,
    SUM(salary) AS total_salary,
    SUM(COALESCE(bonus, 0)) AS total_bonus,
    SUM(salary + COALESCE(bonus, 0)) AS total_compensation
FROM employees
GROUP BY department;

Contact Management Systems

Find the best available contact method for each customer by checking phone, email, and address fields in priority order.

sql

SELECT
    customer_id,
    name,
    COALESCE(mobile, work_phone, home_phone, 'No phone') AS primary_contact,
    COALESCE(email, secondary_email, 'No email') AS primary_email
FROM customers;

Data Quality Dashboards

Identify records with missing critical data using COALESCE to check multiple required fields simultaneously.

sql

SELECT
    id,
    name,
    CASE
        WHEN COALESCE(email, phone, address) IS NULL THEN 'No contact info'
        WHEN email IS NULL THEN 'Missing email'
        ELSE 'Complete'
    END AS data_quality_status
FROM customers;

ETL and Data Pipeline Processing

Replace NULL values with meaningful defaults during data transformation before loading into a data warehouse.

sql

INSERT INTO warehouse.dim_employees
SELECT
    id,
    name,
    COALESCE(department, 'Unknown') AS department,
    COALESCE(salary, 0) AS salary,
    COALESCE(bonus, 0) AS bonus,
    COALESCE(hire_date, GETDATE()) AS hire_date,
    COALESCE(manager_id, -1) AS manager_id  -- -1 represents no manager
FROM staging.employees;

Application Development — Dynamic Queries

Handle optional search parameters in application queries where users may or may not fill in every filter field.

sql

SELECT *
FROM products
WHERE
    category = COALESCE(@category, category)
    AND price <= COALESCE(@max_price, price)
    AND COALESCE(brand, '') LIKE COALESCE('%' + @brand + '%', COALESCE(brand, ''));

Advantages and Disadvantages

ISNULL

Advantages:

• Simple and concise syntax for two-value NULL replacement
• Slightly better performance with expensive subqueries
• Familiar and widely used in SQL Server environments
• Easy to read for simple NULL replacement scenarios

Disadvantages:

• Only works in SQL Server and MySQL — not portable
• Limited to exactly two arguments — no multiple fallback support
• Return type is determined by the first argument, which can cause unexpected truncation
• Not ANSI SQL standard — violates cross-database compatibility

COALESCE

Advantages:

• ANSI SQL standard — works on all major databases
• Supports multiple fallback values natively
• More flexible data type handling
• Can accept expressions and calculations as arguments
• Cleaner syntax than nested ISNULL calls for multiple checks

Disadvantages:

• Slightly more verbose than ISNULL for simple two-argument cases
• May evaluate expensive subquery expressions twice in some databases
• Returns NULL if all arguments are NULL — requires careful fallback planning

Common Mistakes to Avoid

• Using ISNULL in queries meant to run on multiple databases — ISNULL is SQL Server and MySQL specific. If your queries will ever run on PostgreSQL or Oracle, always use COALESCE
• Expecting ISNULL to handle multiple fallbacks — ISNULL only takes two arguments. For multiple fallbacks, either nest ISNULL calls (ugly) or switch to COALESCE (clean)
• Ignoring the data type difference — ISNULL determines the return type from the first argument. If your check_expression is VARCHAR(10) and your replacement is a long string, the result will be truncated to 10 characters silently
• Using COALESCE with expensive subqueries without testing performance — In some database engines, COALESCE may evaluate the subquery twice. Test with ISNULL or use a CTE to pre-calculate the value
• Assuming NULL equals NULL — Never use WHERE column = NULL. Always use WHERE column IS NULL. COALESCE and ISNULL replace nulls but do not change SQL’s NULL comparison rules
• Not providing a final non-NULL fallback in COALESCE — If all expressions could potentially be NULL, always add a hardcoded fallback as the last argument to prevent COALESCE from returning NULL

sql

-- Bad — might still return NULL
COALESCE(primary_phone, secondary_phone, mobile_phone)

-- Good — guaranteed non-NULL result
COALESCE(primary_phone, secondary_phone, mobile_phone, 'No contact available')

Quick Reference Cheat Sheet

Task	ISNULL	COALESCE
Replace NULL with default	ISNULL(col, 'default')	COALESCE(col, 'default')
First non-NULL from multiple columns	Not directly possible	COALESCE(col1, col2, col3)
Multiple fallback with expression	Nested ISNULL required	COALESCE(col1, col2 * 0.1, 0)
Cross-database portability	Not portable	Use COALESCE
Expensive subquery (performance)	Evaluates once	May evaluate twice
SQL Server only project	Either works	Either works
PostgreSQL or Oracle project	Not available	Use COALESCE

COALESCE and ISNULL both solve the same core problem. They replace NULL values with something more useful. But they are not identical, and knowing when to reach for each one matters.

Here is the simplest decision framework:

• Working exclusively in SQL Server with one NULL check → ISNULL or COALESCE (both work)
• Need to check multiple columns for the first non-NULL value → COALESCE
• Writing queries that need to run on multiple databases → Always COALESCE
• Working in PostgreSQL or Oracle → Only COALESCE (ISNULL is not available)
• Using an expensive subquery and performance matters → ISNULL may be slightly faster

In practice, most experienced SQL developers default to COALESCE for everything. It is more powerful, more portable, and more flexible. ISNULL has its place in SQL Server-specific work, but COALESCE covers every scenario ISNULL handles, plus many more.

Master both functions, understand their differences, and NULL values will never catch you off guard again.

FAQs