SQL NULL Handling Explained

SQL NULL is one of the most important concepts in database work because it appears everywhere and causes confusion very quickly if you do not understand how it behaves.

It shows up in questions like:

• why did this WHERE filter return nothing?
• why is COUNT(column_name) smaller than expected?
• why did this LEFT JOIN return NULL values?
• why does = NULL not work?
• why did my NOT IN query behave strangely?
• why did this average ignore some rows?
• why is a missing discount not the same as zero?

These are not edge cases. They are everyday SQL problems.

That is why NULL handling is such a core SQL skill.

This guide explains SQL NULL clearly, including:

• what NULL really means
• why it is not a normal value
• how it affects comparisons
• how it changes filters and joins
• how it affects aggregates
• how COALESCE works
• how CASE and sorting interact with NULL
• and the common mistakes that make queries silently wrong

Why SQL NULL matters so much

A lot of SQL bugs are not syntax bugs. They are meaning bugs.

The query runs. The database returns rows. But the result is wrong because the developer did not think carefully about missing values.

That happens because NULL is not just another value. It changes the logic of SQL expressions.

For example:

• a missing discount is not always the same as zero
• a missing payment date is not the same as an unpaid invoice unless the business rules say so
• a missing profile row is not the same as an empty profile value
• a missing category may mean unknown, unassigned, or not applicable depending on the data model

So NULL handling is not only technical. It is also about business meaning.

That is why strong SQL work requires deliberate NULL handling instead of casual assumptions.

The most important rule

Before anything else, remember this:

NULL in SQL means missing, unknown, or not applicable, and it does not behave like an ordinary value in comparisons.

That is the single most important rule in this entire topic.

A lot of beginner confusion comes from assuming NULL behaves like:

• 0
• ''
• false
• or a blank placeholder

It does not.

That is why queries like:

WHERE column_name = NULL

do not behave the way people expect.

If you understand only one thing from this article, make it this:

• NULL is special
• and you must handle it intentionally

What NULL means in SQL

A NULL value usually means one of these:

• the value is missing
• the value is unknown
• the value has not been provided
• the value does not apply to this row

These are similar, but not always identical in business meaning.

For example:

• middle_name might be NULL because the user has no middle name
• shipped_at might be NULL because the order has not shipped yet
• discount_amount might be NULL because the discount was not calculated yet
• manager_id might be NULL because the employee has no manager
• cancelled_reason might be NULL because the order was never cancelled

So NULL is often a data-model signal, not just a technical placeholder.

That is why NULL handling should reflect business meaning whenever possible.

NULL is not zero

This is one of the most important distinctions.

Suppose discount_amount is NULL.

That does not automatically mean:

• the discount is 0

It may mean:

• no discount exists
• discount not yet assigned
• discount not recorded
• value missing from source data

Only the business logic can tell you which interpretation is correct.

That is why replacing NULL with zero blindly can change the meaning of the data.

NULL is not an empty string

Another common mistake is assuming NULL and empty string are the same.

They are not.

An empty string means:

• there is a value, and it is empty text

NULL means:

• no value is present

Those are different states.

For example:

• phone_number = '' may mean the field was intentionally stored as empty text
• phone_number IS NULL means no phone number value exists in the row

That difference matters in filters, counts, and validation logic.

NULL is not false

NULL also does not simply mean false.

For example, a nullable boolean-like field may have three meaningful states:

• true
• false
• unknown or not yet set

That third state is often why NULL exists in the first place.

So when you see NULL in a boolean-like column, always ask:

• does this mean false?
• or does it mean unknown / not applicable / not yet decided?

That distinction matters a lot in backend systems and analytics.

Why = NULL does not work

One of the most famous SQL beginner mistakes is writing:

WHERE column_name = NULL

This does not work the way people expect because NULL is not treated like a normal comparable value.

Instead, you must use:

WHERE column_name IS NULL

And to find non-NULL values:

WHERE column_name IS NOT NULL

That is the correct SQL pattern.

The correct way to test for NULL

Find NULL values

SELECT *
FROM users
WHERE phone_number IS NULL;

Find non-NULL values

