NULL in AND, OR, NOT logic in SQL - Deep Dive

Overview - NULL in AND, OR, NOT logic

What is it?

In SQL, NULL represents an unknown or missing value. When you use logical operators like AND, OR, and NOT with NULL, the result can be unknown too. This means the outcome is not simply true or false, but sometimes 'unknown', which affects how conditions are evaluated in queries.

Why it matters

Understanding how NULL behaves with logical operators is crucial because it changes how filters and conditions work in databases. Without this knowledge, you might get unexpected results, like missing rows or wrong counts, which can cause errors in reports or applications.

Where it fits

Before learning this, you should know basic SQL queries and boolean logic (true/false conditions). After this, you can learn about three-valued logic in SQL, NULL handling functions, and how to write safe queries that handle missing data correctly.

Mental Model

Core Idea

In SQL, NULL means unknown, so logical operations with NULL produce unknown results that affect query filtering.

Think of it like...

Imagine you are guessing if a box contains a red ball. If you know it does, answer is true; if you know it doesn't, answer is false; but if you haven't looked inside, your answer is 'I don't know' — that's like NULL in logic.

┌───────────────┐
│   Value A     │
│  TRUE/FALSE/  │
│    UNKNOWN    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Logical Op:   │
│ AND / OR /    │
│ NOT           │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Result: TRUE, │
│ FALSE, or     │
│ UNKNOWN(NULL) │
└───────────────┘

Build-Up - 8 Steps

1

Foundation

What is NULL in SQL?

Concept: Introduce NULL as a special marker for missing or unknown data in SQL.

NULL is not a value like 0 or empty string. It means we don't know the value. For example, if a person's birthdate is unknown, the database stores NULL in that field.

Result

NULL represents unknown or missing information in a database.

Understanding NULL as unknown rather than a value is key to grasping how SQL treats it differently in logic.

2

Foundation

Basic Boolean Logic in SQL

Concept: Explain how SQL uses TRUE and FALSE in conditions and filters.

SQL conditions usually return TRUE or FALSE. For example, 'age > 18' is TRUE if age is over 18, FALSE otherwise. These results decide which rows appear in query results.

Result

Conditions filter rows based on TRUE or FALSE results.

Knowing how TRUE and FALSE control query results sets the stage for understanding how NULL changes this behavior.

3

Intermediate

Three-Valued Logic: TRUE, FALSE, UNKNOWN

🤔Before reading on: do you think SQL conditions with NULL return TRUE or FALSE? Commit to your answer.

Concept: Introduce the idea that SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN (NULL).

When a condition involves NULL, the result is often UNKNOWN. For example, 'age > NULL' is UNKNOWN because we don't know the age. This UNKNOWN affects how AND, OR, and NOT work.

Result

Conditions with NULL produce UNKNOWN, not just TRUE or FALSE.

Recognizing UNKNOWN as a third logical state explains why some queries behave unexpectedly with NULL.

4

Intermediate

AND Operator with NULL Values

🤔Before reading on: if one side of AND is FALSE and the other is NULL, do you think the result is TRUE, FALSE, or UNKNOWN?

Concept: Show how AND combines TRUE, FALSE, and UNKNOWN values.

AND returns TRUE only if both sides are TRUE. If one side is FALSE, result is FALSE. If one side is UNKNOWN and the other TRUE, result is UNKNOWN. Example: TRUE AND NULL = UNKNOWN; FALSE AND NULL = FALSE.

Result

AND with NULL can return FALSE or UNKNOWN depending on the other operand.

Understanding AND's behavior with NULL helps predict which rows pass complex filters.

5

Intermediate

OR Operator with NULL Values

🤔Before reading on: if one side of OR is TRUE and the other is NULL, what is the result? TRUE, FALSE, or UNKNOWN?

Concept: Explain how OR works with TRUE, FALSE, and UNKNOWN.

