NULL behavior in comparisons in SQL - Step-by-Step Execution

Concept Flow - NULL behavior in comparisons

Start Comparison

↓

Is any operand NULL?

No→Evaluate comparison normally

Yes↓

Result is UNKNOWN (treated as FALSE in WHERE)

↓

Use IS NULL or IS NOT NULL to check NULL explicitly

When comparing values, if any side is NULL, the result is UNKNOWN, not TRUE or FALSE. Use IS NULL or IS NOT NULL to check for NULL explicitly.

Execution Sample

SQL

SELECT * FROM users WHERE age = NULL;
SELECT * FROM users WHERE age IS NULL;

Shows that 'age = NULL' returns no rows, but 'age IS NULL' returns rows where age is NULL.

Execution Table

Step	Expression	Operands	Evaluation	Result
1	age = 25	age=25, 25	25 = 25	TRUE
2	age = NULL	age=25, NULL	25 = NULL	UNKNOWN (treated as FALSE)
3	age = NULL	age=NULL, NULL	NULL = NULL	UNKNOWN (treated as FALSE)
4	age IS NULL	age=NULL	Check if age is NULL	TRUE
5	age IS NULL	age=25	Check if age is NULL	FALSE
6	age <> NULL	age=25, NULL	25 <> NULL	UNKNOWN (treated as FALSE)
7	age IS NOT NULL	age=25	Check if age is NOT NULL	TRUE
8	age IS NOT NULL	age=NULL	Check if age is NOT NULL	FALSE

💡 Comparisons with NULL result in UNKNOWN, which acts like FALSE in WHERE filters; use IS NULL or IS NOT NULL for explicit NULL checks.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7	After Step 8
age	25 or NULL	25 or NULL	25 or NULL	25 or NULL	25 or NULL	25 or NULL	25 or NULL	25 or NULL
comparison_result	N/A	UNKNOWN	UNKNOWN	TRUE or FALSE	TRUE or FALSE	UNKNOWN	TRUE or FALSE	TRUE or FALSE

Key Moments - 3 Insights

Why does 'age = NULL' not return rows even if some ages are NULL?

Because any comparison with NULL results in UNKNOWN, which is treated as FALSE in WHERE clauses. See execution_table rows 2 and 3.

How can I correctly check if a column is NULL?

Use 'IS NULL' or 'IS NOT NULL' operators, which explicitly test for NULL values. See execution_table rows 4 and 5.

Is NULL equal to NULL in SQL?

No, 'NULL = NULL' results in UNKNOWN, not TRUE. Use 'IS NULL' to check for NULL. See execution_table row 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of 'age = NULL' when age is 25?

A TRUE

B FALSE

C UNKNOWN

D NULL

💡 Hint

Check row 2 in the execution_table where age=25 and comparison is 'age = NULL'.

At which step does the expression correctly identify NULL values?

A Step 2

B Step 4

C Step 6

D Step 7

💡 Hint

Look for 'IS NULL' usage in the execution_table.

If you replace 'age = NULL' with 'age IS NULL', how does the result change?

A From UNKNOWN to TRUE

B From TRUE to FALSE

C From UNKNOWN to TRUE or FALSE

D No change

💡 Hint

Compare rows 2, 3 with rows 4, 5 in the execution_table.

Concept Snapshot

NULL in SQL means unknown value.
Comparisons with NULL return UNKNOWN, not TRUE or FALSE.
WHERE treats UNKNOWN as FALSE, so no rows match.
Use IS NULL or IS NOT NULL to check for NULL explicitly.
NULL = NULL is UNKNOWN, not TRUE.

Full Transcript

In SQL, NULL represents an unknown or missing value. When you compare any value to NULL using standard operators like '=', '<>', or '<', the result is not TRUE or FALSE but UNKNOWN. This UNKNOWN behaves like FALSE in WHERE clauses, so no rows match such conditions. To properly check if a column contains NULL, you must use the IS NULL or IS NOT NULL operators. For example, 'age = NULL' returns no rows even if some ages are NULL, but 'age IS NULL' returns those rows. Also, NULL is not equal to NULL; 'NULL = NULL' is UNKNOWN. Remember to use IS NULL for explicit NULL checks.