SELECT *
FROM users
WHERE phone_number IS NOT NULL;

These are the correct forms.

That is one of the most fundamental SQL habits you should build.

Why comparison with NULL is tricky

In SQL, comparisons involving NULL do not behave like ordinary true-or-false comparisons.

If a value is unknown, then SQL often treats expressions like:

• column = NULL
• column <> NULL

as not producing ordinary truth in the way beginners expect.

The safe practical rule is simple:

• never use = NULL
• never use <> NULL
• always use IS NULL or IS NOT NULL

That is the rule that keeps you correct most of the time.

Basic NULL filter examples

Find orders not yet shipped

SELECT *
FROM orders
WHERE shipped_at IS NULL;

Find orders that have shipped

SELECT *
FROM orders
WHERE shipped_at IS NOT NULL;

Find employees with no manager

SELECT *
FROM employees
WHERE manager_id IS NULL;

Find products with a defined category

SELECT *
FROM products
WHERE category_id IS NOT NULL;

These are very common real-world SQL patterns.

How NULL affects aggregate functions

NULL changes aggregate behavior in important ways.

This is one of the biggest sources of reporting confusion.

COUNT(*)

Counts rows.

COUNT(column_name)

Counts non-NULL values in that column.

AVG(column_name)

Ignores NULL values.

SUM(column_name)

Ignores NULL values.

MIN(column_name) and MAX(column_name)

Usually ignore NULL values too.

That means missing data can change aggregate results without producing an obvious error.

COUNT(*) vs COUNT(column_name)

This is one of the most important NULL-related differences.

Suppose you have this table:

employee_id	bonus_amount
1	500
2	NULL
3	1000

Then:

SELECT COUNT(*)
FROM employees;

returns:

• 3

But:

SELECT COUNT(bonus_amount)
FROM employees;

returns:

• 2

Why?

Because COUNT(bonus_amount) ignores NULL bonus values.

This is one of the most common SQL interview and reporting questions for a reason. It matters a lot in real queries.

AVG ignores NULL values

Suppose the same bonus values are:

• 500
• NULL
• 1000

Then:

SELECT AVG(bonus_amount)
FROM employees;

returns:

• 750

Why not 500?

Because SQL averages only the non-NULL values:

• (500 + 1000) / 2

The NULL row is ignored.

This is often exactly what you want. But sometimes it is not.

That is why you should always ask:

• should NULL values be ignored here?
• or should they be treated as zero?
• or should the missing values be handled another way?

That is a business question, not only a SQL question.

SUM ignores NULL values

Similarly:

SELECT SUM(bonus_amount)
FROM employees;

adds only the non-NULL values.

This often works well. But again, you need to think about meaning.

If NULL means:

• bonus not yet assigned

then ignoring it may be appropriate.

If NULL means:

• bonus should be treated as 0 for a report

then you may need explicit handling.

That is where COALESCE often comes in.

COALESCE explained

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

This is one of the most useful NULL-handling functions in SQL.

Example:

SELECT
    customer_name,
    COALESCE(phone_number, 'No phone number') AS phone_display
FROM customers;

If phone_number is NULL, SQL returns:

• 'No phone number'

Otherwise it returns the actual phone number.

That is basic COALESCE behavior.

Why COALESCE is so useful

COALESCE helps with:

• replacing NULL in display output
• turning NULL into 0 for calculations where that is correct
• falling back from one column to another
• handling optional fields cleanly

Examples:

• COALESCE(discount_amount, 0)
• COALESCE(nickname, full_name)
• COALESCE(city, 'Unknown')

It is one of the most common and useful SQL functions for everyday work.

COALESCE with numeric values

Example:

SELECT
    order_id,
    total_amount,
    COALESCE(discount_amount, 0) AS discount_amount
FROM orders;

This is useful only if NULL truly means:

• no discount

If NULL instead means:

• discount not calculated yet

then replacing it with 0 changes the meaning.

That is why COALESCE is powerful but should be used deliberately.

COALESCE with text values

Example:

SELECT
    user_id,
    COALESCE(display_name, username, 'Anonymous') AS shown_name
FROM users;