OR returns TRUE if any side is TRUE. If one side is UNKNOWN and the other FALSE, result is UNKNOWN. Example: TRUE OR NULL = TRUE; FALSE OR NULL = UNKNOWN.

Result

OR with NULL can return TRUE or UNKNOWN depending on the other operand.

Knowing OR's logic with NULL prevents mistakes in query conditions that combine unknown data.

6

Intermediate

NOT Operator and NULL Values

🤔Before reading on: what is NOT NULL? TRUE, FALSE, or UNKNOWN?

Concept: Describe how NOT flips TRUE and FALSE but leaves UNKNOWN as UNKNOWN.

NOT TRUE = FALSE, NOT FALSE = TRUE, but NOT UNKNOWN = UNKNOWN. So, NOT NULL remains UNKNOWN because we don't know the original value.

Result

NOT operator preserves UNKNOWN when applied to NULL.

Understanding NOT's behavior with NULL is essential for writing correct negations in queries.

7

Advanced

Filtering Rows with NULL in WHERE Clause

🤔Before reading on: does a WHERE condition that evaluates to UNKNOWN include the row in the result? Yes or No?

Concept: Explain how UNKNOWN affects row filtering in queries.

In SQL, WHERE filters include rows only if the condition is TRUE. If condition is FALSE or UNKNOWN, the row is excluded. So, NULL in conditions often causes rows to be skipped.

Result

Rows with conditions evaluating to UNKNOWN are excluded from query results.

Knowing that UNKNOWN excludes rows clarifies why NULLs can cause missing data in query outputs.

8

Expert

Using IS NULL and COALESCE to Handle NULL Logic

🤔Before reading on: can IS NULL be used inside logical expressions to avoid UNKNOWN results? Yes or No?

Concept: Show how special functions help manage NULL in logic to avoid surprises.

IS NULL checks if a value is NULL and returns TRUE or FALSE, avoiding UNKNOWN. COALESCE returns the first non-NULL value, letting you replace NULLs. Using these helps write conditions that behave predictably.

Result

Using IS NULL and COALESCE controls NULL effects in logical expressions.

Mastering these functions empowers you to handle NULLs safely and avoid logic pitfalls in production queries.

Under the Hood

SQL uses three-valued logic internally, where each logical operation considers TRUE, FALSE, and UNKNOWN states. When NULL appears, it represents UNKNOWN, and logical operators follow truth tables extended for this third state. The database engine evaluates conditions row by row, applying these rules to decide if a row matches a filter.

Why designed this way?

SQL was designed to handle incomplete or missing data gracefully. Introducing UNKNOWN as a third logical state allows queries to reflect uncertainty rather than forcing a true/false decision. This design prevents incorrect assumptions about missing data and supports more accurate data analysis.

┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│   TRUE      │       │   FALSE     │       │   UNKNOWN   │
└─────┬───────┘       └─────┬───────┘       └─────┬───────┘
      │                     │                     │
      │                     │                     │
      ▼                     ▼                     ▼
┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│ AND / OR /  │──────▶│ Result:     │◀──────│ Logical     │
│ NOT Logic   │       │ TRUE/FALSE/ │       │ Operator    │
└─────────────┘       │ UNKNOWN     │       └─────────────┘
                      └─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does 'NULL = NULL' evaluate to TRUE or FALSE? Commit to your answer.

Common Belief: Many think NULL equals NULL because they are both unknowns.

Reality: NULL = NULL evaluates to UNKNOWN, not TRUE, because NULL means unknown and two unknowns can't be confirmed equal.

Why it matters: Assuming NULL = NULL is TRUE can cause wrong query results, like missing rows when checking for duplicates.

Quick: If a WHERE clause condition is UNKNOWN, does the row get included? Yes or No?

Common Belief: Some believe UNKNOWN behaves like TRUE and includes the row.

Reality: Rows with conditions evaluating to UNKNOWN are excluded from results, just like FALSE.

Why it matters: Misunderstanding this leads to missing data in query outputs, causing confusion and errors.

