SQL NULL Guide: How IS NULL, COUNT & COALESCE Actually Work

NULL is the single most misunderstood concept in SQL, and it is everywhere in real data. Customer emails go missing. Phone numbers are never collected. Cities are left blank. And every one of those gaps is a NULL waiting to break your query — not with an error, but with a confidently wrong answer.

This guide walks through every way NULL silently corrupts your results, using a real e-commerce dataset with 500 customers, 2,000 orders, and 5,000 line items. Every number here is verified against a live PostgreSQL database. For the broader SQL toolkit, see our SQL JOIN Fan-Out Guide and SQL CTE Guide.

1. NULL Means "Unknown," Not "Empty"

When a database says a value is NULL, it is telling you: I do not know this value. It is not saying the value is blank, zero, or a space. It is saying the information does not exist.

This distinction matters because NULL does not follow the normal rules of comparison. Every other value in SQL can be tested with = or <>. NULL cannot. Comparing NULL to anything — even NULL itself — returns "unknown," which SQL treats as "not true," which means the row gets excluded from your results.

Spotting NULLs in Your Data

SELECT customer_id, first_name, email, phone, city
FROM customers
ORDER BY (email IS NULL) DESC, (phone IS NULL) DESC, (city IS NULL) DESC
LIMIT 20;

This query floats the rows with missing values to the top using a clever trick: (email IS NULL) evaluates to true (1) for NULL rows and false (0) for the rest. Sorting by that in descending order puts all the NULL-containing rows first.

In our dataset, the first 20 rows reveal blank cells in email, phone, and city — those are NULLs, not empty strings.

2. The COUNT Trap: Your Numbers Are Quietly Wrong

This is the gotcha that catches every SQL learner, and it is a favorite in technical interviews.

The Setup

Simple question: how many of our 500 customers actually have an email on file? Let's count it two ways in one query:

SELECT COUNT(*)     AS total_rows,
       COUNT(email) AS has_email
FROM customers;

The Result

Same table, two counts, two different answers. Here's why:

• COUNT(*) counts every row — including rows where email is NULL. It returns 500.
• COUNT(email) counts only rows where email has a non-NULL value. It returns 443.

The gap is 57 customers with no email. COUNT(email) silently skipped them. No error. No warning. If you reported "we have 443 customers" based on COUNT(email), you'd be wrong, and nothing on screen would tell you.

3. The WHERE Trap: NULL Dodges Every Comparison

So you've noticed 57 customers are missing an email. Instinct says: filter where email equals an empty value.

Attempt 1: Equals Empty String

SELECT customer_id, email
FROM customers
WHERE email = '';

Result: 0 rows. An empty grid. But the counts just proved 57 are missing.

Attempt 2: Not Equals a Random Value

SELECT customer_id, email
FROM customers
WHERE email <> 'zzz@nope.com';

Result: 443 rows. The same 443 with emails. The 57 missing-email rows are absent again.

Why Both Fail

NULLs fall through every comparison because unknown is never equal-to or not-equal-to anything. Here is the logic:

WHERE only keeps rows where the condition evaluates to true. "Unknown" is not true, so NULL rows are silently excluded from every = and <> comparison. No error. It just hands you a wrong answer.

4. The Fix: IS NULL and IS NOT NULL

NULL has its own dedicated operators that do not use =:

Find the Missing Emails

SELECT customer_id, first_name, email
FROM customers
WHERE email IS NULL;

Result: 57 rows — exactly the gap between COUNT(*) and COUNT(email).

Find Customers WITH Emails

SELECT customer_id, email
FROM customers
WHERE email IS NOT NULL;

Result: 443 rows.

The rule of thumb: test for NULL with IS NULL or IS NOT NULL, never = NULL. The expression = NULL returns unknown and quietly matches nothing.

5. Empty String Is NOT NULL

This is the trap that confuses even experienced analysts. "Missing" can mean two different things in a database, and they are not interchangeable.

The Reviews Table Proof

Our reviews table has 800 rows. Some reviews have an empty text box — an empty string. Let's count those:

SELECT COUNT(*)
FROM reviews
WHERE review_text = '';

Result: 246. There really are empty-string reviews. Here, = '' works because these are genuinely empty strings, not NULL.

Now the same test with IS NULL:

SELECT COUNT(*)
FROM reviews
WHERE review_text IS NULL;

Result: 0. None of them are NULL.

The Contrast

An empty string is a value you have — it is just blank. NULL is a value you don't have — it is unknown. They look identical in a results grid, but they answer to completely different filters.

6. NULL Poisons Arithmetic

NULL does not just break filters — it corrupts calculations. When any value in an arithmetic expression is NULL, the entire result is NULL.

The Setup

Our order_items table has 5,000 rows. 169 of them have a NULL quantity. If we multiply quantity by unit price to get a line amount:

SELECT item_id, quantity, unit_price,
       quantity * unit_price AS raw_line_amount
FROM order_items
WHERE quantity IS NULL
ORDER BY item_id
LIMIT 10;

The Result

Every raw_line_amount is NULL, even though unit_price right next to it has a real number. One missing quantity poisons the whole calculation. This is technically correct — unknown times anything is unknown — but it is useless in a revenue report.

7. COALESCE: The Safety Net

COALESCE returns the first non-NULL value from a list of arguments. Think of it as: "use this, or if that's missing, use this instead."

Fixing Arithmetic

SELECT item_id, quantity,
       COALESCE(quantity, 0) AS quantity_clean,
       COALESCE(quantity, 0) * unit_price AS raw_line_amount
FROM order_items
WHERE quantity IS NULL
ORDER BY item_id
LIMIT 10;

Now quantity_clean reads 0, and raw_line_amount is 0 instead of NULL. The math survives.

Fixing Display Values

SELECT customer_id, first_name,
       COALESCE(city, 'Unknown') AS city
FROM customers
ORDER BY (city IS NULL) DESC, customer_id
LIMIT 20;

Blank cities now read "Unknown" instead of showing as empty.

The Judgment Call: When NOT to COALESCE

Replacing NULL with 0 is not always correct. A missing rating is not a rating of zero. If you COALESCE(rating, 0), you drag your average down and lie about the product.

The rule: use COALESCE to 0 for sums where "missing means nothing added." For averages, leave NULLs alone so they are excluded from the calculation.

8. NULL in Aggregates: How Each Function Treats It

Every aggregate function handles NULL differently, and knowing the difference prevents silent errors:

The difference between AVG(rating) = 2.941 and AVG(COALESCE(rating, 0)) = 2.625 is significant. The first says "the average rating among people who rated." The second says "the average rating assuming non-raters gave zero." Only the first is honest.

9. Quick Reference: NULL Rules Cheatsheet

10. Practice NULL Handling on Real Databases

Understanding NULL conceptually is the first step. Writing IS NULL queries fluently under interview pressure requires hands-on practice.