Why NULL is not a value in SQL - Why It Works This Way

Overview - Why NULL is not a value

What is it?

NULL in databases means 'unknown' or 'missing' information, not a regular value like a number or text. It shows that we don't know what the data is, rather than it being zero or empty. NULL is a special marker used to represent this unknown state. It helps databases handle incomplete or missing data safely.

Why it matters

Without NULL, databases would have to guess or use fake values for missing data, which can cause wrong answers and confusion. For example, treating missing ages as zero would make average age calculations incorrect. NULL lets us clearly separate 'no data' from real data, making queries and reports more accurate and trustworthy.

Where it fits

Before learning about NULL, you should understand basic data types and how databases store data. After this, you can learn about how SQL handles NULL in comparisons, functions, and joins, and how to write queries that correctly manage missing information.

Mental Model

Core Idea

NULL is not a value but a marker that means 'we don't know what this is yet'.

Think of it like...

Imagine a form where someone leaves a question blank because they don't know the answer. That blank spot is not zero or empty; it means 'unknown'. NULL in databases works the same way.

┌───────────────┐
│   Database    │
│   Table Row   │
│ ┌───────────┐ │
│ │ Column A  │ │
│ │ Value: 10 │ │
│ └───────────┘ │
│ ┌───────────┐ │
│ │ Column B  │ │
│ │ Value: NULL│ │
│ └───────────┘ │
└───────────────┘

NULL means 'unknown', not zero or empty.

Build-Up - 6 Steps

1

Foundation

Understanding NULL as Unknown

Concept: NULL represents unknown or missing data, not a real value.

In a database, NULL means we do not know the value for a field. It is different from zero, empty string, or any other value. For example, if a person's birthdate is unknown, the database stores NULL instead of guessing or leaving it blank.

Result

NULL fields indicate missing or unknown data clearly.

Understanding NULL as 'unknown' prevents confusing it with actual data values like zero or empty.

2

Foundation

NULL is Not Equal to Any Value

Concept: NULL does not behave like normal values in comparisons.

When you compare NULL to anything, even NULL itself, the result is not true or false but unknown. For example, 'NULL = NULL' does not return true because both are unknown, so we can't say they are equal.

Result

Comparisons with NULL return unknown, affecting query results.

Knowing NULL comparisons return unknown helps avoid wrong assumptions in queries.

3

Intermediate

How NULL Affects SQL Conditions

🤔Before reading on: Do you think 'WHERE column = NULL' finds rows with NULL? Commit to your answer.

Concept: SQL treats NULL specially in WHERE clauses and conditions.

Using 'WHERE column = NULL' does not find rows with NULL because NULL is not a value. Instead, SQL uses 'IS NULL' to check for unknown values. This distinction is important to get correct query results.

Result

'WHERE column IS NULL' correctly finds rows with unknown data.

Understanding the special syntax for NULL checks prevents missing or wrong data in queries.

4

Intermediate

NULL in Aggregate Functions

🤔Before reading on: Does NULL count as zero in averages and sums? Commit to your answer.

Concept: Aggregate functions ignore NULL values instead of treating them as zero.

Functions like SUM, AVG, COUNT behave differently with NULL. SUM and AVG skip NULLs, so they don't affect totals or averages. COUNT(column) counts only non-NULL values, while COUNT(*) counts all rows.

Result

Aggregates give accurate results by ignoring unknown data.

Knowing how NULL affects aggregates helps write correct reports and summaries.

5

Advanced

Three-Valued Logic with NULL

🤔Before reading on: Is SQL logic with NULL just true or false? Commit to your answer.

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

Because NULL means unknown, SQL conditions can be true, false, or unknown. Unknown results cause rows to be excluded in WHERE clauses unless explicitly handled. This logic is called three-valued logic and is key to understanding NULL behavior.

Result

Queries behave differently because of the unknown truth value.

Understanding three-valued logic explains why NULL can cause unexpected query filtering.

6

Expert

NULL Storage and Indexing Internals

🤔Before reading on: Do you think NULL takes the same storage space as normal values? Commit to your answer.

Concept: Databases store NULL efficiently and handle indexing specially for NULLs.

Internally, NULL is stored as a special marker, often using a bitmap to track which columns are NULL. Indexes may treat NULLs differently, sometimes excluding them or storing them separately. This affects performance and query plans.

Result

NULL storage and indexing impact database efficiency and query speed.

