INTERSECT equivalent in MySQL - Step-by-Step Execution

Concept Flow - INTERSECT equivalent

Start

↓

Query Set A

↓

Find common rows

↓

Output result

↓

End

This flow shows how to find common rows between two queries by selecting rows that appear in both sets.

Execution Sample

MySQL

SELECT column1 FROM table1
WHERE condition1
AND column1 IN (
  SELECT column1 FROM table2 WHERE condition2
);

This query returns rows from table1 where column1 values also appear in table2, simulating INTERSECT.

Execution Table

Step	Action	Query Part	Intermediate Result	Explanation
1	Execute subquery	SELECT column1 FROM table2 WHERE condition2	[B1, B2, B3]	Get values from table2 matching condition2
2	Execute main query with IN	SELECT column1 FROM table1 WHERE condition1 AND column1 IN (subquery)	[A2, A3]	Select rows from table1 where column1 is in subquery result
3	Output final result	Result set	[A2, A3]	Rows common to both queries output
4	End	-	-	No more steps, query complete

💡 All rows from table1 filtered by presence in table2 subquery result, simulating INTERSECT

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
subquery_result	empty	[B1, B2, B3]	[B1, B2, B3]	[B1, B2, B3]
main_query_result	empty	empty	[A2, A3]	[A2, A3]

Key Moments - 2 Insights

Why do we use IN with a subquery instead of INTERSECT?

MySQL does not support INTERSECT directly, so using IN with a subquery finds common rows by checking membership, as shown in execution_table step 2.

What happens if the subquery returns duplicate values?

Duplicates in the subquery do not affect the IN condition because IN checks for existence, not count, so only unique matches matter (see variable_tracker subquery_result).

Visual Quiz - 3 Questions

Look at the execution_table, what is the intermediate result after step 1?

A [A2, A3]

B [B1, B2, B3]

C [A1, A2, A3]

D empty

💡 Hint

Check the 'Intermediate Result' column for step 1 in execution_table

At which step does the main query filter rows based on the subquery?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the 'Action' and 'Query Part' columns in execution_table for step 2

If the subquery returned no rows, what would be the main query result?

A All rows from table1

B All rows from table2

C No rows

D Error

💡 Hint

Refer to variable_tracker subquery_result and main_query_result when subquery_result is empty

Concept Snapshot

INTERSECT finds common rows between two queries.
MySQL lacks INTERSECT, so use IN with a subquery.
Syntax: SELECT col FROM table1 WHERE col IN (SELECT col FROM table2);
This returns rows in table1 also in table2.
Duplicates in subquery don't affect results.
Use this to simulate INTERSECT behavior.

Full Transcript

This visual execution shows how to simulate the INTERSECT operation in MySQL, which does not support INTERSECT directly. The process starts by running the subquery to get a set of values from table2. Then the main query selects rows from table1 where the column value exists in the subquery result, using the IN operator. The execution table traces these steps, showing intermediate results and final output. The variable tracker follows the subquery result and main query result through the steps. Key moments clarify why IN is used and how duplicates in the subquery affect the result. The quiz tests understanding of intermediate results and filtering steps. The snapshot summarizes the approach as a quick reference.