Course contentsShow
← Data Science
Lesson 871 of 2,14522. SQL FoundationsFree lesson

NULL Handling with Logical Operators

How NULL values interact with AND, OR, and NOT, introducing three-valued logic in SQL comparisons.

NULL Handling with Logical Operators

What you'll learn: How NULL values behave when combined with AND, OR, and NOT operators—introducing SQL's three-valued logic system.

The Core Challenge

In most programming, logic is binary: TRUE or FALSE. But SQL introduces a third state: UNKNOWN (represented by NULL). When you use logical operators with NULL values, you're working with three-valued logic, which can produce surprising results if you're not prepared.

Think of NULL as "I don't know." If someone asks, "Is it raining AND is it cold?" and you don't know if it's raining, you can't definitively answer TRUE or FALSE—your answer is "I don't know" (UNKNOWN).

How Each Operator Handles NULL

AND Operator:

  • TRUE AND NULL → UNKNOWN (might be true, might not)
  • FALSE AND NULL → FALSE (already false regardless)
  • NULL AND NULL → UNKNOWN

OR Operator:

  • TRUE OR NULL → TRUE (already true regardless)
  • FALSE OR NULL → UNKNOWN (depends on the unknown value)
  • NULL OR NULL → UNKNOWN

NOT Operator:

  • NOT NULL → UNKNOWN (negating "unknown" is still unknown)

Why This Matters

A WHERE clause only returns rows where the condition evaluates to TRUE. UNKNOWN results are treated like FALSE—they're filtered out. This means:

WHERE age > 30 AND city = 'Boston'

If city is NULL for a row, that entire condition becomes UNKNOWN, and the row won't appear—even if age > 30 is TRUE.

Key Takeaway: NULL values introduce three-valued logic in SQL. Conditions involving NULL typically evaluate to UNKNOWN (not TRUE or FALSE), causing those rows to be excluded from WHERE clause results. Always use IS NULL or IS NOT NULL to explicitly handle NULL values in your logic.