Subqueries vs JOINs comparison in MySQL - Practice Questions

Challenge - 5 Problems

❓ query_result

intermediate

Output of subquery filtering

Given the tables employees and departments, what is the output of this query?

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

MySQL

CREATE TABLE employees (id INT, name VARCHAR(50), department_id INT);
CREATE TABLE departments (id INT, name VARCHAR(50), location VARCHAR(50));

INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', 10);
INSERT INTO departments VALUES (10, 'Sales', 'NY'), (20, 'HR', 'LA');

A [{"name": "Bob"}]

B []

C [{"name": "Alice"}, {"name": "Bob"}, {"name": "Charlie"}]

D [{"name": "Alice"}, {"name": "Charlie"}]

💡 Hint

Think about which departments are located in NY and which employees belong to those departments.

The subquery selects department IDs where location is 'NY' (id=10). Employees with department_id 10 are Alice and Charlie.

❓ query_result

intermediate

Output of JOIN with filtering

What is the output of this JOIN query?

SELECT e.name, d.location FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.location = 'LA';

MySQL

CREATE TABLE employees (id INT, name VARCHAR(50), department_id INT);
CREATE TABLE departments (id INT, name VARCHAR(50), location VARCHAR(50));

INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', 10);
INSERT INTO departments VALUES (10, 'Sales', 'NY'), (20, 'HR', 'LA');

A [{"name": "Alice", "location": "LA"}, {"name": "Bob", "location": "LA"}, {"name": "Charlie", "location": "LA"}]

B [{"name": "Alice", "location": "NY"}, {"name": "Charlie", "location": "NY"}]

C [{"name": "Bob", "location": "LA"}]

D []

💡 Hint

Look at the JOIN condition and the WHERE clause filtering by location.

The JOIN matches employees to departments by department_id. Only Bob belongs to department 20 which is located in LA.

🧠 Conceptual

advanced

Performance difference between subqueries and JOINs

Which statement about performance is generally true when comparing subqueries and JOINs in MySQL?

A JOINs often perform better than subqueries because they allow the database to optimize data retrieval more efficiently.

B Subqueries always perform better than JOINs because they reduce the number of rows processed.

C JOINs and subqueries always have the same performance in all cases.

D Subqueries are faster because they execute once and cache results automatically.

💡 Hint

Think about how databases optimize queries involving JOINs versus subqueries.

JOINs let the database engine combine tables in a single operation, often using indexes efficiently, while subqueries may be executed repeatedly or less efficiently.

📝 Syntax

advanced

Identify the syntax error in subquery usage

Which option contains a syntax error in using a subquery in the WHERE clause?

A SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

B SELECT name FROM employees WHERE department_id = (SELECT id, location FROM departments WHERE location = 'NY');

C SELECT name FROM employees WHERE department_id = ANY (SELECT id FROM departments WHERE location = 'NY');

D SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE location = 'NY');

💡 Hint

Check if the subquery returns a single column when used with '=' operator.

Option B's subquery returns two columns, which is invalid when using '=' expecting a single value.

❓ optimization

expert

Optimizing query with subquery vs JOIN

You want to list all employees who have made sales. The sales table records sales with employee_id. Which query is generally more efficient in MySQL for large datasets?

A SELECT e.name FROM employees e JOIN sales s ON e.id = s.employee_id;

B SELECT e.name FROM employees e WHERE EXISTS (SELECT 1 FROM sales s WHERE s.employee_id = e.id);

C SELECT e.name FROM employees e WHERE e.id = (SELECT employee_id FROM sales LIMIT 1);

D SELECT e.name FROM employees e WHERE e.id IN (SELECT employee_id FROM sales);

💡 Hint

Consider how JOINs and subqueries scale with large tables and indexing.

JOINs typically allow the database to use indexes and optimize the query plan better than IN or EXISTS subqueries on large datasets.