Subquery vs JOIN performance trade-off in SQL - Practice Questions

Challenge - 5 Problems

❓ query_result

intermediate

Output of JOIN vs Subquery with filtering

Given two tables employees and departments, which query returns the names of employees who work in the 'Sales' department?

Table employees: (id, name, dept_id)

Table departments: (id, dept_name)

SQL

SELECT e.name FROM employees e JOIN departments d ON e.dept_id = d.id WHERE d.dept_name = 'Sales';

A SELECT e.name FROM employees e JOIN departments d ON e.dept_id = d.id WHERE d.dept_name = 'Sales';

B SELECT name FROM employees WHERE dept_id = (SELECT id FROM departments WHERE dept_name = 'Sales');

C SELECT name FROM employees WHERE dept_id IN (SELECT id FROM departments WHERE dept_name = 'Sales');

D SELECT name FROM employees WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'Sales');

💡 Hint

Think about how JOIN matches rows and how subqueries filter results.

Option A uses a JOIN correctly to match employees with the 'Sales' department. Option A uses a subquery but assumes only one department id, which is valid here but less flexible. Option A uses IN which works but is less efficient. Option A is invalid because departments has no dept_id column.

🧠 Conceptual

intermediate

Performance difference between JOIN and Subquery

Which statement best describes the typical performance difference between using a JOIN and a subquery in SQL when retrieving related data?

A JOINs often perform better because they allow the database optimizer to use indexes and combine data efficiently.

B Subqueries are always faster because they filter data before joining.

C JOINs are always slower because they combine tables before filtering.

D There is no performance difference; both always execute the same way.

💡 Hint

Consider how databases optimize queries and use indexes.

JOINs typically allow the database engine to optimize data retrieval better by using indexes and combining tables efficiently. Subqueries can sometimes be less efficient, especially correlated subqueries.

📝 Syntax

advanced

Identify the syntax error in subquery usage

Which SQL query has a syntax error when trying to select employees working in the 'Marketing' department?

SQL

SELECT name FROM employees WHERE dept_id = (SELECT id FROM departments WHERE dept_name = 'Marketing');

A SELECT name FROM employees WHERE dept_id = (SELECT id FROM departments WHERE dept_name = 'Marketing');

B SELECT name FROM employees WHERE dept_id IN (SELECT id FROM departments WHERE dept_name = 'Marketing');

C SELECT name FROM employees WHERE dept_id = ANY (SELECT id FROM departments WHERE dept_name = 'Marketing');

D SELECT name FROM employees WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'Marketing');

💡 Hint

Check if the column names in the subquery exist in the referenced table.

Option D references dept_id in departments which does not exist. The correct column is id. Other options use valid syntax.

❓ optimization

advanced

Optimizing query with JOIN vs Subquery

You want to list all employees with their department names. Which query is generally more efficient on large datasets?

A SELECT e.name, (SELECT dept_name FROM departments d WHERE d.id = e.dept_id) AS department FROM employees e;

B SELECT name, dept_name FROM employees, departments WHERE employees.dept_id = departments.id;

C SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.id;

D SELECT name, dept_name FROM employees LEFT JOIN departments ON employees.dept_id = departments.id WHERE departments.dept_name IS NOT NULL;

💡 Hint

Consider how JOINs and subqueries execute on large tables.

Option C uses an explicit JOIN which is generally optimized better by databases. Option C uses a correlated subquery which can be slow on large datasets. Option C uses implicit join syntax which is less clear but similar to A. Option C filters out employees without departments, which may not be desired.

🔧 Debug

expert

Diagnose performance issue with correlated subquery

A query uses a correlated subquery to count orders per customer:

SELECT c.customer_id, c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count FROM customers c;

It runs very slowly on large data. What is the main reason?

A The query lacks a GROUP BY clause, causing aggregation errors.

B The subquery runs once per customer, causing many repeated scans of the orders table.

C The JOIN between customers and orders is missing, causing incomplete results.

D The COUNT(*) function is not supported in subqueries.

💡 Hint

Think about how correlated subqueries execute for each row.

Correlated subqueries run once for each row in the outer query, causing repeated scans of the orders table, which is inefficient on large datasets. Using a JOIN with GROUP BY is usually faster.