Knowing NULL internals helps optimize database design and troubleshoot performance.

Under the Hood

NULL is stored as a special flag or marker in the database storage engine, not as a normal data value. When the database engine evaluates expressions, it uses three-valued logic (true, false, unknown) where NULL causes unknown results. This affects how comparisons, joins, and filters work internally. Indexes may store NULL presence separately to optimize lookups.

Why designed this way?

NULL was designed to represent missing or unknown data clearly, avoiding confusion with real values like zero or empty strings. Early database systems needed a way to handle incomplete data without corrupting results. Three-valued logic was introduced to handle the uncertainty NULL brings, balancing correctness and usability.

┌───────────────┐
│   Query Input │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Expression    │
│ Evaluation    │
│ (3-valued)    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Result: True, │
│ False, Unknown│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Filter Rows   │
│ (Exclude if   │
│ Unknown)      │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does 'NULL = NULL' return true or false? Commit to your answer.

Common Belief: NULL equals NULL, so comparing two NULLs returns true.

Reality: NULL compared to NULL returns unknown, not true, because both are unknown values.

Why it matters: Assuming NULL equals NULL can cause wrong query results and logic errors.

Quick: Does 'WHERE column = NULL' find rows with NULL? Commit to your answer.

Common Belief: Using '=' with NULL works like with other values to find NULL rows.

Reality: You must use 'IS NULL' to find NULLs; '=' does not work with NULL.

Why it matters: Using '=' with NULL causes queries to miss rows with unknown data.

Quick: Does COUNT(column) count NULL values? Commit to your answer.

Common Belief: COUNT counts all rows, including those with NULL in the column.

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

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

Quick: Is NULL the same as an empty string or zero? Commit to your answer.

Common Belief: NULL is just another value like empty string or zero.

Reality: NULL means unknown or missing, not a value at all.

Why it matters: Treating NULL as a value causes incorrect calculations and logic.

Expert Zone

1

NULL propagation in expressions means any operation with NULL often results in NULL, which can silently hide data issues.

2

Some databases allow special functions or settings to treat NULLs differently, like treating NULL as zero in calculations, but this can cause subtle bugs.

3

Indexing strategies for NULLs vary; some indexes exclude NULLs, affecting query plans and performance in unexpected ways.

When NOT to use

Avoid relying on NULL for mandatory fields where data must always exist; use constraints instead. For boolean logic, consider using explicit tri-state enums or flags to represent unknown states. In some cases, use default values or sentinel values instead of NULL to simplify logic.

Production Patterns

In real systems, NULL is used to represent optional data, missing user input, or incomplete records. Developers write queries with careful NULL checks using IS NULL and COALESCE functions. Data validation layers often handle NULLs explicitly to avoid surprises. Indexes and query plans are tuned considering NULL distribution.

Connections

Three-Valued Logic

NULL behavior in SQL is a direct application of three-valued logic (true, false, unknown).

Understanding three-valued logic clarifies why NULL comparisons don't behave like normal true/false logic.

Optional Types in Programming

NULL in databases is similar to optional or nullable types in programming languages that represent missing values.

Knowing how programming languages handle optional values helps understand NULL's role and handling in databases.

Incomplete Information Theory

NULL represents incomplete information, a concept studied in information theory and decision making.

Recognizing NULL as incomplete information connects database design to broader theories about uncertainty and knowledge.

Common Pitfalls

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

Wrong approach:

SELECT * FROM users WHERE age = NULL;

Correct approach:

SELECT * FROM users WHERE age IS NULL;

Root cause:

Misunderstanding that NULL is not a value and requires special syntax for comparison.

#2 Assuming NULL equals NULL in conditions.

Wrong approach:

SELECT * FROM orders WHERE discount = NULL OR discount = NULL;

Correct approach:

SELECT * FROM orders WHERE discount IS NULL OR discount IS NULL;

Root cause:

Confusing NULL as a value rather than an unknown marker.

#3 Counting NULLs with COUNT(column).

Wrong approach:

SELECT COUNT(age) FROM people;

Correct approach:

SELECT COUNT(*) FROM people;

Root cause:

Not knowing COUNT(column) excludes NULLs, leading to undercounting.

Key Takeaways

NULL is a special marker meaning 'unknown' or 'missing', not a real data value.

Comparisons with NULL do not return true or false but unknown, requiring special handling in SQL.

To check for NULL, use 'IS NULL' or 'IS NOT NULL' instead of '=' or '<>'.

