Understanding SQL NULL Handling: COALESCE, NULLIF, and IS NULL for Reliable Data Queries

You've written what looks like a perfectly reasonable SQL query. You filter your results, join a couple of tables, and pull the data into a report — but something's off. Some rows are missing. A calculated column shows NULL where you expected a number. An average comes back suspiciously low. You double-check your logic. Everything looks right. Then you realize: it's the NULLs.

NULL is one of the most misunderstood concepts in SQL, and it quietly corrupts more queries than most developers care to admit. The problem isn't that NULL is complicated — it's that it behaves in ways that feel wrong until you understand the rules. NULL doesn't mean zero. It doesn't mean empty string. It means unknown, and that distinction has real consequences for how SQL evaluates expressions, filters rows, and computes aggregates.

By the end of this lesson, you'll have a solid, working understanding of how NULL propagates through SQL expressions, how to detect it reliably, and how to substitute meaningful values in its place using three practical functions: IS NULL, COALESCE, and NULLIF. These aren't obscure edge-case tools — they're everyday essentials for anyone writing production-grade queries.

What you'll learn:

• Why NULL represents unknown rather than empty or zero, and why that changes everything
• How to test for NULL correctly using IS NULL and IS NOT NULL
• How to replace NULL with a fallback value using COALESCE
• How to produce a NULL from a specific condition using NULLIF
• How NULL affects aggregations, joins, and filtering in ways that can silently break your results

---

Prerequisites

You should be comfortable with basic SELECT statements, WHERE clauses, and simple aggregate functions like COUNT, SUM, and AVG. You don't need to have worked with NULL before — that's exactly what we're covering here. Any standard SQL environment works for this lesson: PostgreSQL, MySQL, SQL Server, SQLite, or BigQuery all support these functions.

---

What NULL Actually Means

Before we touch any functions, we need to get the concept right. NULL in SQL does not mean zero. It does not mean an empty string (''). It means the value is unknown or absent. Think of it like a survey form where a respondent left a question blank — you don't know if they earn $0 or $500,000 or anything in between. The answer is simply missing.

This distinction matters because SQL's comparison operators don't work on NULL the way you'd expect. Try this:

SELECT 1 = NULL;

Most databases return NULL — not TRUE, not FALSE. NULL. That's because comparing anything to an unknown value produces an unknown result. Think about it: if you don't know what the missing value is, you can't say whether it equals 1.

This is the core of the "three-valued logic" that SQL uses: expressions can evaluate to TRUE, FALSE, or NULL. And in a WHERE clause, only rows where the condition evaluates to TRUE pass through. Rows where the condition is NULL are silently excluded.

That means this query will never return any rows, no matter what's in your table:

-- This never works
SELECT * FROM employees WHERE manager_id = NULL;

Even if manager_id actually contains NULL values in the database, this comparison returns NULL for every row — not TRUE — so nothing passes the filter.

Key concept: You can never test for NULL using =. The comparison always returns NULL, which means always false in a WHERE clause.

---

Testing for NULL: IS NULL and IS NOT NULL

SQL provides a dedicated syntax for NULL checks that bypasses the three-valued logic problem: IS NULL and IS NOT NULL. These are the only reliable ways to test whether a column contains NULL.

Let's work with a realistic scenario. Suppose you have an employees table at a mid-sized company:

CREATE TABLE employees (
    employee_id   INT,
    full_name     VARCHAR(100),
    department    VARCHAR(50),
    manager_id    INT,         -- NULL means this person has no manager (top-level)
    salary        DECIMAL(10,2),
    termination_date DATE      -- NULL means currently employed
);

To find all employees who currently have no manager — meaning they're at the top of the org chart — you'd write:

SELECT employee_id, full_name, department
FROM employees
WHERE manager_id IS NULL;

To find everyone who does have a manager:

SELECT employee_id, full_name, department, manager_id
FROM employees
WHERE manager_id IS NOT NULL;

These work because IS NULL is not a comparison operator — it's a special predicate that evaluates to TRUE or FALSE only, never NULL.

Similarly, if you want to find employees who are still active (no termination date recorded):

SELECT employee_id, full_name, department
FROM employees
WHERE termination_date IS NULL
ORDER BY full_name;

NULL in JOIN Conditions

