Why subqueries nest queries in MySQL - Visual Breakdown

Concept Flow - Why subqueries nest queries

Start Outer Query

↓

Encounter Subquery

↓

Execute Subquery First

↓

Return Subquery Result

↓

Use Result in Outer Query

↓

Complete Outer Query Execution

The outer query pauses to run the inner subquery first, then uses its result to continue and finish.

Execution Sample

MySQL

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

Find employees who work in the 'Sales' department by first finding the department's id.

Execution Table

Step	Action	Query Part	Result/Value
1	Start outer query	SELECT name FROM employees WHERE department_id = (...)	Waiting for subquery result
2	Execute subquery	SELECT id FROM departments WHERE name = 'Sales'	Returns id = 3
3	Subquery result used	WHERE department_id = 3	Filter employees with department_id = 3
4	Fetch matching employees	SELECT name FROM employees WHERE department_id = 3	Returns names: 'Alice', 'Bob'
5	Query complete	Full query	Result set with 'Alice', 'Bob'

💡 Subquery executed first to provide value for outer query condition, then outer query completes.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
subquery_result	null	3	3	3
outer_query_condition	department_id = (subquery)	department_id = 3	department_id = 3	department_id = 3
result_set	empty	empty	['Alice', 'Bob']	['Alice', 'Bob']

Key Moments - 2 Insights

Why does the outer query wait for the subquery to finish before continuing?

Because the outer query needs the subquery's result to know which rows to select, as shown in execution_table step 2 and 3.

Can the subquery return multiple values here?

No, because the outer query uses '=' which expects a single value; if multiple values were returned, it would cause an error.

Visual Quiz - 3 Questions

Look at the execution_table, what is the subquery result at step 2?

A 3

B 'Sales'

C Alice

D department_id

💡 Hint

Check the 'Result/Value' column in step 2 of execution_table.

At which step does the outer query start filtering employees by department_id?

A Step 1

B Step 3

C Step 2

D Step 5

💡 Hint

Look for when the subquery result is used in the outer query condition in execution_table.

If the subquery returned multiple ids, what would happen to the outer query?

A It would ignore extra values

B It would work normally

C It would cause an error because '=' expects one value

D It would return all employees regardless of department

💡 Hint

Refer to key_moments about subquery returning multiple values and '=' operator.

Concept Snapshot

Subqueries nest inside outer queries.
The subquery runs first to provide a value.
Outer query uses this value to filter results.
Subquery must return one value if '=' is used.
This nesting allows complex filtering in one query.

Full Transcript

When a query contains a subquery, the database first runs the subquery to get its result. This result is then used by the outer query to filter or select rows. For example, to find employees in the 'Sales' department, the subquery finds the department id for 'Sales'. The outer query then selects employees with that department id. The outer query waits for the subquery to finish because it needs the subquery's value to continue. If the subquery returns multiple values but the outer query expects one, an error occurs. This nesting of queries allows building complex queries step-by-step.

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