[Solved] Given the tables products(id, category_id, price) and categories(id, name), which query will return the same results but likely with better performance?Query A:SELECT p.id FROM products p WHERE p.... | SQL

SQL - Subqueries

Given the tables products(id, category_id, price) and categories(id, name), which query will return the same results but likely with better performance?

Query A:
SELECT p.id FROM products p WHERE p.category_id IN (SELECT c.id FROM categories c WHERE c.name = 'Electronics');
Query B:
SELECT p.id FROM products p JOIN categories c ON p.category_id = c.id WHERE c.name = 'Electronics';

AQuery A, because subqueries are simpler to execute.

BQuery B, because JOINs allow the optimizer to use indexes efficiently.

CBoth queries perform identically in all cases.

DQuery A, because it avoids the overhead of JOIN operations.

Step-by-Step Solution

Solution:

Step 1: Understand the IN subquery version
The IN subquery version uses a subquery with IN(), which may be less efficient on large datasets.
Step 2: Understand the JOIN version
The JOIN version uses a JOIN, which databases optimize well, especially with indexes on join columns.
Final Answer:
Query B, because JOINs allow the optimizer to use indexes efficiently. -> Option B
Quick Check:
JOIN with indexes = better performance [OK]

Quick Trick: JOINs leverage indexes better than subqueries [OK]

Common Mistakes:

MISTAKES

Assuming subqueries are always simpler and faster
Ignoring the role of indexes in JOIN performance
Believing IN() subqueries scale well always

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Given the tables products(id, category_id, price) and categories(id, name), which query will return the same results but likely with better performance?

Step 1: Understand the IN subquery version

Step 2: Understand the JOIN version

Final Answer:

Quick Check:

Want More Practice?