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.

Practice

1. What does the SQL condition column_name IS NULL check for in a table?

easy

A. It checks if the column value is missing or empty.

B. It checks if the column value is zero.

C. It checks if the column value is a blank string.

D. It checks if the column value is greater than zero.

Solution

1. Step 1: Understand the meaning of NULL in SQL

   NULL means the value is missing or unknown, not zero or empty string.

2. Step 2: Analyze the condition IS NULL

   This condition returns true only when the column has no value (NULL).

3. Final Answer:

   It checks if the column value is missing or unknown. -> Option A

4. Quick Check:

   IS NULL means missing value check [OK]

Hint: IS NULL means value is missing, not zero or empty [OK]

Common Mistakes:

• Confusing NULL with zero or empty string
• Using = NULL instead of IS NULL
• Thinking NULL means zero

2. Which of the following is the correct syntax to select rows where the column email is NOT NULL?

easy

A. SELECT * FROM users WHERE email IS NOT NULL;

B. SELECT * FROM users WHERE email = NOT NULL;

C. SELECT * FROM users WHERE email != NULL;

D. SELECT * FROM users WHERE NOT email = NULL;

Solution

1. Step 1: Recall correct syntax for NOT NULL check

   In SQL, to check for NOT NULL, use IS NOT NULL.

2. Step 2: Evaluate each option

   SELECT * FROM users WHERE email IS NOT NULL; uses correct syntax. Others use invalid or incorrect operators with NULL.

3. Final Answer:

   SELECT * FROM users WHERE email IS NOT NULL; -> Option A

4. Quick Check:

   Use IS NOT NULL to check non-missing values [OK]

Hint: Use IS NOT NULL, not = or != with NULL [OK]

Common Mistakes:

• Using = NULL or != NULL instead of IS NULL/IS NOT NULL
• Forgetting IS keyword
• Using NOT email = NULL which is invalid

3. Given the table orders with columns order_id and shipped_date, what will this query return?

SELECT order_id FROM orders WHERE shipped_date IS NULL;

medium

A. All orders that have a shipped date recorded.

B. All orders that have not been shipped yet.

C. All orders with shipped_date equal to zero.

D. Syntax error due to IS NULL usage.

Solution

1. Step 1: Understand what IS NULL filters

   The condition shipped_date IS NULL selects rows where shipped_date is missing.

2. Step 2: Interpret the meaning in context

   Missing shipped_date means the order has not been shipped yet.

3. Final Answer:

   All orders that have not been shipped yet. -> Option B

4. Quick Check:

   IS NULL finds missing shipped dates = unshipped orders [OK]

Hint: IS NULL finds missing data, here unshipped orders [OK]

Common Mistakes:

• Thinking IS NULL returns shipped orders
• Confusing NULL with zero or empty string
• Assuming syntax error with IS NULL

4. You wrote this query but it returns no rows:

SELECT * FROM employees WHERE manager_id = NULL;

What is the error and how to fix it?

medium

A. Add quotes around NULL like = 'NULL'.

B. Change manager_id to IS NOT NULL.

C. Use IS NULL instead of = NULL to check for NULL values.

D. Use != NULL to find rows with NULL.

Solution

1. Step 1: Identify incorrect NULL comparison

   Using = NULL always returns false because NULL is not equal to anything.

2. Step 2: Correct syntax to check NULL

   Use IS NULL to check for NULL values properly.

3. Final Answer:

   Use IS NULL instead of = NULL to check for NULL values. -> Option C

4. Quick Check:

   Use IS NULL, not = NULL, for NULL checks [OK]

Hint: Always use IS NULL, never = NULL for missing values [OK]

Common Mistakes:

• Using = NULL or != NULL instead of IS NULL/IS NOT NULL
• Adding quotes around NULL
• Confusing IS NULL with IS NOT NULL

5. You want to find customers who have provided a phone number but no email. Which query correctly uses IS NULL and IS NOT NULL to do this?

SELECT * FROM customers WHERE phone IS NOT NULL AND email IS NULL;

hard

A. This query has syntax errors with IS NULL usage.

B. This query returns customers missing both phone and email.

C. This query returns customers with both phone and email.

D. This query returns customers with phone and missing email.

Solution

1. Step 1: Analyze the conditions

   phone IS NOT NULL selects customers who provided a phone number.

2. Step 2: Check the email condition

   email IS NULL selects customers who did not provide an email.

3. Step 3: Combine conditions with AND

   Both conditions together find customers with phone but no email.

4. Final Answer:

   This query returns customers with phone and missing email. -> Option D

5. Quick Check:

   IS NOT NULL and IS NULL combined filter correctly [OK]

Hint: Combine IS NOT NULL and IS NULL with AND for mixed filters [OK]

Common Mistakes:

• Mixing AND with OR incorrectly
• Using = NULL instead of IS NULL
• Confusing which column to check for NULL