WHERE with IN list in SQL - Step-by-Step Execution

Concept Flow - WHERE with IN list

Start Query

↓

Evaluate WHERE clause

↓

Check if column value IN list?

↓

Include row

↓

Return Result Rows

The query checks each row's column value against a list. If the value is in the list, the row is included in the result.

Execution Sample

SQL

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

This query selects all employees who work in Sales, HR, or IT departments.

Execution Table

Step	Row ID	department value	Condition: department IN ('Sales','HR','IT')	Include Row?
1	101	Sales	Sales IN list → True	Yes
2	102	Marketing	Marketing IN list → False	No
3	103	HR	HR IN list → True	Yes
4	104	Finance	Finance IN list → False	No
5	105	IT	IT IN list → True	Yes
6	106	Support	Support IN list → False	No
7	N/A	N/A	No more rows	Stop

💡 All rows checked; query returns rows where department is in the given list.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	After Row 6	Final
Current Row ID	N/A	101	102	103	104	105	106	End
Current department	N/A	Sales	Marketing	HR	Finance	IT	Support	End
Include Row?	N/A	Yes	No	Yes	No	Yes	No	End

Key Moments - 2 Insights

Why does the query exclude rows where the department is not in the list?

Because the WHERE clause condition 'department IN (...)' evaluates to False for those rows, so they are filtered out as shown in execution_table rows 2, 4, and 6.

What happens if the department value is NULL?

NULL values do not match any value in the IN list, so the condition evaluates to Unknown (treated as False), and those rows are excluded.

Visual Quiz - 3 Questions

Look at the execution_table, what is the 'Include Row?' value for Row ID 103?

A No

B Unknown

C Yes

D Not checked

💡 Hint

Check the row with Row ID 103 in the execution_table under 'Include Row?' column.

At which step does the condition 'department IN list' become False for the first time?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

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

If we add 'Support' to the IN list, how would the 'Include Row?' value change for Row ID 106?

A Remain No

B Change from No to Yes

C Change to Unknown

D Row would be excluded anyway

💡 Hint

Refer to variable_tracker and execution_table rows for Row ID 106 and consider the IN list membership.

Concept Snapshot

WHERE with IN list:
Use WHERE column IN (value1, value2, ...)
Filters rows where column matches any listed value.
Rows not matching are excluded.
NULL values do not match any list item.
Useful for checking multiple possible values easily.

Full Transcript

This visual execution shows how the SQL WHERE clause with IN list works. The query checks each row's department value against a list of departments: 'Sales', 'HR', and 'IT'. For each row, if the department is in the list, the row is included in the result. Otherwise, it is excluded. The execution table traces each row's evaluation step by step. The variable tracker shows how the current row and decision change as the query processes rows. Key moments clarify why rows are excluded and how NULL values behave. The quiz tests understanding by asking about specific steps and effects of changing the IN list.

Practice

1. What does the SQL clause WHERE column_name IN (value1, value2, value3) do?

easy

A. It selects rows where column_name matches any value in the list.

B. It updates rows with values in the list.

C. It deletes rows not matching the list.

D. It creates a new table with listed values.

Solution

1. Step 1: Understand the IN clause purpose

   The IN clause checks if a column's value matches any value inside the parentheses list.

2. Step 2: Identify the effect on row selection

   Rows with the column value equal to any listed value are selected; others are ignored.

3. Final Answer:

   It selects rows where column_name matches any value in the list. -> Option A

4. Quick Check:

   WHERE with IN filters rows by listed values [OK]

Hint: IN checks if value matches any in the list [OK]

Common Mistakes:

• Thinking IN updates or deletes rows
• Confusing IN with creating tables
• Assuming IN matches all values at once

2. Which of the following is the correct syntax to select rows where status is either 'active', 'pending', or 'closed'?

easy

A. SELECT * FROM orders WHERE status = IN ('active', 'pending', 'closed');

B. SELECT * FROM orders WHERE status IN ('active', 'pending', 'closed');

C. SELECT * FROM orders WHERE status IN 'active', 'pending', 'closed';

D. SELECT * FROM orders WHERE status IN ('active' 'pending' 'closed');

Solution

1. Step 1: Check correct use of IN syntax

   The correct syntax is column_name IN (value1, value2, ...) without an equals sign.

2. Step 2: Identify correct punctuation and parentheses

   Values must be inside parentheses and separated by commas.

3. Final Answer:

   SELECT * FROM orders WHERE status IN ('active', 'pending', 'closed'); -> Option B

4. Quick Check:

   IN syntax = column IN (values) [OK]

Hint: No '=' before IN; use commas inside parentheses [OK]

Common Mistakes:

• Using '=' before IN
• Missing commas between values
• Omitting parentheses around values

3. Given the table employees with column department, what rows will this query return?

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

medium

A. Rows where department is 'HR' or 'Sales'.

B. Rows where department is 'HR' only.

C. Rows where department is not 'HR' or 'Sales'.

D. All rows regardless of department.

Solution

1. Step 1: Understand the IN filter on department

   The query filters rows where department matches any value in ('HR', 'Sales').

2. Step 2: Determine which rows match

   Only rows with department 'HR' or 'Sales' are returned; others are excluded.

3. Final Answer:

   Rows where department is 'HR' or 'Sales'. -> Option A

4. Quick Check:

   IN filters rows matching listed values [OK]

Hint: IN returns rows matching any listed value [OK]

Common Mistakes:

• Thinking it returns rows not in the list
• Assuming it returns only one value
• Confusing IN with NOT IN

4. Identify the error in this SQL query:

SELECT * FROM products WHERE category IN 'Electronics', 'Toys', 'Books';

medium

A. Incorrect table name.

B. Using IN instead of = operator.

C. Missing parentheses around the list of values.

D. Missing commas between values.

Solution

1. Step 1: Check syntax of IN clause

   The list of values after IN must be enclosed in parentheses.

2. Step 2: Identify missing parentheses

   The query has values listed without parentheses, causing syntax error.

3. Final Answer:

   Missing parentheses around the list of values. -> Option C

4. Quick Check:

   IN requires parentheses around values [OK]

Hint: Always put parentheses around IN value list [OK]

Common Mistakes:

• Omitting parentheses around values
• Using IN with equals sign
• Forgetting commas between values

5. You want to select customers from the orders table whose country is either 'USA', 'Canada', or 'Mexico', but exclude those with status 'cancelled'. Which query correctly uses WHERE with IN and excludes cancelled orders?

hard

A. SELECT * FROM orders WHERE country IN 'USA', 'Canada', 'Mexico' AND status != 'cancelled';

B. SELECT * FROM orders WHERE country IN ('USA', 'Canada', 'Mexico') OR status != 'cancelled';

C. SELECT * FROM orders WHERE country = IN ('USA', 'Canada', 'Mexico') AND status != 'cancelled';

D. SELECT * FROM orders WHERE country IN ('USA', 'Canada', 'Mexico') AND status != 'cancelled';

Solution

1. Step 1: Use IN correctly for country filter

   The country column must be checked with IN ('USA', 'Canada', 'Mexico') without equals sign.

2. Step 2: Exclude cancelled orders with AND condition

   Use AND status != 'cancelled' to exclude those rows.

3. Final Answer:

   SELECT * FROM orders WHERE country IN ('USA', 'Canada', 'Mexico') AND status != 'cancelled'; -> Option D

4. Quick Check:

   IN filters list, AND excludes cancelled [OK]

Hint: Use AND to combine IN filter and exclusion [OK]

Common Mistakes:

• Using OR instead of AND causing wrong results
• Adding '=' before IN
• Omitting parentheses around IN values