INTERSECT for common rows in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find common employees in two departments

Given two tables Sales and Marketing with a column employee_id, which query returns the employee IDs that work in both departments?

SQL

SELECT employee_id FROM Sales
INTERSECT
SELECT employee_id FROM Marketing;

A [{"employee_id": 102}, {"employee_id": 104}]

B []

C [{"employee_id": 101}, {"employee_id": 102}, {"employee_id": 103}]

D [{"employee_id": 101}, {"employee_id": 103}]

💡 Hint

INTERSECT returns only rows present in both queries.

The INTERSECT operator returns only the employee IDs that appear in both Sales and Marketing tables. So only employees 101 and 103 are common.

📝 Syntax

intermediate

Identify the correct INTERSECT syntax

Which of the following SQL queries correctly uses INTERSECT to find common product IDs between two tables InventoryA and InventoryB?

A SELECT product_id FROM InventoryA INTERSECT SELECT product_id FROM InventoryB;

B SELECT product_id FROM InventoryA UNION INTERSECT SELECT product_id FROM InventoryB;

C SELECT product_id FROM InventoryA INTERSECT ALL SELECT product_id FROM InventoryB;

D SELECT product_id FROM InventoryA INTERSECT BY SELECT product_id FROM InventoryB;

💡 Hint

INTERSECT does not combine with UNION or use ALL or BY keywords.

Option A uses the correct syntax: SELECT ... INTERSECT SELECT ... without extra keywords. Others are invalid syntax.

❓ optimization

advanced

Optimize query to find common customers

You want to find customers who bought products in both Orders2023 and Orders2024. Which query is the most efficient to get common customer IDs?

A SELECT DISTINCT customer_id FROM Orders2023 WHERE customer_id IN (SELECT customer_id FROM Orders2024);

B SELECT customer_id FROM Orders2023 INTERSECT SELECT customer_id FROM Orders2024;

C SELECT customer_id FROM Orders2023 JOIN Orders2024 USING (customer_id);

D SELECT customer_id FROM Orders2023 UNION SELECT customer_id FROM Orders2024;

💡 Hint

INTERSECT is designed to efficiently find common rows between two queries.

Option B uses INTERSECT which is optimized for this purpose. Option B uses subquery which can be slower. Option B returns duplicates if multiple matches. Option B returns all unique customers, not just common ones.

🧠 Conceptual

advanced

Understanding INTERSECT with duplicates

Consider two tables with these rows:
Table A: (1), (1), (2), (3)
Table B: (1), (2), (2), (4)
What will the result of SELECT val FROM A INTERSECT SELECT val FROM B; be?

A [(1), (2), (3)]

B [(1), (1), (2), (2)]

C [(1), (2)]

D [(1), (2), (3), (4)]

💡 Hint

INTERSECT removes duplicates and returns only common distinct values.

INTERSECT returns distinct values present in both tables. So only 1 and 2 appear in both, once each.

🔧 Debug

expert

Why does this INTERSECT query fail?

Given two tables Employees and Managers with different column counts, why does this query cause an error?
SELECT id, name FROM Employees INTERSECT SELECT id FROM Managers;

SQL

SELECT id, name FROM Employees INTERSECT SELECT id FROM Managers;

A Because the number of columns in both SELECT statements must be the same for INTERSECT.

B Because INTERSECT cannot be used with SELECT statements containing text columns.

C Because the column names must be identical in both SELECT statements.

D Because INTERSECT requires the tables to have the same number of rows.

💡 Hint

INTERSECT requires matching column counts and compatible types.

INTERSECT requires both SELECT statements to have the same number of columns with compatible data types. Here, one has two columns, the other one, causing an error.