Quick: Does NOT NULL return TRUE or UNKNOWN? Commit your guess.

Common Belief: People often think NOT NULL is TRUE because NULL means false or empty.

Reality: NOT NULL returns UNKNOWN because negating unknown remains unknown.

Why it matters: Incorrectly assuming NOT NULL is TRUE can cause logic errors in filters and conditions.

Quick: Does 'NULL OR TRUE' evaluate to TRUE or UNKNOWN? Commit your answer.

Common Belief: Some think any OR with NULL is NULL (unknown).

Reality: 'NULL OR TRUE' evaluates to TRUE because OR returns TRUE if any operand is TRUE.

Why it matters: Misunderstanding OR with NULL can cause wrong query logic and unexpected results.

Expert Zone

1

SQL's three-valued logic means that NULL can propagate through complex expressions, making it essential to carefully order conditions to avoid unintended UNKNOWN results.

2

Using IS NULL and IS NOT NULL explicitly in conditions is often more reliable than comparing to NULL with '=' or '<>', which always yield UNKNOWN.

3

Some database systems optimize NULL handling differently, so understanding the standard behavior helps write portable and predictable SQL.

When NOT to use

Avoid relying on implicit NULL logic in critical filters; instead, use explicit NULL checks or COALESCE to provide defaults. For strict true/false logic, consider data cleaning to remove NULLs or use application logic to handle unknowns.

Production Patterns

In production, queries often use IS NULL and COALESCE to handle missing data explicitly. Complex filters combine NULL-safe conditions to ensure no rows are unintentionally excluded. Indexes and query plans also consider NULLs, so understanding their logic helps optimize performance.

Connections

Three-Valued Logic (3VL) in Computer Science

SQL's NULL logic is a practical application of 3VL, which adds an 'unknown' state to classical true/false logic.

Knowing 3VL theory explains why SQL uses UNKNOWN and helps understand other systems that handle uncertainty.

Error Handling in Programming

Both NULL logic and error handling deal with unknown or exceptional states that affect flow control.

Understanding how SQL treats NULL like an unknown state parallels how programs handle exceptions or missing data, improving debugging skills.

Philosophy of Knowledge (Epistemology)

NULL represents unknown knowledge, linking SQL logic to philosophical ideas about what can be known or unknown.

This connection deepens appreciation of how databases model real-world uncertainty and incomplete information.

Common Pitfalls

#1 Using '=' to compare values to NULL.

Wrong approach:

SELECT * FROM users WHERE birthdate = NULL;

Correct approach:

SELECT * FROM users WHERE birthdate IS NULL;

Root cause:

Misunderstanding that NULL is not a value and '=' comparison with NULL always yields UNKNOWN.

#2 Assuming NOT NULL returns TRUE.

Wrong approach:

SELECT * FROM orders WHERE NOT discount = NULL;

Correct approach:

SELECT * FROM orders WHERE discount IS NOT NULL;

Root cause:

Confusing logical negation with NULL checks; NOT NULL does not mean TRUE but UNKNOWN.

#3 Ignoring NULL effects in complex AND/OR conditions.

Wrong approach:

SELECT * FROM products WHERE price > 100 AND discount = NULL;

Correct approach:

SELECT * FROM products WHERE price > 100 AND discount IS NULL;

Root cause:

Not realizing NULL in conditions causes UNKNOWN, which excludes rows unexpectedly.

Key Takeaways

NULL in SQL means unknown, not a value, and affects logical operations by introducing a third state: UNKNOWN.

Logical operators AND, OR, and NOT follow three-valued logic, producing TRUE, FALSE, or UNKNOWN depending on NULL presence.

Conditions evaluating to UNKNOWN exclude rows from query results, which can cause missing data if not handled properly.

Use IS NULL, IS NOT NULL, and COALESCE functions to explicitly manage NULLs and avoid unexpected UNKNOWN results.

Understanding NULL logic is essential for writing correct, reliable SQL queries and interpreting database behavior accurately.