Nested Subqueries — Queries Within Queries

---

title: “Nested Subqueries” part: 2 topic_number: 6 slug: “nested-subqueries” difficulty: “Intermediate” prerequisites: “select-from-where, aggregate-functions” —

Nested Subqueries — Queries Within Queries

What Is It?

A subquery (also called nested query or inner query) is a query inside another query. The inner query runs first, and its result is used by the outer query.

Real-world analogy: Like solving a math problem with parentheses — you solve what’s inside the parentheses first, then use that result in the larger calculation.

Why this feels hard: You have to read the query inside-out, but MySQL runs it inside-out too. Once you see the inner query as “just a value or list”, the outer query becomes obvious.

When you’d use it:

• Compare values to aggregates (e.g., “find employees earning above average”)
• Filter based on results from another table
• Create dynamic filters
• Build complex conditions

---

Syntax Breakdown

-- Subquery in WHERE clause
SELECT columns
FROM table
WHERE column operator (SELECT ... FROM ...);

-- Subquery in SELECT clause
SELECT 
    column,
    (SELECT ... FROM ... WHERE ...) AS calculated_column
FROM table;

-- Subquery in FROM clause (derived table)
SELECT ...
FROM (SELECT ... FROM ...) AS subquery_alias;

Key points:

• Inner query executes first
• Must return appropriate data type/structure for context
• Often enclosed in parentheses
• Can appear in SELECT, FROM, WHERE, HAVING

---

Basic Examples

Find Above-Average Earners

How to read this query — two steps, not one:

Step 1 — Inner query runs first:
  SELECT AVG(salary) FROM employees   →  returns: 72000

Step 2 — Outer query uses that result:
  SELECT ... WHERE salary > 72000

That’s it. The subquery is just a way to compute a number you’d otherwise have to hardcode.

SELECT 
    first_name,
    last_name,
    salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Expected output:

first_name	last_name	salary
Sam	Clark	95000.00
Frank	Miller	93000.00
Henry	Moore	92000.00
Paul	Garcia	87000.00
Bob	Smith	82000.00
…	…	…

How it works:

1. Inner query calculates AVG(salary) → ~72,000
2. Outer query finds employees with salary > 72,000

Subquery with IN

Find employees in departments located in New York:

SELECT 
    first_name,
    last_name,
    department_id
FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location = 'New York'
)
ORDER BY last_name;

Expected output:

first_name	last_name	department_id
Frank	Miller	2
Carol	Williams	3
George	Jones	7

Employees in NY-based departments.

---

Going Deeper

Subquery in SELECT (Scalar Subquery)

Show each employee’s salary compared to their department average:

SELECT 
    first_name,
    last_name,
    salary,
    department_id,
    (SELECT AVG(salary) 
     FROM employees e2 
     WHERE e2.department_id = e.department_id
    ) AS dept_avg_salary,
    salary - (SELECT AVG(salary) 
              FROM employees e2 
              WHERE e2.department_id = e.department_id
             ) AS diff_from_avg
FROM employees e
ORDER BY department_id, salary DESC;

Expected output:

first_name	last_name	salary	department_id	dept_avg_salary	diff_from_avg
Sam	Clark	95000.00	1	85666.67	9333.33
Paul	Garcia	87000.00	1	85666.67	1333.33
Alice	Johnson	75000.00	1	85666.67	-10666.67
Frank	Miller	93000.00	2	87500.00	5500.00
…	…	…	…	…	…

Each employee compared to their department’s average.

Subquery in FROM (Derived Table)

SELECT 
    dept_summary.department_id,
    dept_summary.employee_count,
    dept_summary.avg_salary,
    d.department_name
