IS NULL and IS NOT NULL in MySQL - Step-by-Step Execution

Concept Flow - IS NULL and IS NOT NULL

Start Query

↓

Check each row

↓

Is column value NULL?

Yes→Include row if IS NULL

No↓

Include row if IS NOT NULL

↓

Return filtered rows

↓

End Query

The query checks each row's column value to see if it is NULL or not NULL, then includes rows accordingly.

Execution Sample

MySQL

SELECT name FROM employees WHERE manager_id IS NULL;
SELECT name FROM employees WHERE manager_id IS NOT NULL;

These queries select employee names where the manager_id is NULL or not NULL.

Execution Table

Step	Row Data (id, name, manager_id)	Condition Checked	Condition Result	Row Included?
1	(1, 'Alice', NULL)	manager_id IS NULL	True	Yes
2	(2, 'Bob', 1)	manager_id IS NULL	False	No
3	(3, 'Charlie', NULL)	manager_id IS NULL	True	Yes
4	(4, 'Diana', 2)	manager_id IS NULL	False	No
5	(5, 'Eve', 3)	manager_id IS NULL	False	No
6	(1, 'Alice', NULL)	manager_id IS NOT NULL	False	No
7	(2, 'Bob', 1)	manager_id IS NOT NULL	True	Yes
8	(3, 'Charlie', NULL)	manager_id IS NOT NULL	False	No
9	(4, 'Diana', 2)	manager_id IS NOT NULL	True	Yes
10	(5, 'Eve', 3)	manager_id IS NOT NULL	True	Yes
11	End of rows	-	-	-

💡 All rows checked; query returns rows where condition is True.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	Final
Current Row	None	(1, 'Alice', NULL)	(2, 'Bob', 1)	(3, 'Charlie', NULL)	(4, 'Diana', 2)	(5, 'Eve', 3)	End
Condition Result (IS NULL)	N/A	True	False	True	False	False	N/A
Condition Result (IS NOT NULL)	N/A	False	True	False	True	True	N/A

Key Moments - 2 Insights

Why does IS NULL check for NULL values instead of using = NULL?

In SQL, NULL means unknown or missing, so = NULL does not work as expected. The execution_table shows that IS NULL correctly identifies NULL values (rows 1 and 3), while = NULL would not.

Can a row be included in both IS NULL and IS NOT NULL results?

No, because a value cannot be both NULL and NOT NULL at the same time. The execution_table rows 1-5 show that each row is included in only one of the two queries.

Visual Quiz - 3 Questions

Look at the execution_table, what is the condition result for row 4 when checking IS NULL?

A True

B Unknown

C False

D Error

💡 Hint

Check row 4 under 'Condition Result' for 'manager_id IS NULL' in the execution_table.

At which step does the condition 'manager_id IS NOT NULL' first become True?

A Step 7

B Step 6

C Step 1

D Step 3

💡 Hint

Look at the execution_table rows 6-10 for 'Condition Result' of IS NOT NULL.

If the value NULL was replaced by 0 in row 1, how would the IS NULL condition result change?

A It would become True

B It would become False

C It would cause an error

D It would remain Unknown

💡 Hint

Refer to variable_tracker for how NULL affects condition results.

Concept Snapshot

IS NULL checks if a column value is NULL (missing/unknown).
IS NOT NULL checks if a column value is not NULL.
Use IS NULL or IS NOT NULL in WHERE clauses to filter rows.
Do NOT use = NULL or <> NULL because they don't work as expected.
Example: SELECT * FROM table WHERE column IS NULL;

Full Transcript

This visual execution shows how the IS NULL and IS NOT NULL conditions work in SQL queries. Each row in the table is checked to see if the specified column value is NULL or not. Rows where the condition is true are included in the result. The execution table traces each row's data, the condition checked, the result of that condition, and whether the row is included in the output. The variable tracker shows how the current row and condition results change step-by-step. Key moments clarify common confusions such as why = NULL does not work and that a value cannot be both NULL and NOT NULL. The quiz questions help reinforce understanding by asking about specific steps and outcomes in the execution. This helps beginners see exactly how SQL handles NULL values in filtering data.