IS NULL and IS NOT NULL in SQL: Handling NULL Values in Queries

NULL is one of SQL's most misunderstood concepts — it doesn't mean zero, an empty string, or false; it means 'unknown' or 'no value at all.' This special nature means NULL can't be checked using the ordinary = or != operators, which is exactly why SQL provides the dedicated IS NULL and IS NOT NULL operators.

What Is NULL, and Why Does It Need Special Operators?

NULL represents the absence of a value in a column — it is not the same as zero, an empty string, or false; it genuinely means the value is unknown or not applicable. Because comparing anything to NULL using = or != always evaluates to unknown rather than true or false, SQL provides the dedicated IS NULL and IS NOT NULL operators specifically to test for NULL correctly.

What You'll Learn

• Understand why = and != don't work for checking NULL values.
• Use IS NULL to find rows missing a value in a column.
• Use IS NOT NULL to find rows that do have a value.
• Recognize how NULL affects logical operators and aggregate functions.

Key Terms to Know

• NULL: A special marker representing an unknown or absent value, distinct from zero or an empty string.
• IS NULL: An operator that correctly tests whether a column's value is NULL.
• IS NOT NULL: An operator that correctly tests whether a column's value is not NULL.
• Three-valued logic: SQL's logic system where conditions can evaluate to true, false, or unknown (as with NULL comparisons).

Why WHERE column = NULL Never Works

In SQL, NULL represents an unknown value, and comparing anything — including another NULL — to an unknown value using = produces an unknown result, not true. This means WHERE column = NULL; will never match any row, even rows where that column genuinely is NULL, which surprises many people encountering this for the first time.

This isn't a bug; it's a deliberate consequence of SQL's three-valued logic, where every condition evaluates to true, false, or unknown, and only conditions evaluating to true cause a row to be included in the result.

Using IS NULL and IS NOT NULL Correctly

WHERE column IS NULL; correctly returns every row where that column has no value at all. WHERE column IS NOT NULL; correctly returns every row where the column does have some actual value, regardless of what that value is. These are the only reliable ways to test for the presence or absence of NULL in standard SQL.

For example, WHERE shipped_date IS NULL; on an orders table would correctly find every order that hasn't shipped yet, assuming shipped_date is only populated once an order actually ships.

How NULL Affects Logical Operators and Aggregates

NULL's unknown nature ripples into logical operators too: TRUE AND NULL evaluates to unknown (treated as not matching), while TRUE OR NULL evaluates to true, since OR only needs one true side regardless of the other. This is part of why the earlier NOT IN NULL gotcha occurs.

Most aggregate functions like SUM, AVG, and COUNT(column) automatically ignore NULL values when calculating their result, rather than including them as zero — which is exactly why COUNT(*) (counting rows) and COUNT(column) (counting non-NULL values in that column) can return different numbers on the same table.

Visual Summary

Picture a stack of feedback forms where a few respondents simply left the 'rating' field completely blank rather than writing 0. = NULL is like trying to find the blank forms by asking 'does this form's rating equal blank?' — a question that doesn't even make logical sense to ask that way, so it never finds anything. IS NULL is the correct, direct question: 'is this form's rating field actually blank?' — which correctly identifies every blank form.

NULL-Related Comparisons

Expression	Result
column = NULL	Always unknown (never matches), even if column is NULL
column IS NULL	True if column has no value
column IS NOT NULL	True if column has any actual value
COUNT(*)	Counts all rows, including those with NULL columns
COUNT(column)	Counts only rows where that column is not NULL

SQL Example

-- Incorrect: this will never match rows, even ones that are actually NULL
SELECT * FROM orders WHERE shipped_date = NULL; -- always returns zero rows

-- Correct: find orders that haven't shipped yet
SELECT order_id, order_date
FROM orders
WHERE shipped_date IS NULL;

-- Correct: find orders that have shipped
SELECT order_id, shipped_date
FROM orders
WHERE shipped_date IS NOT NULL;

-- Demonstrating COUNT(*) vs COUNT(column) with NULLs present
SELECT COUNT(*) AS total_orders, COUNT(shipped_date) AS shipped_orders
FROM orders;

The first commented query demonstrates the classic mistake: it's syntactically valid but logically broken, returning zero rows no matter what. The second and third queries show the correct IS NULL and IS NOT NULL usage for finding unshipped versus shipped orders. The final query illustrates how COUNT(*) counts every row regardless of NULLs, while COUNT(shipped_date) only counts rows where that specific column actually has a value.

Real-World Examples

