IS NULL vs equals NULL in SQL - Visual Side-by-Side Comparison

Concept Flow - IS NULL vs equals NULL

Start Query

↓

Check Condition

↓

Return True

↓

Filter Rows

↓

Result Set

The query checks each row's column with IS NULL or = NULL. IS NULL returns true for NULLs, = NULL returns unknown, so no rows match.

Execution Sample

SQL

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

Two queries: one finds rows where email is NULL, the other tries to find rows where email equals NULL but returns no rows.

Execution Table

Step	Row email value	Condition	Evaluation	Row Included in Result?
1	NULL	email IS NULL	TRUE	Yes
2	NULL	email = NULL	UNKNOWN (treated as FALSE)	No
3	'[e-mail]'	email IS NULL	FALSE	No
4	'[e-mail]'	email = NULL	UNKNOWN (treated as FALSE)	No
5	'' (empty string)	email IS NULL	FALSE	No
6	'' (empty string)	email = NULL	UNKNOWN (treated as FALSE)	No

💡 All rows checked; only rows with email IS NULL condition TRUE are included.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	After Row 6
email	N/A	NULL	NULL	'[e-mail]'	'[e-mail]'	''	''
email IS NULL	N/A	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE
email = NULL	N/A	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

Key Moments - 2 Insights

Why does 'email = NULL' never return any rows even if some emails are NULL?

Because in SQL, comparing anything to NULL with '=' results in UNKNOWN, which is treated as FALSE in WHERE filters. See execution_table rows 2,4,6.

What does 'IS NULL' check differently than '='?

'IS NULL' explicitly checks if a value is NULL and returns TRUE for NULLs, allowing those rows to be included. See execution_table rows 1 and 3.

Visual Quiz - 3 Questions

Look at the execution_table, what is the evaluation of 'email = NULL' when email is NULL?

A TRUE

B FALSE

C UNKNOWN

D NULL

💡 Hint

Check row 2 in the execution_table under 'Evaluation' for 'email = NULL' with email NULL.

At which step does 'email IS NULL' return TRUE?

A Step 1

B Step 3

C Step 5

D Step 6

💡 Hint

Look at execution_table rows where 'email IS NULL' is TRUE.

If we replace 'email = NULL' with 'email IS NULL', how would the result change?

A No rows would be returned

B Only rows with NULL emails would be returned

C All rows would be returned

D Only rows with empty string emails would be returned

💡 Hint

Refer to the difference in evaluation between 'email = NULL' and 'email IS NULL' in the execution_table.

Concept Snapshot

IS NULL checks if a value is NULL and returns TRUE for NULLs.
Using '=' to compare with NULL returns UNKNOWN, treated as FALSE.
Use IS NULL to find NULL values in SQL queries.
Example: WHERE column IS NULL returns rows with NULLs.
WHERE column = NULL returns no rows.
Always use IS NULL for NULL checks.

Full Transcript

This visual execution shows how SQL treats NULL comparisons. When checking if a column is NULL, use IS NULL because it returns TRUE for NULL values. Using '=' to compare with NULL returns UNKNOWN, which SQL treats as FALSE, so no rows match. The execution table shows rows with NULL emails pass the IS NULL check but fail the '=' check. This is why IS NULL is the correct way to find NULLs in SQL.