FROM (
    SELECT 
        department_id,
        COUNT(*) AS employee_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_summary
JOIN departments d ON dept_summary.department_id = d.department_id
WHERE dept_summary.employee_count >= 2
ORDER BY dept_summary.avg_salary DESC;

Expected output:

department_id	employee_count	avg_salary	department_name
2	2	87500.00	Marketing
1	3	85666.67	Engineering
4	2	79000.00	Finance
3	3	69666.67	Sales
…	…	…	…

Pre-aggregated data from subquery, then joined.

Multiple Subqueries

Find employees earning more than their department average AND more than company average:

SELECT 
    first_name,
    last_name,
    salary,
    department_id
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees)
  AND salary > (SELECT AVG(salary) 
                FROM employees e2 
                WHERE e2.department_id = e.department_id)
ORDER BY salary DESC;

Expected output:

first_name	last_name	salary	department_id
Sam	Clark	95000.00	1
Frank	Miller	93000.00	2
Henry	Moore	92000.00	4
Paul	Garcia	87000.00	1
Bob	Smith	82000.00	3

Top performers both overall and within their departments.

Pause and Predict: What happens if a subquery returns multiple rows when you use =?

Answer

**Error!** "Subquery returns more than 1 row" When using `=`, `>`, `<`, etc., the subquery must return exactly ONE value (scalar subquery). Use `IN`, `ANY`, or `ALL` for multi-row subqueries: - `WHERE col IN (subquery)` — matches any value - `WHERE col > ANY (subquery)` — greater than at least one value - `WHERE col > ALL (subquery)` — greater than every value

---

Watch Out — Common Mistakes

Mistake #1: Forgetting Subquery Returns NULL

--  CAN FAIL SILENTLY
SELECT *
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE employee_id = 9999);
-- Returns NO ROWS (subquery returns NULL, NULL comparisons are always false)

Fix: Handle NULL explicitly:

-- • SAFER
SELECT *
FROM employees
WHERE salary > COALESCE(
    (SELECT salary FROM employees WHERE employee_id = 9999),
    0  -- default if not found
);

Mistake #2: Performance Issues (N+1 Query Pattern)

--  SLOW (subquery runs for EVERY row)
SELECT 
    first_name,
    (SELECT COUNT(*) FROM employee_projects ep WHERE ep.employee_id = e.employee_id) AS project_count
FROM employees e;

Problem: Subquery executes once per employee (20 times for 20 employees).

Better (use JOIN):

-- • FASTER
SELECT 
    e.first_name,
    COUNT(ep.project_id) AS project_count
FROM employees e
LEFT JOIN employee_projects ep ON e.employee_id = ep.employee_id
GROUP BY e.employee_id, e.first_name;

Mistake #3: Using Subquery When EXISTS is Better

--  INEFFICIENT (returns all IDs, checks membership)
SELECT *
FROM employees e
WHERE e.employee_id IN (
    SELECT employee_id FROM employee_projects
);

Better (EXISTS short-circuits):

-- • FASTER
SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employee_projects ep WHERE ep.employee_id = e.employee_id
);

Why? EXISTS stops checking as soon as it finds one match. IN processes all results.

---

Edge Case Spotlight

ANY vs ALL

-- Find employees earning more than ANY sales employee
SELECT first_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE department_id = 3
)
ORDER BY salary DESC;
-- Returns: anyone earning more than the LOWEST sales salary

-- Find employees earning more than ALL sales employees
SELECT first_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department_id = 3
)
ORDER BY salary DESC;
-- Returns: only those earning more than the HIGHEST sales salary

Subquery Returning No Rows

SELECT *
FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments WHERE location = 'Mars'
);
-- Returns: empty result (no departments on Mars)

This is safe — IN with empty set returns no rows (doesn’t error).

---

Try This

Exercise 1 (Guided)

Find employees who earn more than the maximum salary in the Sales department (dept_id = 3). Show first_name, last_name, salary, and department_id. Sort by salary descending.

Hint

Use WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 3)

Exercise 2 (Independent)

Show each department’s name and the number of projects its employees are working on (total assignments, not unique projects). Use a subquery in the SELECT clause.

Hint

