Why equals NULL fails in SQL - Visual Breakdown

Concept Flow - Why equals NULL fails

Start Query

↓

Check condition: column = NULL

↓

Result is UNKNOWN, not TRUE

↓

Row excluded from result

↓

Query returns no rows matching NULL

↓

Use IS NULL instead for NULL check

SQL treats NULL as unknown, so comparing with = NULL returns unknown, not true, causing rows to be excluded.

Execution Sample

SQL

SELECT * FROM users WHERE last_login = NULL;

This query tries to find rows where last_login is NULL using = NULL, but it returns no rows.

Execution Table

Step	Condition Evaluated	Result	Row Included?
1	last_login = NULL for row 1 (last_login = '2023-01-01')	FALSE	No
2	last_login = NULL for row 2 (last_login = NULL)	UNKNOWN	No
3	last_login = NULL for row 3 (last_login = '2023-02-01')	FALSE	No

💡 Rows with FALSE or UNKNOWN for = NULL condition are excluded, so no rows are included in the result.

Variable Tracker

Row	last_login	Condition last_login = NULL	Included in Result
1	'2023-01-01'	FALSE	No
2	NULL	UNKNOWN	No
3	'2023-02-01'	FALSE	No

Key Moments - 2 Insights

Why does comparing a column to NULL with = NULL not return TRUE even if the column is NULL?

In SQL, NULL means unknown, so = NULL does not evaluate to TRUE but to UNKNOWN, which excludes the row from results as shown in execution_table row 2.

How can you correctly check if a column is NULL?

Use IS NULL instead of = NULL. IS NULL returns TRUE when the column is NULL, including those rows in the result.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of the condition last_login = NULL for row 2 where last_login is NULL?

A TRUE

B UNKNOWN

C FALSE

D ERROR

💡 Hint

Check the 'Result' column for step 2 in execution_table.

At which step does the query exclude rows because the condition is UNKNOWN?

A Step 3

B Step 2

C All steps

D Step 1

💡 Hint

Look at the 'Row Included?' column for all steps in execution_table.

If you replace = NULL with IS NULL in the query, what would happen to the row with last_login NULL?

A It would be included

B Query would error

C It would still be excluded

D All rows would be included

💡 Hint

Refer to key_moments explanation about correct NULL checking.

Concept Snapshot

In SQL, NULL means unknown.
Comparing with = NULL returns UNKNOWN, not TRUE.
Rows with UNKNOWN conditions are excluded.
Use IS NULL to check for NULL values correctly.
Example: WHERE column IS NULL returns rows with NULL.

Full Transcript

When you write a SQL query with a condition like column = NULL, it does not work as expected because NULL means unknown. The comparison returns UNKNOWN, not TRUE, so no rows match. To check for NULL values, you must use IS NULL. This is why queries with = NULL return no rows even if NULL values exist. Using IS NULL correctly includes those rows in the result.