Here's where things get surprisingly tricky. Suppose you're joining your employees table to a departments table, and some employees have NULL in their department_id column. When SQL evaluates the join condition employees.department_id = departments.department_id, any row where department_id is NULL on either side will produce NULL — and that row gets dropped from an INNER JOIN.

-- Employees with NULL department_id will disappear from this result
SELECT e.full_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

If you need to keep those employees in your result, you'd use a LEFT JOIN, which preserves all rows from the left table regardless of whether they match:

SELECT e.full_name, COALESCE(d.department_name, 'Unassigned') AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Notice that COALESCE sneaking in there — we'll cover it thoroughly in a moment.

NULL in Aggregates

Another gotcha: most aggregate functions silently ignore NULL values. SUM, AVG, MIN, MAX, and COUNT(column) all skip NULL rows.

-- This counts only rows where salary is not NULL
SELECT COUNT(salary) FROM employees;

-- This counts all rows, including those with NULL salary
SELECT COUNT(*) FROM employees;

The difference between COUNT(salary) and COUNT(*) can be dramatic if your data is sparse. And AVG is especially dangerous — it calculates the average over only the non-NULL values, which can make your average look higher than the true "average including unknowns" would be.

---

COALESCE: Your Go-To NULL Replacement Tool

COALESCE is the workhorse of NULL handling. It accepts two or more arguments and returns the first one that is not NULL. If all arguments are NULL, it returns NULL.

COALESCE(value1, value2, value3, ...)

The mental model: think of it as a waterfall. SQL evaluates the arguments left to right and returns the first one it finds that has an actual value.

Basic Usage: Providing a Default Value

The most common use case is substituting a literal default when a column is NULL:

SELECT
    full_name,
    COALESCE(department, 'No Department Assigned') AS department
FROM employees;

Now instead of seeing a blank or NULL in your report, users see the string 'No Department Assigned'. This is especially valuable when feeding data into dashboards or downstream systems that don't handle NULL gracefully.

Chaining Multiple Fallbacks

COALESCE becomes more powerful when you have multiple potential sources for a value and want to fall back through them in priority order. Imagine a customer contact table where you might have a work phone, a mobile phone, or a home phone — but any of them might be missing:

SELECT
    customer_id,
    full_name,
    COALESCE(work_phone, mobile_phone, home_phone, 'No contact number') AS best_contact
FROM customers;

SQL will return the first non-NULL phone number it finds, in the order you specified. If all three are NULL, it returns the fallback string.

COALESCE in Calculations

This is where NULL handling gets really practical. Any arithmetic involving NULL produces NULL. Multiply a salary by a NULL bonus rate, and the result is NULL. Add a NULL shipping fee to a subtotal, and you get NULL.

-- If shipping_fee is NULL, this returns NULL
SELECT order_id, subtotal + shipping_fee AS total FROM orders;

-- Better: treat NULL shipping as free (0)
SELECT order_id, subtotal + COALESCE(shipping_fee, 0) AS total FROM orders;

The same pattern applies to averages and weighted scores:

-- Performance review: weight ratings, with NULL treated as 0
SELECT
    employee_id,
    (COALESCE(q1_rating, 0) + COALESCE(q2_rating, 0) +
     COALESCE(q3_rating, 0) + COALESCE(q4_rating, 0)) / 4.0 AS avg_rating
FROM performance_reviews;

COALESCE vs. ISNULL and NVL

Different database systems have their own shorthand for a two-argument COALESCE:

• SQL Server uses ISNULL(column, default)
• Oracle uses NVL(column, default)
• MySQL supports both IFNULL and COALESCE

COALESCE is the ANSI SQL standard function and works across virtually all databases. Stick with it unless you have a specific reason to use a vendor-specific version.

---

NULLIF: Turning Specific Values Into NULL

NULLIF does the opposite of COALESCE — instead of replacing NULL with something, it creates a NULL under specific conditions. It takes exactly two arguments and returns NULL if they're equal; otherwise it returns the first argument unchanged.

NULLIF(value, comparison_value)

This sounds niche, but it's remarkably useful in two common situations.

Preventing Division-by-Zero Errors

Division by zero is a runtime error in SQL. If you're calculating a ratio and the denominator might be zero, your query will fail:

-- This crashes if total_attempts is 0
SELECT player_id, successful_shots / total_attempts AS success_rate
FROM game_stats;

NULLIF gives you an elegant fix: convert the zero denominator to NULL. Division by NULL returns NULL rather than throwing an error:

SELECT
    player_id,
    successful_shots / NULLIF(total_attempts, 0) AS success_rate
FROM game_stats;

Now rows where total_attempts is zero return NULL for success_rate instead of crashing your query. You can then wrap that in a COALESCE if you want to display a specific value instead:

SELECT
    player_id,
    COALESCE(successful_shots / NULLIF(total_attempts, 0), 0) AS success_rate
FROM game_stats;

Treating Placeholder Values as Missing Data

Databases often accumulate placeholder values over time — things like 'N/A', 'Unknown', '-', or 0 that were inserted to fill required fields but don't represent real data. NULLIF lets you convert those sentinel values to proper NULLs in your queries without altering the underlying data.

Imagine a product table where some legacy records have 'N/A' stored in the category column:

SELECT
    product_id,
    product_name,
    NULLIF(category, 'N/A') AS category
FROM products;

This returns NULL for any row where category = 'N/A', and returns the actual category name for everything else. You can then use COALESCE on top of it to replace those NULLs with a meaningful label:

SELECT
    product_id,
    product_name,
    COALESCE(NULLIF(category, 'N/A'), 'Uncategorized') AS category
FROM products;

This two-function pattern — COALESCE(NULLIF(...)) — is a classic SQL idiom worth keeping in your toolkit.

---

Putting It All Together: A Realistic Reporting Query

Let's write a query that combines everything we've covered. Suppose you're building a monthly sales summary report for a regional manager. You have an orders table and a sales_reps table, and there are some data quality issues to contend with:

• Some orders have no assigned sales rep (new direct orders)
• Some orders have a discount_amount of NULL (no discount applied)
• Some reps have 'TBD' stored in their region column
• You need to calculate revenue, and dividing by order count shouldn't crash on empty months

SELECT
    COALESCE(sr.region, NULLIF(sr.region, 'TBD'), 'Unassigned Region') AS region,
    COALESCE(sr.full_name, 'Direct Sale') AS sales_rep,
    COUNT(o.order_id) AS total_orders,
    SUM(o.order_amount) AS gross_revenue,
    SUM(o.order_amount - COALESCE(o.discount_amount, 0)) AS net_revenue,
    ROUND(
        SUM(o.order_amount - COALESCE(o.discount_amount, 0))
        / NULLIF(COUNT(o.order_id), 0),
        2
    ) AS avg_order_value
FROM orders o
LEFT JOIN sales_reps sr ON o.sales_rep_id = sr.rep_id
WHERE o.order_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY sr.region, sr.full_name
ORDER BY net_revenue DESC;

Let's break down what's happening:

• The LEFT JOIN preserves orders with no sales rep
• COALESCE(sr.full_name, 'Direct Sale') labels those unassigned orders clearly
• COALESCE(o.discount_amount, 0) ensures the discount arithmetic doesn't produce NULL
• NULLIF(COUNT(o.order_id), 0) prevents division by zero in the average calculation

---

Hands-On Exercise

Set up this practice dataset in any SQL environment and work through the tasks below:

CREATE TABLE support_tickets (
    ticket_id       INT,
    customer_name   VARCHAR(100),
    issue_category  VARCHAR(50),
    priority        VARCHAR(20),
    resolution_time INT,        -- minutes to resolve; NULL if unresolved
    satisfaction    INT,        -- score 1-5; NULL if not rated; 0 if declined to rate
    assigned_agent  VARCHAR(100) -- NULL if unassigned
);

INSERT INTO support_tickets VALUES
(1,  'Maria Chen',     'Billing',   'High',   45,   5,    'Jordan Kim'),
(2,  'David Park',     'Technical', 'Low',    120,  3,    'Alex Torres'),
(3,  'Sarah Johnson',  'Billing',   'Medium', NULL, NULL, NULL),
(4,  'Tom Wright',     'Account',   'High',   30,   4,    'Jordan Kim'),
(5,  'Lisa Nguyen',    'Technical', 'Medium', 95,   0,    'Alex Torres'),
(6,  'James Miller',   'Account',   'Low',    NULL, NULL, 'Jordan Kim'),
(7,  'Emma Davis',     'Technical', 'High',   200,  2,    NULL),
(8,  'Carlos Rivera',  'Billing',   'Medium', 60,   5,    'Alex Torres');