SELECT department_name, (SELECT COUNT(*) FROM employee_projects ep JOIN employees e ON ... WHERE e.department_id = d.department_id) AS project_count FROM departments d

Exercise 3 (Challenge)

Find employees who are assigned to MORE projects than the average number of projects per employee. Show first_name, last_name, and their project_count. Sort by project_count descending.

Hint: You’ll need:

1. Subquery to find average project count per employee
2. Main query to count projects per employee and filter

Hint

Use a derived table with employee project counts, then filter WHERE count > (SELECT AVG(count) FROM ...)

---

Answer Key

Exercise 1 Answer

```sql SELECT first_name, last_name, salary, department_id FROM employees WHERE salary > ( SELECT MAX(salary) FROM employees WHERE department_id = 3 ) ORDER BY salary DESC; ``` **Expected output:** | first_name | last_name | salary | department_id | |------------|-----------|---------|---------------| | Sam | Clark | 95000.00 | 1 | | Frank | Miller | 93000.00 | 2 | | Henry | Moore | 92000.00 | 4 | | Paul | Garcia | 87000.00 | 1 | | Bob | Smith | 82000.00 | 3 | Employees earning more than the top sales employee.

Exercise 2 Answer

```sql SELECT d.department_name, (SELECT COUNT(*) FROM employee_projects ep JOIN employees e ON ep.employee_id = e.employee_id WHERE e.department_id = d.department_id ) AS total_project_assignments FROM departments d ORDER BY total_project_assignments DESC; ``` **Expected output:** | department_name | total_project_assignments | |-----------------|---------------------------| | Engineering | 7 | | Finance | 5 | | Human Resources | 4 | | Sales | 3 | | Marketing | 0 | | ... | ... | Project assignments per department via subquery.

Exercise 3 Answer

```sql SELECT e.first_name, e.last_name, emp_projects.project_count FROM employees e JOIN ( SELECT employee_id, COUNT(*) AS project_count FROM employee_projects GROUP BY employee_id ) AS emp_projects ON e.employee_id = emp_projects.employee_id WHERE emp_projects.project_count > ( SELECT AVG(project_count) FROM ( SELECT COUNT(*) AS project_count FROM employee_projects GROUP BY employee_id ) AS avg_calc ) ORDER BY emp_projects.project_count DESC; ``` **Expected output:** | first_name | last_name | project_count | |------------|-----------|---------------| | Alice | Johnson | 4 | | Carol | Williams | 3 | | Eve | Davis | 3 | Employees with above-average project loads. **How it works:** 1. Inner-most subquery calculates project count per employee 2. Middle subquery calculates average of those counts 3. Main query finds employees exceeding that average

---

---

Subquery vs JOIN — When to Use Which

This is the most common question about subqueries:

Situation	Use
Compare a row to an aggregate (avg, max, min)	Subquery — e.g., WHERE salary > (SELECT AVG...)
Filter by a list from another table	Either — IN (subquery) or JOIN + WHERE
You need columns from both tables in the result	JOIN — subqueries in WHERE can’t return extra columns
“Does a related row exist?” check	EXISTS subquery — cleaner than JOIN for existence
You want to pre-aggregate then filter	Subquery in FROM (derived table)

Rule of thumb: If you’re pulling data FROM the related table into your SELECT list → use JOIN. If you’re just FILTERING based on the related table → either works, but subquery is often more readable.

---

Quick Recap

• Subqueries are queries inside other queries
• Can appear in SELECT, FROM, WHERE, HAVING clauses
• Inner query executes first, result used by outer query
• Use IN for multi-row results, = for single value
• ANY matches at least one, ALL matches every value
• EXISTS often faster than IN for existence checks
• Be careful of performance with correlated subqueries (next topic!)
• Consider JOINs as alternative for better performance

---

Up Next

Next topic: Correlated Subqueries → part2_07_correlated_subqueries.md

Type ‘next’ when ready to continue!