WHERE with IS NULL and IS NOT NULL in SQL - Step-by-Step Execution

Concept Flow - WHERE with IS NULL and IS NOT NULL

Start Query

↓

Scan Table Rows

↓

Check WHERE Condition

↓

Is value IS NULL?

Yes→Include Row

↓

Is value IS NOT NULL?

Yes→Include Row

↓

Exclude Row

↓

Return Filtered Rows

↓

End Query

The query scans each row, checks if a column is NULL or NOT NULL based on the WHERE clause, and includes or excludes rows accordingly.

Execution Sample

SQL

SELECT * FROM employees
WHERE manager_id IS NULL;

This query selects all employees who do not have a manager assigned (manager_id is 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', 1)	manager_id IS NULL	False	No
4	(4, 'Diana', NULL)	manager_id IS NULL	True	Yes
5	(5, 'Eve', 3)	manager_id IS NULL	False	No
6	End of rows			Query ends

💡 All rows checked; only rows with manager_id NULL included.

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', 1)	(4, 'Diana', NULL)	(5, 'Eve', 3)	End
Condition Result	N/A	True	False	False	True	False	N/A
Rows Included	[]	[(1, 'Alice', NULL)]	[(1, 'Alice', NULL)]	[(1, 'Alice', NULL)]	[(1, 'Alice', NULL), (4, 'Diana', NULL)]	[(1, 'Alice', NULL), (4, 'Diana', NULL)]	[(1, 'Alice', NULL), (4, 'Diana', NULL)]

Key Moments - 2 Insights

Why does the condition 'manager_id IS NULL' only include rows where manager_id is exactly NULL?

Because IS NULL checks specifically for NULL values, not zero or empty strings. Rows with any other value fail the condition, as shown in execution_table rows 2, 3, and 5.

What happens if we use 'IS NOT NULL' instead?

The query would include only rows where manager_id has a value (not NULL). This is the opposite of the current condition, so rows included would be those excluded here (rows 2, 3, and 5).

Visual Quiz - 3 Questions

Look at the execution_table, what is the condition result for row 4?

A True

B False

C Unknown

D Error

💡 Hint

Check the 'Condition Result' column for step 4 in the execution_table.

At which step does the query exclude a row because the condition is false?

A Step 1

B Step 4

C Step 2

D Step 6

💡 Hint

Look for the first 'False' in the 'Condition Result' column and see if the row was included.

If we change the condition to 'manager_id IS NOT NULL', which rows would be included?

A Rows 1 and 4

B Rows 2, 3, and 5

C All rows

D No rows

💡 Hint

Refer to the explanation in key_moments about IS NOT NULL including rows with values.

Concept Snapshot

WHERE with IS NULL and IS NOT NULL:
- Use IS NULL to filter rows where a column has no value (NULL).
- Use IS NOT NULL to filter rows where a column has any value.
- NULL is not equal to anything, so use IS NULL, not '= NULL'.
- The query checks each row and includes it if the condition matches.
- Useful to find missing or present data in columns.

Full Transcript

This visual execution shows how a SQL query with WHERE and IS NULL works. The query scans each row of the employees table. For each row, it checks if the manager_id column is NULL. If yes, the row is included in the result. Otherwise, it is excluded. The execution table lists each row, the condition checked, the result, and whether the row is included. The variable tracker shows how the current row and included rows change step by step. Key moments clarify common confusions about NULL checks. The quiz tests understanding of condition results and row inclusion. The snapshot summarizes the key points about using IS NULL and IS NOT NULL in WHERE clauses.