Why equals NULL fails in SQL - Why It Works This Way

Overview - Why equals NULL fails

What is it?

In SQL, NULL represents a missing or unknown value. When you try to compare a value to NULL using the equals sign (=), it does not work as expected because NULL is not a regular value. Instead, SQL uses special logic to handle NULLs, which requires different operators to check for NULL values.

Why it matters

Without understanding why equals NULL fails, queries can return no results or incorrect results, leading to confusion and bugs. This concept is crucial because NULLs are common in databases, representing unknown or missing data. If you treat NULL like a normal value, your data filtering and logic will break, causing real-world problems in reports, applications, and decisions.

Where it fits

Before learning this, you should understand basic SQL queries and how data is stored in tables. After this, you can learn about three-valued logic in SQL, NULL-safe functions, and how to handle NULLs in joins and aggregates.

Mental Model

Core Idea

NULL is not a value but a marker for unknown, so comparing anything to NULL with equals (=) always results in unknown, not true or false.

Think of it like...

Imagine you have a box with a mystery item inside. Asking 'Is the item equal to a ball?' is impossible to answer because you don't know what's inside. The answer is 'I don't know,' not yes or no.

┌───────────────┐
│   Value A     │
├───────────────┤
│   NULL (???)  │
└───────────────┘

Comparison with '=' operator:
Value A = NULL → UNKNOWN (not TRUE or FALSE)

SQL treats this as:
TRUE  → passes filter
FALSE → rejects filter
UNKNOWN → also rejects filter

So, 'WHERE column = NULL' filters out all rows.

Build-Up - 7 Steps

1

Foundation

Understanding NULL in SQL

Concept: NULL means missing or unknown data, not zero or empty.

In SQL, NULL is a special marker used when the value is unknown or missing. It is different from zero (0), empty string (''), or any other value. NULL means 'I don't know what this is.'

Result

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

Understanding that NULL is not a value but a placeholder for unknown data is the foundation for handling it correctly.

2

Foundation

Basic SQL Comparison Operators

Concept: Equals (=) compares two known values and returns true or false.

The equals operator (=) checks if two values are the same. For example, 5 = 5 returns true, and 5 = 3 returns false. This works only when both sides have known values.

Result

Queries using '=' filter rows where the condition is true.

Knowing that '=' expects known values helps see why it fails with unknowns like NULL.

3

Intermediate

Why 'equals NULL' Always Fails

🤔Before reading on: do you think 'column = NULL' returns rows where column is NULL or no rows? Commit to your answer.

Concept: Comparing anything to NULL with '=' results in UNKNOWN, which SQL treats as false in filters.

When you write 'WHERE column = NULL', SQL does not find rows where column is NULL. This is because NULL means unknown, and comparing unknown to anything with '=' is unknown, not true. SQL filters only keep rows where conditions are true, so no rows match.

Result

No rows are returned when using 'column = NULL' even if NULLs exist.

Understanding that NULL breaks normal comparison logic prevents confusion and incorrect queries.

4

Intermediate

Using IS NULL and IS NOT NULL

🤔Before reading on: do you think 'IS NULL' is a comparison operator or a special keyword? Commit to your answer.

Concept: SQL provides special operators IS NULL and IS NOT NULL to check for NULL values.

To check if a column is NULL, use 'WHERE column IS NULL'. This returns rows where the value is unknown. Similarly, 'WHERE column IS NOT NULL' returns rows with known values.

Result

Queries correctly filter rows with or without NULL values.

Knowing the special NULL operators is essential to handle missing data properly.

5

Intermediate

Three-Valued Logic in SQL

🤔Before reading on: do you think SQL conditions return only true or false, or is there a third option? Commit to your answer.

Concept: SQL uses three-valued logic: true, false, and unknown (for NULL comparisons).

When comparing values, SQL can return true, false, or unknown. Unknown happens when NULL is involved in comparisons. Filters only keep rows where condition is true, so unknown acts like false in filtering.

Result

Understanding this explains why NULL comparisons behave differently.

Recognizing three-valued logic clarifies many confusing SQL behaviors with NULL.

6

Advanced

NULLs in Joins and Aggregates

🤔Before reading on: do you think NULLs match each other in JOIN conditions using '='? Commit to your answer.

Concept: NULLs do not match each other in JOINs using '=', affecting query results and aggregates.

When joining tables on columns that may contain NULLs, rows with NULL do not match because NULL = NULL is unknown, not true. Aggregation functions like COUNT ignore NULLs unless handled explicitly.

Result

Joins and aggregates behave differently with NULLs, requiring special handling.

Knowing NULL behavior in joins and aggregates helps avoid subtle bugs in complex queries.

7

Expert

NULL Semantics and SQL Standards

🤔Before reading on: do you think all databases handle NULLs exactly the same way? Commit to your answer.

Concept: SQL NULL behavior follows standards but has subtle differences across database systems.

