SQL NULL Handling: IS NULL, COALESCE, and IFNULL

SQL NULL handling is one of the trickiest concepts for beginners — and one of the fastest ways to level up your queries. In this guide, you'll learn how IS NULL, COALESCE, and IFNULL work with real Chinook database examples.

Why NULL Trips Up Every SQL Beginner

If you've ever written WHERE column = NULL and gotten zero rows back, you're not alone. NULL in SQL doesn't mean "empty" or "zero" — it means unknown. And because it's unknown, almost every comparison with NULL returns NULL (which is treated as false).

Understanding NULL handling is one of the fastest ways to level up your SQL skills. In this guide, you'll learn exactly how NULLs behave, how to test for them, and how to replace them with meaningful values — all using real examples from the Chinook database.

Table of Contents

1. What Is NULL in SQL?
2. Testing for NULL: IS NULL and IS NOT NULL
3. Replacing NULLs with COALESCE
4. IFNULL: The Two-Argument Shortcut
5. NULLs in Aggregations and GROUP BY
6. NULL and the NOT IN Trap
7. Cheat Sheet
8. Common Mistakes
9. Practice Exercises
10. Summary
11. FAQs

What Is NULL in SQL?

NULL represents a missing or unknown value. It is not the same as zero, an empty string, or false. Think of it as SQL's way of saying "we don't know."

The key rule: any expression involving NULL evaluates to NULL, not true or false.

SELECT 1 = NULL;    -- Returns NULL (not false)
SELECT NULL = NULL;  -- Returns NULL (not true)
SELECT NULL <> NULL;  -- Returns NULL (not false)

This is why WHERE column = NULL never matches any rows. SQL can't confirm that an unknown value equals another unknown value.

Testing for NULL: IS NULL and IS NOT NULL

Since = doesn't work with NULL, SQL provides the IS NULL and IS NOT NULL operators.

Find tracks with no composer listed

The Chinook database's Track table has a Composer column that is NULL for many tracks. Let's find them:

SELECT Name, Composer, Milliseconds
FROM Track
WHERE Composer IS NULL
ORDER BY Milliseconds DESC
LIMIT 5;

Find tracks that DO have a composer

SELECT Name, Composer
FROM Track
WHERE Composer IS NOT NULL
ORDER BY Name
LIMIT 5;

Tip: Always use IS NULL or IS NOT NULL — never = NULL or <> NULL. This is the single most common NULL mistake in SQL.

Try it yourself — practice finding missing data in our SQL exercises.

Replacing NULLs with COALESCE

COALESCE returns the first non-NULL value from a list of arguments. It's the standard, portable way to provide fallback values.

Syntax: COALESCE(value1, value2, ..., default_value)

Display "Unknown" instead of NULL for composers

SELECT
  Name,
  COALESCE(Composer, 'Unknown') AS Composer
FROM Track
ORDER BY TrackId
LIMIT 5;

Chain multiple fallbacks

COALESCE really shines when you have multiple columns that might be NULL:

SELECT
  c.FirstName,
  c.LastName,
  COALESCE(c.Company, c.City, 'No info') AS Organization
FROM Customer c
ORDER BY c.CustomerId
LIMIT 5;

Here COALESCE checks Company first. If that's NULL, it falls back to City. If both are NULL, it shows "No info". This pattern is common in reports and dashboards.

Want more on conditional logic? See our GROUP BY and HAVING guide.

IFNULL: The Two-Argument Shortcut

IFNULL(expression, default) works like COALESCE but only accepts two arguments. It's supported in SQLite and MySQL.

SELECT
  Name,
  IFNULL(Composer, 'Unknown') AS Composer
FROM Track
ORDER BY TrackId
LIMIT 5;

IFNULL vs COALESCE — When to use which?

Bottom line: COALESCE is the safer, more portable choice. Use IFNULL only when you're certain you'll stay on SQLite or MySQL and want slightly more readable code for simple two-value cases.

NULLs in Aggregations and GROUP BY

Aggregate functions like COUNT, SUM, and AVG handle NULLs in specific ways that can surprise you.

COUNT(*) vs COUNT(column)

SELECT
  COUNT(*) AS total_tracks,
  COUNT(Composer) AS tracks_with_composer
FROM Track;

• COUNT(*) counts all rows, including those with NULL composers
• COUNT(Composer) only counts rows where Composer is not NULL

This 978-track difference is entirely due to NULLs. Understanding this distinction is critical for accurate reporting.

AVG ignores NULLs

SELECT
  ROUND(AVG(Milliseconds), 0) AS avg_all_tracks,
  ROUND(AVG(CASE WHEN Composer IS NOT NULL THEN Milliseconds END), 0) AS avg_with_composer,
  ROUND(AVG(CASE WHEN Composer IS NULL THEN Milliseconds END), 0) AS avg_without_composer
FROM Track;

Tracks without a listed composer tend to be longer — interesting! AVG automatically skips NULLs, which is usually what you want. But if you need to treat NULLs as zero, wrap the column in COALESCE(column, 0).