This means:

• use display_name if present
• otherwise use username
• otherwise use 'Anonymous'

This is a very practical SQL pattern.

It shows that COALESCE is not only for replacing NULL with constants. It can also create fallback chains.

CASE and NULL handling

CASE is another important tool when NULL logic affects categorization.

Example:

SELECT
    order_id,
    CASE
        WHEN shipped_at IS NULL THEN 'Not Shipped'
        ELSE 'Shipped'
    END AS shipping_status
FROM orders;

This is a very common way to convert NULL-based logic into readable labels.

You can also combine NULL logic with other conditions:

SELECT
    invoice_id,
    CASE
        WHEN paid_at IS NOT NULL THEN 'Paid'
        WHEN due_date < CURRENT_DATE THEN 'Overdue'
        ELSE 'Open'
    END AS invoice_status
FROM invoices;

This is a good example of real business logic using NULL correctly.

NULL in LEFT JOIN results

One of the most common places NULL appears is after a LEFT JOIN.

Example:

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

If a customer has no order, then:

• o.order_id becomes NULL

That does not mean the query failed. It means:

• the left-side row was preserved
• but no right-side match existed

This is how LEFT JOIN works.

Using NULL after LEFT JOIN to find missing matches

This is a very common SQL pattern.

Example: find customers with no orders.

SELECT
    c.customer_id,
    c.customer_name
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This works because unmatched right-side rows produce NULL values.

This is one of the most useful NULL-based query patterns in SQL.

Why NULL in JOIN results matters for COUNT

Suppose you want order counts per customer.

This query is correct:

SELECT
    c.customer_id,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

Why use COUNT(o.order_id) instead of COUNT(*)?

Because for customers with no orders:

• o.order_id is NULL
• COUNT(o.order_id) returns 0
• but COUNT(*) would still count the joined row itself

This is one of the most important NULL-related reporting details.

NULL in ORDER BY

NULL also affects sorting.

If a column contains NULL values and you sort it, those NULLs must appear somewhere in the order.

Where they appear can depend on:

• the database engine
• ascending or descending order
• and explicit NULL sorting options if supported

Example conceptually:

SELECT *
FROM tasks
ORDER BY due_date;

Tasks with NULL due_date values may appear at the beginning or end depending on the system.

This matters in real apps because:

• unscheduled items
• unassigned dates
• or missing values

may sort in surprising ways if you do not think about NULL behavior.

NULL and boolean logic in WHERE

NULL also affects logical expressions.

For example, conditions like:

• column > 10
• column = 'Paid'
• column <> 'Cancelled'

do not behave as though NULL automatically passes or fails the way ordinary values do.

The practical lesson is:

• rows with NULL in the compared column may not satisfy the condition the way beginners assume

That is why explicit NULL handling is often safer when missing values matter.

Example:

SELECT *
FROM invoices
WHERE paid_at IS NULL;

is much clearer than relying on indirect logic.

NULL and NOT IN

This is one of the most important SQL NULL traps.

Suppose you write:

SELECT *
FROM users
WHERE user_id NOT IN (
    SELECT manager_id
    FROM employees
);

If the subquery returns NULL anywhere, the result can behave in a way many people do not expect.

This is one of the reasons NOT IN can be dangerous when NULL values are involved.

A safer alternative in many cases is:

• filter NULLs explicitly in the subquery
• or use NOT EXISTS

For example:

SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.manager_id = u.user_id
);

This is a very important practical SQL lesson.

Why NOT IN with NULL causes confusion

The core issue is that NULL introduces uncertainty into comparisons.

If the subquery list contains NULL, SQL cannot always treat the NOT IN comparison as a simple yes-or-no match in the way beginners expect.

So a strong habit is:

• be very careful with NOT IN when the subquery can return NULL
• use NOT EXISTS or explicit NULL filtering when needed

This prevents a lot of hard-to-debug query results.

NULL and data modeling

Some NULL problems are actually schema design problems.

A good schema should make it reasonably clear when NULL is appropriate.

Examples where NULL often makes sense:

• shipped_at before shipping
• manager_id for top-level managers
• deleted_at for active rows
• cancelled_reason only for cancelled items

