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.

Practice

1. What does the SQL INTERSECT operator do?

easy

A. Combines rows from both queries including duplicates.

B. Returns all rows from the first SELECT query.

C. Returns all rows from the second SELECT query.

D. Returns only the rows common to both SELECT queries.

Solution

1. Step 1: Understand the purpose of INTERSECT

   The INTERSECT operator compares two SELECT queries and returns only the rows that appear in both results.

2. Step 2: Compare with other set operators

   Unlike UNION or UNION ALL, INTERSECT excludes rows not common to both queries.

3. Final Answer:

   Returns only the rows common to both SELECT queries. -> Option D

4. Quick Check:

   INTERSECT = common rows [OK]

Hint: INTERSECT keeps only shared rows between two queries [OK]

Common Mistakes:

• Confusing INTERSECT with UNION which combines all rows
• Thinking INTERSECT returns rows from only one query
• Assuming INTERSECT includes duplicates

2. Which of the following is the correct syntax to find common rows between two tables TableA and TableB with the same columns id and name?

easy

A. SELECT id, name FROM TableA JOIN TableB ON TableA.id = TableB.id;

B. SELECT id, name FROM TableA UNION SELECT id, name FROM TableB;

C. SELECT id, name FROM TableA INTERSECT SELECT id, name FROM TableB;

D. SELECT id, name FROM TableA WHERE id IN TableB;

Solution

1. Step 1: Identify correct INTERSECT syntax

   The INTERSECT operator is used between two SELECT statements with matching columns and types.

2. Step 2: Check each option

   SELECT id, name FROM TableA INTERSECT SELECT id, name FROM TableB; uses INTERSECT correctly. SELECT id, name FROM TableA UNION SELECT id, name FROM TableB; uses UNION which combines rows. SELECT id, name FROM TableA JOIN TableB ON TableA.id = TableB.id; uses JOIN which is different. SELECT id, name FROM TableA WHERE id IN TableB; has incorrect WHERE syntax.

3. Final Answer:

   SELECT id, name FROM TableA INTERSECT SELECT id, name FROM TableB; -> Option C

4. Quick Check:

   Correct INTERSECT syntax = SELECT id, name FROM TableA INTERSECT SELECT id, name FROM TableB; [OK]

Hint: Use INTERSECT between two SELECTs with matching columns [OK]

Common Mistakes:

• Using UNION instead of INTERSECT
• Trying to use WHERE with IN incorrectly
• Confusing JOIN with INTERSECT

3. Given two tables:
Employees1:
id | name
1 | Alice
2 | Bob
3 | Carol

Employees2:
id | name
2 | Bob
3 | Carol
4 | Dave

What is the result of this query?

SELECT id, name FROM Employees1 INTERSECT SELECT id, name FROM Employees2;

medium

A. Rows with Bob and Carol only

B. Rows with Alice, Bob, Carol, and Dave

C. Rows with Alice only

D. Rows with Dave only

Solution

1. Step 1: List rows from both tables

   Employees1 has (1, Alice), (2, Bob), (3, Carol). Employees2 has (2, Bob), (3, Carol), (4, Dave).

2. Step 2: Find common rows

   Common rows are those present in both: (2, Bob) and (3, Carol).

3. Final Answer:

   Rows with Bob and Carol only -> Option A

4. Quick Check:

   INTERSECT returns common rows = Bob, Carol [OK]

Hint: INTERSECT returns only rows present in both tables [OK]

Common Mistakes:

• Including rows unique to one table
• Confusing UNION with INTERSECT
• Ignoring column matching in rows

4. Consider this SQL query:

SELECT id, name FROM Customers INTERSECT SELECT id, name FROM Orders;

It returns an error. What is the most likely cause?

medium

A. The columns selected have different data types in Customers and Orders.

B. INTERSECT cannot be used with SELECT statements.

C. The tables Customers and Orders do not exist.

D. INTERSECT requires a WHERE clause.

Solution

1. Step 1: Understand INTERSECT requirements

   INTERSECT requires both SELECT queries to have the same number of columns with compatible data types.

2. Step 2: Analyze error causes

   If columns differ in type (e.g., id is integer in one table and string in another), the query errors. Other options are incorrect because INTERSECT is valid syntax, tables must exist to run, and WHERE is optional.

3. Final Answer:

   The columns selected have different data types in Customers and Orders. -> Option A

4. Quick Check:

   Matching column types required for INTERSECT [OK]

Hint: Check column types match for INTERSECT queries [OK]

Common Mistakes:

• Assuming INTERSECT needs WHERE clause
• Thinking INTERSECT is invalid syntax
• Ignoring data type mismatches

5. You have two tables:
ProductsA with columns product_id, name, price
ProductsB with columns product_id, name, price

You want to find products that exist in both tables with the same product_id and name, ignoring price differences.

Which query correctly uses INTERSECT to achieve this?

hard

A. SELECT product_id, name, price FROM ProductsA INTERSECT SELECT product_id, name, price FROM ProductsB;

B. SELECT product_id, name FROM ProductsA INTERSECT SELECT product_id, name FROM ProductsB;

C. SELECT product_id FROM ProductsA INTERSECT SELECT product_id FROM ProductsB;

D. SELECT * FROM ProductsA INTERSECT SELECT * FROM ProductsB;

Solution

1. Step 1: Identify columns to compare

   You want to compare only product_id and name, ignoring price differences.

2. Step 2: Use INTERSECT on matching columns

   SELECT product_id, name FROM ProductsA INTERSECT SELECT product_id, name FROM ProductsB; selects product_id and name from both tables and intersects them, returning only products common by those two columns.

3. Step 3: Analyze other options

   SELECT product_id, name, price FROM ProductsA INTERSECT SELECT product_id, name, price FROM ProductsB; includes price, so products with different prices won't match. SELECT product_id FROM ProductsA INTERSECT SELECT product_id FROM ProductsB; compares only product_id, ignoring name. SELECT * FROM ProductsA INTERSECT SELECT * FROM ProductsB; compares all columns, including price, which is not desired.

4. Final Answer:

   SELECT product_id, name FROM ProductsA INTERSECT SELECT product_id, name FROM ProductsB; -> Option B

5. Quick Check:

   INTERSECT on selected columns matches desired fields [OK]

Hint: Select only columns to compare before INTERSECT [OK]

Common Mistakes:

• Including extra columns that cause mismatches
• Selecting * when columns differ
• Comparing only one column when two are needed