Why NULL is not a value in SQL - Visual Breakdown

Concept Flow - Why NULL is not a value

Start with NULL

↓

Check if NULL is a value

No↓

NULL means unknown or missing

↓

Operations with NULL result in NULL or UNKNOWN

↓

NULL is not equal to anything, even NULL

↓

Use IS NULL or IS NOT NULL to check NULL

This flow shows that NULL is not a value but a marker for unknown or missing data, affecting how SQL treats it in comparisons and operations.

Execution Sample

SQL

SELECT NULL = NULL AS EqualCheck,
       NULL IS NULL AS IsNullCheck;

This query shows that NULL compared to NULL is not true, but IS NULL returns true.

Execution Table

Step	Expression	Evaluation	Result
1	NULL = NULL	Is NULL equal to NULL?	NULL (unknown)
2	NULL IS NULL	Is NULL actually NULL?	TRUE
3	NULL = 5	Is NULL equal to 5?	NULL (unknown)
4	NULL <> 5	Is NULL not equal to 5?	NULL (unknown)
5	NULL IS NOT NULL	Is NULL not NULL?	FALSE

💡 SQL treats NULL as unknown, so comparisons with NULL return NULL (unknown), not TRUE or FALSE.

Variable Tracker

Expression	Result
NULL = NULL	NULL (unknown)
NULL IS NULL	TRUE
NULL = 5	NULL (unknown)
NULL <> 5	NULL (unknown)
NULL IS NOT NULL	FALSE

Key Moments - 3 Insights

Why does 'NULL = NULL' not return TRUE?

Because NULL means unknown, SQL cannot say two unknowns are equal, so it returns NULL (unknown), as shown in execution_table step 1.

How do we properly check if a value is NULL?

Use 'IS NULL' or 'IS NOT NULL' operators, which return TRUE or FALSE, unlike '=' operator. See execution_table step 2.

What happens when NULL is compared to a number?

Any comparison with NULL returns NULL (unknown), not TRUE or FALSE, as shown in steps 3 and 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of 'NULL = NULL' at step 1?

A NULL (unknown)

B FALSE

C TRUE

D Error

💡 Hint

Check the 'Result' column in execution_table row 1.

At which step does the query confirm a value is NULL?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look for 'IS NULL' operator in execution_table.

If you replace 'NULL = 5' with 'NULL IS NULL', what would the result be?

A NULL (unknown)

B FALSE

C TRUE

D Error

💡 Hint

Refer to execution_table step 2 for 'NULL IS NULL' result.

Concept Snapshot

NULL is not a value but a marker for unknown or missing data.
Comparisons with NULL using '=' or '<>' return NULL (unknown), not TRUE or FALSE.
Use 'IS NULL' or 'IS NOT NULL' to check for NULL explicitly.
NULL = NULL returns NULL, but NULL IS NULL returns TRUE.
This affects how SQL queries handle missing information.

Full Transcript

In SQL, NULL represents unknown or missing data, not a real value. When you compare NULL to anything, even another NULL, the result is not TRUE or FALSE but NULL, meaning unknown. To check if a value is NULL, you must use 'IS NULL' or 'IS NOT NULL' operators, which return TRUE or FALSE. This behavior ensures SQL handles missing information carefully without assuming equality or inequality. For example, 'NULL = NULL' returns NULL, but 'NULL IS NULL' returns TRUE. Understanding this helps avoid mistakes in SQL queries involving NULLs.