Examples where NULL may be questionable:

• fields that should always exist
• business-critical values with no clear missing-state meaning
• flags where a third state is not intended

A strong data model uses NULL intentionally, not carelessly.

Nullable columns should have clear meaning

A good database design question is:

If this column is NULL, what does that mean?

If the answer is unclear, the model may need improvement.

Good answers might be:

• not yet shipped
• not applicable
• unknown from source system
• optional field not provided
• no assigned manager

Weak answers sound like:

• I do not know
• maybe zero
• maybe blank
• we just left it that way

That is how NULL confusion spreads through systems.

Common SQL NULL mistakes

There are a few classic mistakes that show up constantly.

1. Using = NULL instead of IS NULL

Wrong:

WHERE phone_number = NULL

Correct:

WHERE phone_number IS NULL

2. Assuming NULL equals zero or empty string

It does not.

3. Forgetting that aggregates ignore NULL values

This causes wrong counts and averages.

4. Using COUNT(*) after a LEFT JOIN when you really want count of matching right-side rows

This often turns zero into one.

5. Using COALESCE without checking business meaning

Replacing NULL with 0 or 'Unknown' can be useful, but it can also distort the data meaning.

6. Using NOT IN when NULL may appear in the subquery

This causes many subtle bugs.

Best practices for NULL handling

A few habits make SQL NULL handling much safer.

1. Use IS NULL and IS NOT NULL explicitly

This is the most basic rule.

2. Decide what NULL means in the business context

Do not guess. Be explicit.

3. Use COALESCE only when the fallback value is semantically correct

Do not treat it as a cosmetic patch.

4. Be careful with aggregates

Know when NULL values are being ignored.

5. Handle LEFT JOIN NULLs intentionally

Especially in counts and filters.

6. Be cautious with NOT IN

Prefer NOT EXISTS when NULL may be involved.

7. Design nullable columns intentionally

A nullable field should have a clear meaning.

Practical examples

Example 1: find rows with missing values

SELECT *
FROM customers
WHERE phone_number IS NULL;

Example 2: replace missing discount with zero for reporting

SELECT
    order_id,
    total_amount,
    COALESCE(discount_amount, 0) AS discount_amount
FROM orders;

Example 3: count non-NULL emails

SELECT COUNT(email)
FROM users;

Example 4: show all customers and how many orders they have

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.customer_name;

Example 5: classify missing ship dates

SELECT
    order_id,
    CASE
        WHEN shipped_at IS NULL THEN 'Pending Shipment'
        ELSE 'Shipped'
    END AS shipping_status
FROM orders;

These are all common real-world NULL patterns.

FAQ

What does NULL mean in SQL?

NULL in SQL represents a missing, unknown, or not applicable value. It is not the same as zero, false, or an empty string.

Why does = NULL not work in SQL?

Because NULL does not behave like an ordinary value in comparisons. To test for NULL, you use IS NULL or IS NOT NULL instead of = NULL or <> NULL.

How does NULL affect COUNT and AVG in SQL?

COUNT(column_name) ignores NULL values in that column, while COUNT(*) counts rows. AVG(column_name) also ignores NULL values.

What is COALESCE used for in SQL?

COALESCE returns the first non-NULL value from a list of expressions. It is commonly used to replace NULL with a fallback value such as 0, 'Unknown', or another column.

Final thoughts

SQL NULL handling is one of the most important practical SQL skills because NULL affects:

• filters
• joins
• counts
• averages
• sorting
• conditional logic
• and even schema design

That is why the best way to think about NULL is simple:

• it is not a normal value
• it represents missing or unknown meaning
• and it must be handled intentionally

The most important habits to keep are:

• use IS NULL and IS NOT NULL
• do not assume NULL means zero or blank
• understand how aggregates treat NULL
• handle LEFT JOIN NULLs carefully
• use COALESCE deliberately
• and be cautious with NOT IN when NULL may be present

If you understand those ideas clearly, a huge number of confusing SQL bugs become much easier to prevent. And that is exactly why strong NULL handling matters so much in real SQL work.