IS NULL and IS NOT NULL

Last Updated: June 12, 2026 • 16 min read

NULL represents the absence of a value — it is not zero, not an empty string, and not false. Understanding how to detect, handle, and avoid NULL is one of the most important skills in SQL.

1. Introduction

NULL is SQL's way of representing missing, unknown, or inapplicable data. It follows three-valued logic (TRUE, FALSE, UNKNOWN) that behaves differently from any other value. IS NULL and IS NOT NULL are the only correct operators for testing NULL — using = or <> with NULL always returns UNKNOWN, filtering out the row.

2. Why It Matters

Correct queries: Using = NULL instead of IS NULL silently returns zero rows — a bug that's hard to detect.
Accurate aggregates: COUNT(*) vs COUNT(column) produce different results when NULLs exist. Misunderstanding this leads to incorrect reports.
NULL propagation: Any arithmetic with NULL produces NULL — price * NULL = NULL — silently corrupting calculations.
Top interview topic: NULL handling, three-valued logic, and COALESCE are among the most frequently tested SQL concepts.

3. Real-World Analogy

Think of NULL as an empty field on a paper form. If someone leaves "middle name" blank, it doesn't mean their middle name is an empty string — it means the information wasn't provided. You can't compare two blank fields and say "these are the same person" (because two different people might both skip the field). Similarly, you can't ask "is this blank field equal to nothing?" — you can only ask "is this field blank?" That's the difference between = NULL (wrong) and IS NULL (correct).

4. How It Works

NULL represents three distinct concepts depending on context:

Unknown: The value exists but isn't known (e.g., a user's middle name that wasn't collected).
Not applicable: The value doesn't apply (e.g., a shipping address for a digital product).
Missing: The value should exist but wasn't provided (e.g., a required field left blank).

NULL comparison rules (three-valued logic):

NULL = NULL → UNKNOWN (not TRUE)
NULL <> NULL → UNKNOWN (not TRUE)
NULL = 0 → UNKNOWN (not FALSE)
WHERE col IS NULL → The only correct way to test for NULL

5. Internal Architecture

How the database engine handles NULL internally:

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Using = NULL or <> NULL: These always evaluate to UNKNOWN and return zero rows. Always use IS NULL / IS NOT NULL.
Confusing NULL with empty string: NULL means "no value exists" while '' means "a value exists and it is empty." They are fundamentally different.
Forgetting NULL in aggregates: AVG(salary) calculates average of non-NULL salaries only. If NULL means "zero salary," the average is wrong — use AVG(COALESCE(salary, 0)).

Interview Insight

The most common NULL interview question: "What does SELECT * FROM users WHERE phone = NULL return?" Answer: nothing. You must use IS NULL. Also know: NULL in COUNT, SUM, and comparison operators.

Common Pitfall

Comparing two nullable columns: WHERE a = b returns UNKNOWN when both are NULL. Use WHERE a IS NOT DISTINCT FROM b for NULL-safe equality.

9. Quick Quiz

Q1: What does SELECT COUNT(*) - COUNT(email) FROM users return?

A) 0 B) The number of rows where email IS NULL C) Error

Answer: B — COUNT(*) counts all rows, COUNT(email) counts only non-NULL. The difference is the NULL count.

Q2: What is the result of SELECT 10 + NULL?

A) 10 B) 0 C) NULL

Answer: C — NULL propagates through arithmetic. Any calculation involving NULL produces NULL.

10. Scenario-Based Challenge

Data Quality Audit

You have an orders table with columns: id, customer_id, product_id, quantity, discount, shipped_date. Some rows have NULL in discount, some have NULL in shipped_date, and some have NULL in customer_id (guest checkouts). Write queries to: (1) find all unshipped orders, (2) calculate average discount without treating NULL as zero, (3) find orders where quantity × NULL discount produces NULL revenue, and (4) produce a clean report using COALESCE for display.

11. Debugging Exercise

This query is supposed to find products where price and sale_price are different, but it returns zero rows for products where sale_price is NULL. Why?

12. Interview Questions

Q: What's the difference between NULL and an empty string?

A: NULL means "no value exists" (the cell is empty). Empty string means "a value exists and it happens to be empty" (the cell contains ""). NULL is excluded by IS NOT NULL, empty string is not. In storage, NULL uses a bitmap flag while empty string stores actual bytes.

Q: How does COALESCE differ from NULLIF?

A: COALESCE(a, b, ...) returns the first non-NULL argument — used for providing fallback values. NULLIF(a, b) returns NULL if a equals b, otherwise returns a — commonly used to prevent division by zero: total / NULLIF(count, 0).

Q: How does NULL affect JOINs and GROUP BY?

A: In JOINs, NULL = NULL evaluates to UNKNOWN, so rows with NULL keys won't match — use ON a IS NOT DISTINCT FROM b or COALESCE. In GROUP BY, NULL values are grouped together (all NULLs form one group), which differs from WHERE behavior.

13. Production Considerations

Prefer NOT NULL with defaults: Use DEFAULT 0, DEFAULT '', or DEFAULT FALSE when a sensible default exists. NULLs complicate queries and calculations.
Use NULL only when meaningful: NULL is appropriate when the value is genuinely unknown or inapplicable, not just as a default "empty" placeholder.
Handle NULL explicitly in queries: Use COALESCE or CASE WHEN for fallback values in reports and calculations — never let NULL propagate silently through arithmetic.
IS DISTINCT FROM for NULL-safe comparisons: WHERE a IS DISTINCT FROM b returns TRUE even when one side is NULL. Prefer this over manual (a = b) OR (a IS NULL AND b IS NULL) patterns.