Aggregate functions ignore NULLs to avoid skewing results, so COUNT(column) counts only known values.

Understanding NULL's three-valued logic and storage helps write correct queries and optimize database performance.

Practice

1. What does NULL represent in a database?

easy

A. Missing or unknown data

B. Zero value

C. Empty string

D. Boolean false

Solution

1. Step 1: Understand the meaning of NULL

   NULL means that the data is missing or unknown, not a specific value like zero or empty string.

2. Step 2: Differentiate NULL from other values

   Zero, empty string, and false are actual values, but NULL means no value is present.

3. Final Answer:

   Missing or unknown data -> Option A

4. Quick Check:

   NULL means missing data = C [OK]

Hint: Remember: NULL means unknown, not zero or empty [OK]

Common Mistakes:

• Thinking NULL is zero
• Confusing NULL with empty string
• Assuming NULL is false

2. Which SQL syntax correctly checks if a column age is NULL?

easy

A. WHERE age = NULL

B. WHERE age == NULL

C. WHERE age IS NULL

D. WHERE age != NULL

Solution

1. Step 1: Recall correct NULL comparison syntax

   In SQL, you cannot use = or != to check for NULL because NULL is not a value.

2. Step 2: Use IS NULL to check for NULL

   The correct way is to use IS NULL to find rows where the column is NULL.

3. Final Answer:

   WHERE age IS NULL -> Option C

4. Quick Check:

   Use IS NULL, not = NULL [OK]

Hint: Use IS NULL, never = NULL to check for NULL [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Using == NULL which is invalid in SQL
• Using != NULL which does not work

3. Given a table users with column email, what will this query return?

SELECT * FROM users WHERE email = NULL;

medium

A. All rows where email is NULL

B. No rows

C. Syntax error

D. All rows

Solution

1. Step 1: Understand comparison with NULL using =

   Using = NULL does not work because NULL is not a value and comparisons with NULL return unknown.

2. Step 2: Result of WHERE email = NULL

   The condition email = NULL evaluates to unknown for all rows, so no rows match and none are returned.

3. Final Answer:

   No rows -> Option B

4. Quick Check:

   email = NULL returns no rows [OK]

Hint: = NULL never matches; use IS NULL instead [OK]

Common Mistakes:

• Expecting rows with NULL to be returned
• Thinking = NULL works like IS NULL
• Confusing NULL with empty string

4. You want to find rows where salary is NULL, but this query returns no rows:

SELECT * FROM employees WHERE salary = NULL;

What is the error and how to fix it?

medium

A. Use IS NULL instead of = NULL

B. Use salary = 0 instead of NULL

C. Use salary != NULL instead

D. Add quotes around NULL

Solution

1. Step 1: Identify incorrect NULL comparison

   The query uses = NULL which does not work because NULL is not a value and cannot be compared with =.

2. Step 2: Correct syntax to find NULL values

   Use IS NULL to check for NULL values in SQL.

3. Final Answer:

   Use IS NULL instead of = NULL -> Option A

4. Quick Check:

   IS NULL finds NULLs, = NULL does not [OK]

Hint: Replace = NULL with IS NULL to fix query [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Trying != NULL which also fails
• Using 0 or empty string instead of NULL

5. You want to count how many rows have a missing phone_number in table contacts. Which query correctly counts NULLs?

hard

A. SELECT COUNT(phone_number) FROM contacts WHERE phone_number IS NULL;

B. SELECT COUNT(phone_number) FROM contacts;

C. SELECT COUNT(*) FROM contacts WHERE phone_number = NULL;

D. SELECT COUNT(*) FROM contacts WHERE phone_number IS NULL;

Solution

1. Step 1: Understand counting NULLs

   COUNT(column) counts only non-NULL values, so it ignores NULLs.

2. Step 2: Use WHERE phone_number IS NULL to filter NULL rows

   To count rows with NULL phone_number, filter with IS NULL and count all rows with COUNT(*)

3. Final Answer:

   SELECT COUNT(*) FROM contacts WHERE phone_number IS NULL; -> Option D

4. Quick Check:

   Use IS NULL filter and COUNT(*) to count NULL rows [OK]

Hint: Filter with IS NULL and use COUNT(*) to count NULLs [OK]

Common Mistakes:

• Using = NULL in WHERE clause
• Using COUNT(column) which skips NULLs
• Filtering with COUNT(phone_number) which is invalid