IN and NOT IN operators in MySQL - Step-by-Step Execution

Concept Flow - IN and NOT IN operators

Start Query

↓

Evaluate IN/NOT IN List

↓

Check if value is in list?

Yes No↓

Return True/False

↓

Filter Rows Based on Result

↓

Return Result Set

The query checks if a value is inside or outside a list, then filters rows accordingly.

Execution Sample

MySQL

SELECT name FROM employees WHERE department IN ('Sales', 'HR');

This query returns names of employees who work in Sales or HR departments.

Execution Table

Step	Row Data (name, department)	Condition (department IN ('Sales','HR'))	Result	Action
1	('Alice', 'Sales')	Sales IN list	True	Include row
2	('Bob', 'IT')	IT IN list	False	Exclude row
3	('Carol', 'HR')	HR IN list	True	Include row
4	('Dave', 'Marketing')	Marketing IN list	False	Exclude row
5	('Eve', 'Sales')	Sales IN list	True	Include row
Exit	-	-	-	All rows checked, query ends

💡 All rows checked, filtering done based on IN condition

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Current Row	None	('Alice', 'Sales')	('Bob', 'IT')	('Carol', 'HR')	('Dave', 'Marketing')	('Eve', 'Sales')	None
Condition Result	None	True	False	True	False	True	None
Included Rows	[]	['Alice']	['Alice']	['Alice', 'Carol']	['Alice', 'Carol']	['Alice', 'Carol', 'Eve']	['Alice', 'Carol', 'Eve']

Key Moments - 3 Insights

Why does the query exclude 'Bob' and 'Dave'?

Because their departments ('IT' and 'Marketing') are not in the list ('Sales', 'HR'), so the condition is False (see execution_table rows 2 and 4).

What happens if the list in IN is empty?

The condition will always be False, so no rows will be included. This is because no value can be found in an empty list.

How does NOT IN differ from IN?

NOT IN returns True when the value is NOT in the list, so it includes rows excluded by IN. For example, NOT IN ('Sales', 'HR') would include 'Bob' and 'Dave' instead.

Visual Quiz - 3 Questions

Look at the execution_table, what is the condition result for 'Carol' at step 3?

A False

B Unknown

C True

D Error

💡 Hint

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

At which step does the condition become False for the first time?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the 'Condition Result' column in execution_table and find the first False.

If we change the list to IN ('IT', 'Marketing'), which rows would be included?

A Alice, Carol, Eve

B Bob, Dave

C All rows

D No rows

💡 Hint

Refer to variable_tracker and execution_table to see which departments match the new list.

Concept Snapshot

IN and NOT IN operators check if a value is inside or outside a list.
Syntax: column IN (value1, value2, ...)
Returns rows where condition is True.
NOT IN returns rows where value is not in the list.
Used to filter rows easily by multiple values.

Full Transcript

This visual execution shows how the IN and NOT IN operators work in MySQL queries. The query checks each row's department against a list of values. If the department is in the list, the condition is True and the row is included in the result. If not, the row is excluded. The execution table traces each row's evaluation step-by-step. The variable tracker shows how the current row and condition result change over time, and which rows get included. Key moments clarify common confusions like why some rows are excluded and how NOT IN differs. The quiz tests understanding by asking about specific steps and changes. This helps beginners see exactly how IN and NOT IN filter data in a query.