Dive deeper into aggregations in our GROUP BY and HAVING guide.

NULL and the NOT IN Trap

This is one of SQL's most dangerous gotchas, and it catches even experienced developers.

The problem

When a subquery returns any NULL values, NOT IN returns no rows at all:

-- This might return zero rows unexpectedly!
SELECT Name
FROM Artist
WHERE ArtistId NOT IN (
  SELECT ArtistId FROM Album
);

Why? Because NOT IN checks value <> x AND value <> y AND value <> NULL. Since value <> NULL is NULL, the entire AND chain becomes NULL, which is treated as false.

The fix: Use NOT EXISTS instead

SELECT a.Name
FROM Artist a
WHERE NOT EXISTS (
  SELECT 1 FROM Album al
  WHERE al.ArtistId = a.ArtistId
)
ORDER BY a.Name
LIMIT 5;

NOT EXISTS is NULL-safe and generally performs better too. For a deep dive into this topic, check out our subqueries vs JOINs guide which covers this exact pattern.

Practice NULL-safe queries in our exercises.

Cheat Sheet

Common Mistakes

1. Using = NULL instead of IS NULL

-- WRONG: Returns zero rows
SELECT * FROM Track WHERE Composer = NULL;

-- CORRECT: Returns all tracks without a composer
SELECT * FROM Track WHERE Composer IS NULL;

This is the number one NULL mistake. The = operator cannot compare with NULL because NULL means unknown.

2. Forgetting NULLs in NOT IN subqueries

-- DANGEROUS: If subquery returns any NULL, you get zero rows
SELECT * FROM Artist WHERE ArtistId NOT IN (SELECT ArtistId FROM Album);

-- SAFE: NOT EXISTS handles NULLs correctly
SELECT * FROM Artist a WHERE NOT EXISTS (SELECT 1 FROM Album al WHERE al.ArtistId = a.ArtistId);

Always prefer NOT EXISTS over NOT IN when the subquery column could contain NULLs. Read more in our subqueries guide.

3. Confusing COUNT(*) with COUNT(column)

COUNT(*) counts all rows. COUNT(column) skips NULLs. Using the wrong one leads to incorrect totals in reports.

4. Assuming COALESCE checks for empty strings

-- This does NOT catch empty strings
COALESCE('', 'fallback');  -- Returns '' (empty string), NOT 'fallback'

-- To handle both NULL and empty string:
COALESCE(NULLIF(column, ''), 'fallback');

COALESCE only replaces NULL, not empty strings. Use NULLIF(column, '') to convert empty strings to NULL first.

5. NULLs in ORDER BY surprises

NULLs sort first in ascending order in most databases. If you need them last:

SELECT Name, Composer
FROM Track
ORDER BY Composer IS NULL, Composer
LIMIT 5;

This sorts non-NULL composers alphabetically first, then NULLs at the end.

Practice Exercises

Ready to test your NULL handling skills? These exercises on SQiLs will help you master the patterns from this guide:

Start with exercise #636 — it directly applies the IS NULL pattern you learned in this guide.

Browse all SQL exercises on SQiLs.

Summary

NULL is SQL's way of representing unknown or missing data. Here are the key takeaways:

• Never use = NULL — always use IS NULL or IS NOT NULL
• COALESCE is your go-to for replacing NULLs with default values, and it works in every database
• IFNULL is a simpler two-argument alternative for SQLite and MySQL
• COUNT(column) skips NULLs while COUNT(*) counts all rows
• NOT IN breaks when subqueries return NULLs — use NOT EXISTS instead
• AVG, SUM, MIN, MAX all ignore NULL rows automatically

Master these patterns and you'll avoid the most common bugs in SQL queries. The exercises above give you hands-on practice with each concept.

FAQs

Is NULL the same as an empty string in SQL?

No. NULL means "unknown/missing" while an empty string ('') is a known value that happens to contain no characters. IS NULL won't match empty strings, and COALESCE won't replace them. Use NULLIF(column, '') to convert empty strings to NULL if you need to treat them the same way.

Does COALESCE work in all SQL databases?

Yes. COALESCE is part of the SQL standard and works in PostgreSQL, MySQL, SQLite, SQL Server, Oracle, and every other major database. IFNULL is the non-standard alternative limited to SQLite and MySQL. Always prefer COALESCE for portable code.

Why does NOT IN fail with NULLs?

NOT IN (1, 2, NULL) expands to value <> 1 AND value <> 2 AND value <> NULL. Since value <> NULL evaluates to NULL, the entire expression becomes NULL (falsy), so no rows match. Use NOT EXISTS which evaluates each row independently and handles NULLs correctly. Our subqueries vs JOINs guide covers this in detail.

How do I check for NULL in a CASE statement?

Use WHEN column IS NULL, not WHEN column = NULL:

SELECT
  Name,
  CASE
    WHEN Composer IS NULL THEN 'No composer listed'
    ELSE Composer
  END AS Composer
FROM Track
LIMIT 3;

This is equivalent to COALESCE(Composer, 'No composer listed') but gives you more control when you need multiple conditions.