Ravindra BagaleCourses & study guides Track your progress

7. NULL Values: Missing Data Done Right

7.3 Why = NULL Never Works (Common Beginner Trap)

By Last updated:

Why. = NULL is a famous trap in interviews and real bugs.

What. In SQL, NULL = NULL is not TRUE. Comparisons with NULL yield unknown. Use IS NULL.

How. Wrong: WHERE note = NULL. Right: WHERE note IS NULL.

Steps

  1. Run the wrong form — see empty or odd results.
  2. Run IS NULL — see real missing rows.
  3. Remember the interview one-liner.

Examples

Example 1 · dataset: raja_coffee

SELECT COUNT(*) AS wrong_equals_null
FROM orders
WHERE note = NULL;

Output

wrong_equals_null
0

Meaning. Usually 0 — = NULL does not find NULLs.

Example 2 · dataset: raja_coffee

SELECT COUNT(*) AS right_is_null
FROM orders
WHERE note IS NULL;

Output

right_is_null
22

Meaning. Real count of missing notes.

Ravindra Bagale's Tip

Interview line: “I use IS NULL because NULL means unknown, not a value.”