INTERSECT equivalent in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find common customers using INTERSECT equivalent

Given two tables CustomersA and CustomersB with a column customer_id, which query correctly returns the customer IDs present in both tables using MySQL syntax?

MySQL

SELECT customer_id FROM CustomersA
INTERSECT
SELECT customer_id FROM CustomersB;

A SELECT customer_id FROM CustomersA EXCEPT SELECT customer_id FROM CustomersB;

B SELECT customer_id FROM CustomersA JOIN CustomersB ON CustomersA.customer_id = CustomersB.customer_id;

C SELECT DISTINCT customer_id FROM CustomersA UNION SELECT DISTINCT customer_id FROM CustomersB;

D SELECT customer_id FROM CustomersA WHERE customer_id IN (SELECT customer_id FROM CustomersB);

💡 Hint

MySQL does not support INTERSECT directly; think about how to find common values using subqueries.

Option D uses a WHERE IN clause to select customer IDs from CustomersA that also exist in CustomersB, effectively mimicking INTERSECT behavior. Option D returns duplicates if multiple matches exist and may return more columns if not careful. Option D returns all unique customer IDs from both tables (union). Option D uses EXCEPT which is not supported in MySQL and would cause an error.

❓ query_result

intermediate

Using JOIN to simulate INTERSECT

Which query returns the same result as INTERSECT for two tables Orders1 and Orders2 on column order_id in MySQL?

MySQL

SELECT order_id FROM Orders1
INTERSECT
SELECT order_id FROM Orders2;

A SELECT DISTINCT Orders1.order_id FROM Orders1 INNER JOIN Orders2 ON Orders1.order_id = Orders2.order_id;

B SELECT order_id FROM Orders1 WHERE order_id NOT IN (SELECT order_id FROM Orders2);

C SELECT order_id FROM Orders1 UNION SELECT order_id FROM Orders2;

D SELECT order_id FROM Orders1 LEFT JOIN Orders2 ON Orders1.order_id = Orders2.order_id WHERE Orders2.order_id IS NULL;

💡 Hint

Think about how INNER JOIN returns only matching rows.

Option A uses INNER JOIN to return only order_ids present in both tables, simulating INTERSECT. Option A returns order_ids in Orders1 but not in Orders2 (difference). Option A returns all unique order_ids from both tables (union). Option A returns order_ids in Orders1 but not in Orders2 (difference).

📝 Syntax

advanced

Identify the syntax error in INTERSECT simulation

Which of the following MySQL queries will cause a syntax error when trying to simulate INTERSECT?

A SELECT customer_id FROM CustomersA INTERSECT SELECT customer_id FROM CustomersB;

B SELECT customer_id FROM CustomersA JOIN CustomersB USING (customer_id);

C SELECT DISTINCT CustomersA.customer_id FROM CustomersA INNER JOIN CustomersB ON CustomersA.customer_id = CustomersB.customer_id;

D SELECT customer_id FROM CustomersA WHERE customer_id IN (SELECT customer_id FROM CustomersB);

💡 Hint

Check if MySQL supports the INTERSECT keyword.

Option A uses INTERSECT which is not supported in MySQL and will cause a syntax error. Options A, B, and C are valid MySQL queries that simulate INTERSECT behavior.

❓ optimization

advanced

Optimizing INTERSECT equivalent query

Given large tables EmployeesA and EmployeesB with column emp_id, which query is the most efficient way to find common emp_ids in MySQL?

A SELECT emp_id FROM EmployeesA UNION SELECT emp_id FROM EmployeesB;

B SELECT DISTINCT EmployeesA.emp_id FROM EmployeesA INNER JOIN EmployeesB ON EmployeesA.emp_id = EmployeesB.emp_id;

C SELECT emp_id FROM EmployeesA WHERE emp_id IN (SELECT emp_id FROM EmployeesB);

D SELECT emp_id FROM EmployeesA LEFT JOIN EmployeesB ON EmployeesA.emp_id = EmployeesB.emp_id WHERE EmployeesB.emp_id IS NOT NULL;

💡 Hint

Consider how JOINs and indexes affect performance on large datasets.

Option B uses INNER JOIN with DISTINCT which is generally more efficient on large datasets if indexes exist on emp_id columns. Option B uses a subquery which can be slower. Option B returns union, not intersection. Option B uses LEFT JOIN with a filter which is less efficient than INNER JOIN.

🧠 Conceptual

expert

Understanding INTERSECT behavior with duplicates

Consider two tables Table1 and Table2 each having duplicate values in column val. Which MySQL query best simulates the INTERSECT operation that returns each common value only once, regardless of duplicates?

A SELECT val FROM Table1 WHERE val IN (SELECT val FROM Table2);

B SELECT val FROM Table1 INNER JOIN Table2 ON Table1.val = Table2.val;

C SELECT DISTINCT val FROM Table1 INNER JOIN Table2 ON Table1.val = Table2.val;

D SELECT val FROM Table1 UNION SELECT val FROM Table2;

💡 Hint

INTERSECT returns unique common values, duplicates removed.

Option C uses INNER JOIN with DISTINCT to return unique common values, matching INTERSECT behavior. Option C may return duplicates if Table1 has duplicates. Option C returns duplicates from the join. Option C returns all unique values from both tables (union).