The SQL standard defines NULL and three-valued logic, but implementations vary in details like NULL sorting, index usage, and functions. Some databases offer NULL-safe equality operators or extensions to handle NULLs differently.

Result

Expert users must know their database's NULL behavior for optimal queries.

Understanding the standard and variations prevents portability issues and surprises in production.

Under the Hood

Internally, SQL treats NULL as a special marker, not a value. Comparisons involving NULL do not produce true or false but an unknown state. The SQL engine uses three-valued logic to evaluate conditions: true, false, and unknown. Filters only pass rows where conditions are true, so unknown acts like false in filtering. Special operators IS NULL and IS NOT NULL directly check the NULL marker without comparison.

Why designed this way?

SQL was designed to handle incomplete or missing data gracefully. Using NULL as a marker and three-valued logic allows queries to distinguish between known false and unknown states. This design avoids incorrect assumptions about missing data. Alternatives like treating NULL as a value would cause wrong results and hide data quality issues.

┌───────────────┐       ┌───────────────┐
│   Value A     │       │   Value B     │
├───────────────┤       ├───────────────┤
│   Known       │       │   NULL (???)  │
└──────┬────────┘       └──────┬────────┘
       │ Equals (=) comparison    │
       ▼                         ▼
┌───────────────────────────────┐
│ Result: UNKNOWN (not TRUE/FALSE)│
└──────────────┬────────────────┘
               │
               ▼
       ┌───────────────┐
       │ Filter rows   │
       │ Keep if TRUE  │
       │ Discard if    │
       │ FALSE or UNKNOWN│
       └───────────────┘

Myth Busters - 4 Common Misconceptions

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

Common Belief: People often believe 'column = NULL' finds rows with NULL values.

Reality: 'column = NULL' always returns no rows because NULL comparisons yield unknown, not true.

Why it matters: Using '=' with NULL causes queries to miss NULL rows, leading to incomplete or wrong results.

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

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

Reality: NULL does not equal NULL; the comparison returns unknown, not true.

Why it matters: Assuming NULL equals NULL breaks joins and filters, causing missing matches.

Quick: Is IS NULL just a synonym for '= NULL'? Commit yes or no.

Common Belief: Some believe IS NULL is the same as '= NULL'.

Reality: IS NULL is a special operator that correctly checks for NULL, unlike '= NULL'.

Why it matters: Using '= NULL' instead of IS NULL causes queries to fail silently.

Quick: Do all databases handle NULL comparisons identically? Commit yes or no.

Common Belief: People assume all SQL databases treat NULL the same way.

Reality: While based on standards, implementations differ in NULL handling details.

Why it matters: Ignoring differences can cause bugs when moving queries between systems.

Expert Zone

1

NULLs can affect index usage and query optimization differently across databases, impacting performance.

2

Some databases provide NULL-safe equality operators or functions to compare NULLs as equal when needed.

3

Aggregates like COUNT(*) count rows including NULLs, but COUNT(column) excludes NULLs, which can confuse results.

When NOT to use

Avoid using '=' to check for NULLs; instead, use IS NULL or IS NOT NULL. For complex NULL handling, consider COALESCE or NULL-safe functions. When NULL semantics cause confusion, redesign schema to use explicit default values or flags.

Production Patterns

In production, developers use IS NULL checks for filtering, COALESCE to provide defaults, and careful join conditions to handle NULLs. They also write tests to verify NULL behavior and use database-specific NULL-safe operators for performance and correctness.

Connections

Three-valued logic

Builds-on

Understanding SQL's NULL behavior requires grasping three-valued logic, where conditions can be true, false, or unknown.

Data quality management

Related concept

NULLs represent missing data, so handling NULLs correctly is essential for maintaining and improving data quality.

Philosophy of knowledge (Epistemology)

Analogous concept

NULL as unknown parallels philosophical ideas about knowledge and uncertainty, helping appreciate why NULL comparisons yield unknown rather than true or false.

Common Pitfalls

#1 Using '=' to check for NULL values returns no rows.

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 '=' does not work with NULL.

#2 Assuming NULL equals NULL in join conditions causes missing matches.

Wrong approach:

SELECT * FROM a JOIN b ON a.col = b.col WHERE a.col = NULL;

Correct approach:

SELECT * FROM a JOIN b ON a.col = b.col WHERE a.col IS NULL;

Root cause:

Believing NULL behaves like normal values in equality comparisons.

#3 Using IS NULL as '= NULL' in conditional expressions.

Wrong approach:

IF (column = NULL) THEN ...

Correct approach:

IF (column IS NULL) THEN ...

Root cause:

Confusing special NULL operators with standard comparison operators.

Key Takeaways

NULL in SQL means unknown or missing data, not a regular value.

Comparing anything to NULL with '=' always results in unknown, which SQL treats as false in filters.

To check for NULL values, use the special operators IS NULL and IS NOT NULL.

SQL uses three-valued logic (true, false, unknown) to handle NULLs, which affects query results.

Understanding NULL behavior is essential to write correct queries, especially in joins, filters, and aggregates.