NULL behavior in comparisons in SQL - Deep Dive

Overview - NULL behavior in comparisons

What is it?

In SQL, NULL represents a missing or unknown value. When you compare NULL with any other value using standard comparison operators, the result is not true or false but unknown. This means NULL behaves differently than regular values in comparisons.

Why it matters

Without understanding how NULL behaves, queries can return unexpected results, like missing rows or incorrect counts. This can cause confusion and errors in data analysis, reporting, and application logic that rely on accurate database queries.

Where it fits

Learners should first understand basic SQL data types and comparison operators. After mastering NULL behavior, they can learn about three-valued logic, IS NULL checks, and how to handle NULLs in joins and aggregates.

Mental Model

Core Idea

NULL means unknown, so any comparison with NULL results in an unknown truth value, not true or false.

Think of it like...

Imagine trying to compare two puzzle pieces when one piece is missing; you can't say they match or don't match because you simply don't know what the missing piece looks like.

Comparison Result Table:
┌───────────────┬───────────────┬───────────────┐
│ Value 1       │ Value 2       │ Comparison    │
├───────────────┼───────────────┼───────────────┤
│ 5             │ 5             │ TRUE          │
│ 5             │ 3             │ FALSE         │
│ 5             │ NULL          │ UNKNOWN (NULL)│
│ NULL          │ NULL          │ UNKNOWN (NULL)│
└───────────────┴───────────────┴───────────────┘

Build-Up - 7 Steps

1

Foundation

What is NULL in SQL

Concept: NULL represents missing or unknown data in a database.

In SQL, NULL is a special marker used when the value of a field is unknown or missing. It is not the same as zero or an empty string. NULL means 'no data' or 'unknown data'.

Result

NULL values appear in tables where data is incomplete or not provided.

Understanding that NULL is not a value but a marker for unknown data is key to grasping its special behavior.

2

Foundation

Basic SQL Comparisons

Concept: Comparison operators check if values are equal, greater, or less.

SQL uses operators like =, <, >, <=, >=, and <> to compare values. Normally, these return TRUE or FALSE depending on the values compared.

Result

Comparisons like 5 = 5 return TRUE; 5 = 3 returns FALSE.

Knowing how comparisons work with normal values sets the stage for understanding how NULL changes this behavior.

3

Intermediate

Comparing NULL with Values

🤔Before reading on: Do you think '5 = NULL' returns TRUE, FALSE, or UNKNOWN? Commit to your answer.

Concept: Any comparison with NULL results in UNKNOWN, not TRUE or FALSE.

When you compare a value to NULL using = or <>, the result is UNKNOWN because NULL means unknown data. For example, '5 = NULL' does not return TRUE or FALSE but UNKNOWN.

Result

Queries using 'WHERE column = NULL' return no rows because UNKNOWN is treated as FALSE in filtering.

Recognizing that NULL breaks normal true/false logic prevents common query mistakes.

4

Intermediate

Three-Valued Logic in SQL

🤔Before reading on: Does SQL treat NULL comparisons as TRUE/FALSE or something else? Commit to your answer.

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

In SQL, logical expressions can evaluate to TRUE, FALSE, or UNKNOWN. UNKNOWN arises when NULL is involved in comparisons. In WHERE clauses, only TRUE rows are returned; FALSE and UNKNOWN rows are excluded.

Result

Rows with NULL in compared columns are often excluded unless explicitly handled.

Understanding three-valued logic explains why NULL comparisons behave unexpectedly in filters.

5

Intermediate

Using IS NULL and IS NOT NULL

🤔Before reading on: Can you use '= NULL' to check for NULL values? Commit to your answer.

Concept: Special operators IS NULL and IS NOT NULL check for NULL values correctly.

Because '= NULL' does not work, SQL provides IS NULL and IS NOT NULL to test for NULL presence. For example, 'WHERE column IS NULL' returns rows where the column is NULL.

Result

Queries correctly find rows with missing data using IS NULL checks.

Knowing the right way to check for NULL avoids silent bugs in data filtering.

6

Advanced

NULL in JOIN and Aggregate Functions

🤔Before reading on: Do NULLs affect JOIN matches and aggregate results? Commit to your answer.

Concept: NULLs influence JOIN behavior and aggregate calculations in subtle ways.

In JOINs, NULLs do not match any value, including other NULLs, so rows with NULL keys may be excluded. Aggregate functions like COUNT(column) ignore NULLs, while COUNT(*) counts all rows regardless of NULLs.

Result

Understanding NULL effects helps write accurate JOINs and aggregates.

Recognizing NULL's impact on JOINs and aggregates prevents data loss or miscounts.

7

Expert

NULL Propagation and Indexing Effects

🤔Before reading on: Does NULL affect query optimization and index usage? Commit to your answer.

Concept: NULL values can affect how queries are optimized and how indexes are used internally.

Some database engines treat NULLs specially in indexes, sometimes excluding them or storing them differently. This can affect query plans and performance. Also, NULLs propagate through expressions, causing results to be NULL unless handled explicitly.

Result

Expert handling of NULLs improves query efficiency and correctness.

Knowing NULL's influence on internals helps optimize complex queries and avoid subtle bugs.

Under the Hood

