INTERSECT equivalent in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select common names from two tables using INNER JOIN.

MySQL

SELECT a.name FROM table1 a INNER JOIN table2 b ON a.[1] = b.name;

Drag options to blanks, or click blank then click option'

Aid

Bvalue

Cname

Ddate

Attempts:

3 left

💡 Hint

Common Mistakes

Using different column names in the ON clause.

Using a column that does not exist in one of the tables.

The INNER JOIN matches rows where the 'name' column is the same in both tables, simulating INTERSECT.

2fill in blank

medium

Complete the code to find common ids using EXISTS to simulate INTERSECT.

MySQL

SELECT id FROM table1 t1 WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.[1] = t1.id);

Drag options to blanks, or click blank then click option'

Aid

Bvalue

Cname

Ddate

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong column in the subquery.

Forgetting to correlate the subquery with the outer query.

The EXISTS clause checks if the id from table1 exists in table2, returning common ids.

3fill in blank

hard

Fix the error in the query to simulate INTERSECT using IN clause.

MySQL

SELECT name FROM table1 WHERE name [1] (SELECT name FROM table2);

Drag options to blanks, or click blank then click option'

AIN

BNOT IN

C=

DLIKE

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which compares only one value, not a list.

Using NOT IN which returns non-matching rows.

The IN operator checks if 'name' from table1 exists in the list of names from table2, simulating INTERSECT.

4fill in blank

hard

Fill both blanks to simulate INTERSECT using INNER JOIN with aliasing.

MySQL

SELECT [1].id FROM [2] a INNER JOIN table2 b ON a.id = b.id;

Drag options to blanks, or click blank then click option'

Aa

Btable1

Ctable_one

DtableA

Attempts:

3 left

💡 Hint

Common Mistakes

Using different aliases in SELECT and FROM.

Using a wrong table name.

The alias 'a' is used to refer to 'table1' in the SELECT and FROM clauses for the join.

5fill in blank

hard

Fill all three blanks to simulate INTERSECT using EXISTS with correct table aliases and column.

MySQL

SELECT [1].name FROM [2] [1] WHERE EXISTS (SELECT 1 FROM [3] WHERE [3].name = [1].name);

Drag options to blanks, or click blank then click option'

At1

Btable1

Ctable2

Dt2

Attempts:

3 left

💡 Hint

Common Mistakes

Using inconsistent aliases.

Mixing up table names in the subquery.

Alias 't1' is used for 'table1' in FROM and SELECT. The EXISTS subquery checks 'table2' for matching names.

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