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.

Practice

1. In MySQL, which of the following is the best way to find rows common to two tables table1 and table2 when INTERSECT is not supported?

easy

A. Use SELECT * FROM table1 JOIN table2 ON table1.column = table2.column

B. Use SELECT * FROM table1 UNION SELECT * FROM table2

C. Use SELECT * FROM table1 WHERE column IN (SELECT column FROM table2)

D. Use SELECT * FROM table1 EXCEPT SELECT * FROM table2

Solution

1. Step 1: Understand INTERSECT purpose

   INTERSECT returns rows common to both queries or tables.

2. Step 2: Use WHERE ... IN to mimic INTERSECT

   MySQL lacks INTERSECT, but WHERE column IN (subquery) returns rows in table1 that also appear in table2.

3. Final Answer:

   Use SELECT * FROM table1 WHERE column IN (SELECT column FROM table2) -> Option C

4. Quick Check:

   INTERSECT equivalent = WHERE ... IN [OK]

Hint: Use WHERE ... IN (subquery) to find common rows [OK]

Common Mistakes:

• Using UNION returns all unique rows, not common ones
• JOIN returns combined rows, not just common rows alone
• EXCEPT is not supported in MySQL and finds differences

2. Which of the following is the correct syntax to find common id values between tableA and tableB in MySQL?

easy

A. SELECT id FROM tableA INTERSECT SELECT id FROM tableB;

B. SELECT id FROM tableA WHERE id IN (SELECT id FROM tableB);

C. SELECT id FROM tableA UNION SELECT id FROM tableB;

D. SELECT id FROM tableA EXCEPT SELECT id FROM tableB;

Solution

1. Step 1: Recognize MySQL lacks INTERSECT and EXCEPT

   MySQL does not support INTERSECT or EXCEPT keywords.

2. Step 2: Use WHERE ... IN syntax for intersection

   The correct syntax is SELECT id FROM tableA WHERE id IN (SELECT id FROM tableB); to get common ids.

3. Final Answer:

   SELECT id FROM tableA WHERE id IN (SELECT id FROM tableB); -> Option B

4. Quick Check:

   Correct syntax for INTERSECT equivalent = WHERE ... IN [OK]

Hint: Use WHERE ... IN (subquery) syntax, not INTERSECT [OK]

Common Mistakes:

• Trying to use INTERSECT or EXCEPT directly in MySQL
• Using UNION which combines all unique rows
• Confusing JOIN with intersection

3. Given these tables:
table1:
id
1
2
3

table2:
id
2
3
4

What is the output of this query?

SELECT id FROM table1 WHERE id IN (SELECT id FROM table2);

medium

A. [1, 2, 3]

B. [] (empty set)

C. [4]

D. [2, 3]

Solution

1. Step 1: Identify ids in table1

   table1 has ids 1, 2, 3.

2. Step 2: Identify ids in table2

   table2 has ids 2, 3, 4.

3. Step 3: Find common ids using WHERE ... IN

   The query returns ids from table1 that are also in table2: 2 and 3.

4. Final Answer:

   [2, 3] -> Option D

5. Quick Check:

   Common ids = [2, 3] [OK]

Hint: Check which ids appear in both tables [OK]

Common Mistakes:

• Including ids only in one table (like 1 or 4)
• Confusing UNION with intersection
• Expecting all ids from table1

4. You wrote this query to find common user_id values:

SELECT user_id FROM users WHERE user_id IN (SELECT user_id FROM orders);

But it returns an error. What is the most likely cause?

medium

A. The subquery returns multiple columns instead of one

B. The IN clause is not supported in MySQL

C. The user_id column does not exist in orders

D. You need to use INTERSECT instead of IN

Solution

1. Step 1: Check subquery column count

   The subquery inside IN must return exactly one column.

2. Step 2: Identify error cause

   If the subquery returns multiple columns, MySQL throws an error.

3. Final Answer:

   The subquery returns multiple columns instead of one -> Option A

4. Quick Check:

   Subquery must return single column [OK]

Hint: Ensure subquery returns only one column for IN clause [OK]

Common Mistakes:

• Using multiple columns in subquery for IN
• Assuming INTERSECT exists in MySQL
• Ignoring column existence in tables

5. You want to find all product_id values that appear in both sales and returns tables. Both tables have product_id and date columns. You only want products sold and returned on the same date. Which query correctly finds this intersection?

hard

A. SELECT product_id FROM sales WHERE (product_id, date) IN (SELECT product_id, date FROM returns);

B. SELECT product_id FROM sales WHERE product_id IN (SELECT product_id FROM returns);

C. SELECT product_id FROM sales JOIN returns ON sales.product_id = returns.product_id AND sales.date = returns.date;

D. SELECT product_id FROM sales INTERSECT SELECT product_id FROM returns WHERE sales.date = returns.date;

Solution

1. Step 1: Understand requirement for matching product_id and date

   We need products sold and returned on the same date, so both columns must match.

2. Step 2: Use tuple comparison in WHERE ... IN

   MySQL supports tuple comparisons like (product_id, date) IN (SELECT product_id, date FROM returns) to find exact matches.

3. Step 3: Evaluate other options

   SELECT product_id FROM sales WHERE product_id IN (SELECT product_id FROM returns); ignores date, SELECT product_id FROM sales JOIN returns ON sales.product_id = returns.product_id AND sales.date = returns.date; can produce duplicate product_id values if multiple returns match the same product and date, SELECT product_id FROM sales INTERSECT SELECT product_id FROM returns WHERE sales.date = returns.date; uses unsupported INTERSECT with conditions.

4. Final Answer:

   SELECT product_id FROM sales WHERE (product_id, date) IN (SELECT product_id, date FROM returns); -> Option A

5. Quick Check:

   Tuple IN matches multiple columns [OK]

Hint: Use tuple IN (subquery) for multiple column intersection [OK]

Common Mistakes:

• Ignoring date column in intersection
• Using INTERSECT which MySQL lacks
• Using JOIN which can produce duplicate product_ids