Internally, SQL uses a three-valued logic system where NULL represents an unknown truth value. Comparisons involving NULL do not produce TRUE or FALSE but UNKNOWN. This UNKNOWN is treated as FALSE in WHERE filters, so rows with NULL comparisons are excluded unless IS NULL is used. Indexes may store NULLs differently or exclude them, affecting query plans.

Why designed this way?

SQL was designed to handle incomplete or missing data explicitly. Using three-valued logic allows databases to represent unknown information without forcing a true or false decision. This design avoids incorrect assumptions about missing data but requires special handling in queries.

┌───────────────┐      ┌───────────────┐      ┌───────────────┐
│ Value 1       │  =   │ Value 2       │  --> │ Result        │
├───────────────┤      ├───────────────┤      ├───────────────┤
│ Non-NULL      │      │ Non-NULL      │      │ TRUE or FALSE │
│ (e.g., 5)     │      │ (e.g., 3)     │      │               │
├───────────────┤      ├───────────────┤      ├───────────────┤
│ Any Value     │      │ NULL          │      │ UNKNOWN (NULL)│
├───────────────┤      ├───────────────┤      ├───────────────┤
│ NULL          │      │ NULL          │      │ UNKNOWN (NULL)│
└───────────────┘      └───────────────┘      └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does 'column = NULL' find rows where column is NULL? Commit yes or no.

Common Belief: Many think 'column = NULL' returns rows where column is NULL.

Reality: 'column = NULL' always returns UNKNOWN, so no rows are returned. You must use 'column IS NULL' instead.

Why it matters: Using '= NULL' silently filters out NULL rows, causing missing data in results.

Quick: Does NULL equal NULL in SQL? Commit yes or no.

Common Belief: Some believe NULL equals NULL because they both represent missing data.

Reality: NULL compared to NULL is UNKNOWN, not TRUE, so they are not equal in comparisons.

Why it matters: Assuming NULL equals NULL can cause incorrect JOINs or filters, missing matching rows.

Quick: Does COUNT(column) count NULL values? Commit yes or no.

Common Belief: People often think COUNT(column) counts all rows including NULLs.

Reality: COUNT(column) ignores NULLs and counts only non-NULL values; COUNT(*) counts all rows.

Why it matters: Misunderstanding this leads to wrong row counts and faulty reports.

Quick: Does NULL behave like an empty string or zero? Commit yes or no.

Common Belief: Some assume NULL is the same as empty string or zero.

Reality: NULL is different; it means unknown or missing, not a value like zero or empty string.

Why it matters: Treating NULL as a value causes logic errors and incorrect query results.

Expert Zone

1

NULLs can cause unexpected behavior in boolean expressions combined with AND/OR due to three-valued logic.

2

Some databases optimize queries by excluding NULLs from indexes, affecting performance and query plans.

3

Functions like COALESCE and NULLIF provide powerful ways to handle NULLs but require careful use to avoid masking data issues.

When NOT to use

Avoid relying on implicit NULL comparisons in filters; always use IS NULL or IS NOT NULL. For missing data, consider default values or explicit flags. In some cases, use NOT NULL constraints to prevent NULLs entirely.

Production Patterns

In production, developers use IS NULL checks explicitly, COALESCE to provide defaults, and carefully design schemas to minimize NULLs where possible. Query plans are analyzed to ensure NULLs do not degrade performance, especially in JOINs and indexes.

Connections

Three-Valued Logic

NULL behavior in SQL is a direct application of three-valued logic (TRUE, FALSE, UNKNOWN).

Understanding three-valued logic outside SQL helps grasp why NULL comparisons yield UNKNOWN instead of TRUE or FALSE.

Missing Data in Statistics

NULL in databases parallels missing data in statistics, where unknown values affect analysis.

Knowing how statisticians handle missing data informs better database NULL handling and query design.

Boolean Algebra

SQL's NULL logic extends classical Boolean algebra to include an unknown state.

Recognizing this extension clarifies why SQL logical expressions behave differently with NULLs.

Common Pitfalls

#1 Using '= NULL' to check for NULL values.

Wrong approach:

SELECT * FROM users WHERE email = NULL;

Correct approach:

SELECT * FROM users WHERE email IS NULL;

Root cause:

Misunderstanding that NULL is not a value and cannot be compared with '='.

#2 Assuming NULL equals NULL in JOIN conditions.

Wrong approach:

SELECT * FROM table1 JOIN table2 ON table1.col = table2.col WHERE table1.col IS NULL;

Correct approach:

Use 'IS NULL' checks separately or use FULL OUTER JOIN with IS NULL conditions to handle NULL matches.

Root cause:

Believing NULLs match each other in equality comparisons.

#3 Counting NULLs with COUNT(column).

Wrong approach:

SELECT COUNT(column) FROM table;

Correct approach:

SELECT COUNT(*) FROM table; -- counts all rows including NULLs

Root cause:

Not knowing COUNT(column) excludes NULLs.

Key Takeaways

NULL in SQL means unknown or missing data, not a value like zero or empty string.

Comparisons with NULL do not return TRUE or FALSE but UNKNOWN, which behaves differently in queries.

Use IS NULL and IS NOT NULL to check for NULL values correctly; '=' and '<>' do not work with NULL.

SQL uses three-valued logic (TRUE, FALSE, UNKNOWN) to handle NULLs, affecting filtering and joins.

Understanding NULL behavior is essential to avoid subtle bugs and ensure accurate query results.