• Order management systems use shipped_date IS NULL to identify orders still pending fulfillment.
• CRM systems use last_contacted_at IS NULL to find leads that have never been followed up with.
• Profile completion features use WHERE profile_photo_url IS NULL to identify users who haven't uploaded a photo yet.
• Data quality audits use IS NULL checks across multiple required-in-practice columns to find incomplete records needing cleanup.

Best Practices and Pro Tips

• Never use = NULL or != NULL — train yourself to immediately reach for IS NULL or IS NOT NULL whenever NULL is involved in a condition.
• Remember that COUNT(column) and COUNT(*) can legitimately return different numbers on the same table whenever that column contains any NULLs — this is expected behavior, not a bug.
• When designing a schema, consider whether a column should really allow NULL at all, or whether a NOT NULL constraint with a sensible DEFAULT value would model the data more accurately and simplify queries.

Common Mistakes to Avoid

• Writing WHERE column = NULL or WHERE column != NULL, both of which always evaluate to unknown and never behave as intended.
• Assuming NULL is the same as zero or an empty string, leading to incorrect aggregate calculations or filtering logic.
• Forgetting that NULL can silently affect AND/OR logic and the NOT IN operator, as covered in earlier lessons in this module.
• Using AVG(column) without realizing NULL rows are excluded from the average's denominator entirely, which can produce a misleadingly different result than expected if NULLs were assumed to count as zero.

Interview Questions

Q1. Why doesn't WHERE column = NULL work as a way to find NULL values?

Because comparing anything to NULL with = produces an unknown result rather than true, even when the column genuinely contains NULL. SQL's three-valued logic means only conditions evaluating to true are included in the result, so this comparison never matches.

Q2. What is the correct way to check whether a column contains a value at all?

Use IS NOT NULL, such as WHERE shipped_date IS NOT NULL;, which correctly tests for the presence of any actual value in that column.

Q3. Why might COUNT(*) and COUNT(column) return different results on the same table?

COUNT(*) counts every row regardless of NULL values, while COUNT(column) only counts rows where that specific column is not NULL, so any NULLs present in that column cause the two counts to diverge.

Q4. How does NULL affect AND and OR logic?

NULL introduces an 'unknown' result into three-valued logic: TRUE AND NULL evaluates to unknown (excluded from results), while TRUE OR NULL evaluates to true, since OR only needs one definitively true side.

Practice MCQs

1. Which condition correctly finds rows where a column has no value?

1. WHERE column = NULL
2. WHERE column != NULL
3. WHERE column IS NULL
4. WHERE column = ''

Answer: C. WHERE column IS NULL

Explanation: IS NULL is the only reliable, correct operator for testing whether a column contains NULL.

2. What does WHERE column = NULL always return?

1. Every row where column is NULL
2. Zero rows, regardless of the data
3. An error
4. Every row in the table

Answer: B. Zero rows, regardless of the data

Explanation: Comparing to NULL with = always evaluates to unknown, never true, so this condition never matches any row, even ones that are actually NULL.

3. Why might AVG(rating) differ from what you'd expect if some rating values are NULL?

1. AVG treats NULL as zero automatically
2. AVG excludes NULL rows entirely from both the sum and the count
3. AVG throws an error when NULL is present
4. NULL values are converted to the column's average automatically

Answer: B. AVG excludes NULL rows entirely from both the sum and the count

Explanation: Aggregate functions like AVG ignore NULL values rather than treating them as zero, which changes both the sum and the denominator used in the average calculation.

Quick Revision Points

• NULL means unknown/absent, not zero or empty string.
• = NULL and != NULL always evaluate to unknown; use IS NULL / IS NOT NULL instead.
• NULL introduces three-valued logic (true, false, unknown) into AND/OR conditions.
• Aggregate functions like SUM, AVG, and COUNT(column) ignore NULL values by default.

Conclusion

• Understanding NULL correctly is one of the most foundational, frequently-tested concepts in all of SQL.
• IS NULL and IS NOT NULL are the only correct tools for testing NULL, full stop.
• NULL's effect on aggregates and logical operators explains several otherwise-confusing SQL behaviors.

NULL represents a genuinely unknown or absent value, fundamentally different from zero or an empty string, and this distinction is why = and != can never reliably test for it — IS NULL and IS NOT NULL exist specifically to fill that gap. Understanding NULL's three-valued logic also explains why aggregate functions behave the way they do, and why operators like NOT IN can fail silently in its presence. With filtering fully covered across this and the previous several lessons, the next lesson moves to ORDER BY — controlling the order in which results are returned.