Task 1: Write a query that lists all tickets, showing 'Unassigned' instead of NULL for tickets with no agent, and 'Open' instead of NULL for resolution_time (just to flag it as open status rather than showing the minutes).

Task 2: Calculate the average resolution time per agent, treating unresolved tickets (NULL resolution_time) as excluded from the average — which is what SQL does naturally — but then write a second version that treats unresolved as 9999 minutes to show the "worst case" average.

Task 3: Calculate the average satisfaction score per agent, but exclude both NULL ratings and zero (declined to rate) from the average. Use NULLIF to convert zeroes to NULL before aggregating.

Task 4: Find all tickets that are either unresolved OR have an unassigned agent — and make sure your WHERE clause correctly identifies NULLs.

---

Common Mistakes & Troubleshooting

Mistake 1: Using = NULL instead of IS NULL

-- Wrong: returns no rows ever
WHERE assigned_agent = NULL

-- Right
WHERE assigned_agent IS NULL

If your filter isn't catching NULL rows, this is almost always the reason.

Mistake 2: Expecting COUNT(*) and COUNT(column) to be the same

SELECT COUNT(*), COUNT(resolution_time) FROM support_tickets;
-- Returns: 8, 5 (three unresolved tickets are excluded from second count)

Use COUNT(*) when you want total rows. Use COUNT(column) when you specifically want non-NULL counts.

Mistake 3: Forgetting that NULL is not equal to NULL

-- This also returns no rows
WHERE category_a = category_b
-- ...when both columns contain NULL

If you need to treat two NULL values as matching (such as in a FULL OUTER JOIN reconciliation), use:

WHERE category_a IS NOT DISTINCT FROM category_b  -- PostgreSQL
-- or
WHERE (category_a = category_b OR (category_a IS NULL AND category_b IS NULL))

Mistake 4: COALESCE with mismatched data types

-- This will error: can't coalesce INT and VARCHAR
SELECT COALESCE(resolution_time, 'Not resolved') FROM support_tickets;

-- Fix: cast to a compatible type
SELECT COALESCE(CAST(resolution_time AS VARCHAR), 'Not resolved') FROM support_tickets;

COALESCE requires all its arguments to be of compatible types. When mixing numbers and strings, you'll need an explicit cast.

Mistake 5: Using NULLIF with an expression that can never be equal

-- This does nothing useful: a string can never equal an integer
NULLIF(customer_name, 0)

NULLIF is only useful when both arguments are the same type and there's a realistic chance of equality.

---

Summary & Next Steps

NULL handling is one of those skills that separates queries that look right from queries that are right. Here's what you've learned to do:

• Recognize that NULL means unknown, not zero or empty — and that this changes how comparisons work
• Use IS NULL and IS NOT NULL as the only reliable way to filter on NULL values
• Replace NULL with meaningful defaults using COALESCE, including chaining multiple fallback options
• Create NULL from specific sentinel values using NULLIF, and prevent division-by-zero errors in the process
• Understand how NULL affects aggregates, joins, and arithmetic so your results don't silently mislead you

These three tools form a foundation you'll use constantly in real data work. The patterns here — COALESCE for safe arithmetic, NULLIF for division guards, IS NULL in WHERE clauses — will appear in reporting queries, ETL pipelines, and data validation scripts.

Where to go next:

• CASE expressions let you handle NULL and other conditional logic with more flexibility than COALESCE alone — and they're the backbone of dynamic column transformations
• Window functions interact with NULL in their own specific ways, particularly around ORDER BY within a window frame
• Data quality checks — writing systematic queries to audit NULL rates across columns before loading data into a warehouse or BI tool
• Database-specific NULL functions like NVL2 in Oracle or IIF in SQL Server offer shortcuts for specific patterns you'll encounter on those platforms

The goal isn't to memorize every edge case — it's to develop a habit of asking "what happens here if this value is NULL?" before you ship a query. Once that question becomes automatic, your work gets dramatically more reliable.