Why subqueries nest queries in MySQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why do subqueries nest queries?

Which of the following best explains why subqueries are used to nest queries in SQL?

A To speed up the database by running multiple queries at the same time.

B To create multiple tables from a single query automatically.

C To allow a query to use the result of another query as input for filtering or calculation.

D To avoid writing any conditions in the main query.

💡 Hint

Think about how one query can depend on the result of another.

Subqueries nest queries so that the outer query can use the results of the inner query to filter or calculate data. This allows complex data retrieval in steps.

❓ query_result

intermediate

Output of nested subquery filtering

Given the tables employees(id, name, department_id) and departments(id, name), what is the output of this query?

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

A Names of employees who work in the Sales department.

B Names of all employees regardless of department.

C Names of departments named Sales.

D An error because subqueries cannot be used in WHERE clauses.

💡 Hint

Look at how the inner query selects department IDs for Sales.

The subquery finds the IDs of departments named 'Sales'. The outer query then selects employees whose department_id matches those IDs, so it returns employees in Sales.

📝 Syntax

advanced

Identify the syntax error in this nested subquery

Which option correctly fixes the syntax error in this query?

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales' AND;

A SELECT name FROM employees WHERE department_id = SELECT id FROM departments WHERE name = 'Sales';

B SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales' OR);

C SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales' AND);

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

💡 Hint

Check for incomplete conditions and missing parentheses.

The original query ends with an incomplete AND condition, causing syntax error. Option D completes the subquery properly with a closing parenthesis and no incomplete condition.

❓ optimization

advanced

Optimizing nested subqueries for performance

Which option is the best way to optimize this nested subquery for better performance?

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name LIKE '%Sales%');

A Use a subquery with DISTINCT to remove duplicates.

B Replace the subquery with a JOIN between employees and departments on department_id and id.

C Add an ORDER BY clause inside the subquery.

D Use a UNION instead of IN.

💡 Hint

Think about how JOINs can be more efficient than IN with subqueries.

JOINs often perform better than IN with subqueries because the database can optimize the join operation better, especially with indexes.

🔧 Debug

expert

Debugging unexpected results from nested subqueries

Why does this query return no rows even though there are employees in the 'Marketing' department?

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

A The subquery returns multiple rows, causing the '=' operator to fail.

B The employees table has no matching department_id values.

C The departments table has no 'Marketing' department.

D The query syntax is invalid.

💡 Hint

Check if the subquery returns more than one value when using '='.

If the subquery returns multiple department IDs, using '=' causes an error or no results. Using IN instead of '=' fixes this.

Practice

1. Why do we use subqueries in MySQL?
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

easy

A. To nest one query inside another and use its results

B. To speed up the database server hardware

C. To create new tables automatically

D. To change the database schema

Solution

1. Step 1: Understand subquery usage

   The inner query SELECT id FROM departments WHERE location = 'NY' finds department IDs in New York.

2. Step 2: Use subquery results in outer query

   The outer query selects employees whose department_id matches those IDs from the subquery.

3. Final Answer:

   To nest one query inside another and use its results -> Option A

4. Quick Check:

   Subqueries = nested queries using results [OK]

Hint: Subqueries let you use one query's results inside another [OK]

Common Mistakes:

• Thinking subqueries change database structure
• Confusing subqueries with hardware upgrades
• Believing subqueries create tables automatically

2. Which of the following is the correct syntax for a subquery in MySQL?

easy

A. SELECT * FROM table1 WHERE id = SELECT id FROM table2 WHERE condition;

B. SELECT * FROM table1 WHERE id == (SELECT id FROM table2 WHERE condition);

C. SELECT * FROM table1 WHERE id IN SELECT id FROM table2 WHERE condition;

D. SELECT * FROM table1 WHERE id = (SELECT id FROM table2 WHERE condition);

Solution

1. Step 1: Check subquery syntax

   Subqueries must be enclosed in parentheses and used with operators like = or IN.

2. Step 2: Identify correct syntax

   SELECT * FROM table1 WHERE id = (SELECT id FROM table2 WHERE condition); uses parentheses correctly and the = operator, which is valid syntax.

3. Final Answer:

   SELECT * FROM table1 WHERE id = (SELECT id FROM table2 WHERE condition); -> Option D

4. Quick Check:

   Subquery syntax requires parentheses [OK]

Hint: Always put subqueries inside parentheses [OK]

Common Mistakes:

• Using double equals (==) instead of single equals (=)
• Omitting parentheses around subquery
• Using IN without parentheses

3. What will be the output of this query?

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

Given:
- departments table has ids 1,2,3 with locations NY, LA, LA respectively
- employees table has:
(name: Alice, department_id: 1),
(name: Bob, department_id: 2),
(name: Carol, department_id: 3),
(name: Dave, department_id: 4)

medium

A. Bob, Carol

B. Alice, Bob, Carol

C. Alice, Dave

D. Dave only

Solution

1. Step 1: Identify department IDs in LA

   From departments, IDs with location 'LA' are 2 and 3.

2. Step 2: Find employees in those departments

   Employees with department_id 2 and 3 are Bob and Carol.

3. Final Answer:

   Bob, Carol -> Option A

4. Quick Check:

   Subquery filters departments = Bob, Carol [OK]

Hint: Match outer query filter with subquery results [OK]

Common Mistakes:

• Including employees from other departments
• Ignoring subquery filtering
• Assuming all employees are returned

4. Identify the error in this query:

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

medium

A. Missing WHERE clause in outer query

B. Using = instead of IN for multiple results

C. Missing parentheses around the subquery

D. Wrong table name 'employees'

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses to be valid.

2. Step 2: Identify missing parentheses

   The query lacks parentheses around SELECT id FROM departments WHERE location = 'NY'.

3. Final Answer:

   Missing parentheses around the subquery -> Option C

4. Quick Check:

   Subqueries need parentheses [OK]

Hint: Always wrap subqueries in parentheses [OK]

Common Mistakes:

• Forgetting parentheses causes syntax error
• Confusing = and IN without checking subquery results
• Assuming table names are incorrect without checking

5. You want to find all employees who earn more than the average salary in their department. Which query correctly uses a subquery to achieve this?

hard

A. SELECT name, salary FROM employees WHERE salary > AVG(salary);

B. SELECT name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

C. SELECT name, salary FROM employees WHERE salary > (SELECT salary FROM employees);

D. SELECT name, salary FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

Solution

1. Step 1: Understand the problem

   We want employees earning more than their department's average salary.

2. Step 2: Analyze subquery usage

   SELECT name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); uses a correlated subquery that calculates average salary per employee's department.

3. Step 3: Check other options

   SELECT name, salary FROM employees WHERE salary > AVG(salary); lacks grouping, C compares salary to all salaries, D compares to all averages without correlation.

4. Final Answer:

   SELECT name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); -> Option B

5. Quick Check:

   Correlated subquery per department = SELECT name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); [OK]

Hint: Use correlated subqueries to compare per group [OK]

Common Mistakes:

• Using aggregate functions without grouping
• Not correlating subquery with outer query
• Comparing salary to unrelated values