Why subqueries nest queries in MySQL - Why It Works This Way

Overview - Why subqueries nest queries

What is it?

A subquery is a query written inside another query. It lets you use the result of one query as input for another. This nesting helps break complex questions into smaller parts that the database can solve step-by-step. It is like asking a question inside another question.

Why it matters

Without subqueries, writing complex database questions would be much harder and messier. You would have to write many separate queries and combine their results manually. Subqueries make it easier to get precise answers from data by letting one query depend on another. This saves time and reduces mistakes.

Where it fits

Before learning subqueries, you should understand basic SQL SELECT statements and simple filtering with WHERE. After mastering subqueries, you can learn about JOINs, advanced filtering, and query optimization to write even more powerful database queries.

Mental Model

Core Idea

A subquery is a smaller question inside a bigger question, letting the bigger question use the smaller one's answer.

Think of it like...

Imagine you want to find the tallest person in a group, but only among those who scored above 80 on a test. First, you ask 'Who scored above 80?' Then, inside that answer, you ask 'Who is the tallest?' The second question depends on the first, just like a subquery inside a query.

Main Query
  ├─ Subquery (nested inside)
  │    └─ Returns data used by main query
  └─ Uses subquery result to filter or calculate

Example:
SELECT * FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Build-Up - 6 Steps

1

Foundation

Understanding Basic SQL Queries

Concept: Learn how to write simple SELECT queries to get data from one table.

A basic SQL query looks like this: SELECT column1, column2 FROM table_name WHERE condition; This gets data from a table filtered by a condition.

Result

You get rows from the table that match the condition.

Knowing how to write simple queries is essential before nesting queries inside each other.

2

Foundation

What Is a Subquery?

Concept: Introduce the idea of a query inside another query to use its result.

A subquery is a SELECT statement inside parentheses used in another query. For example: SELECT * FROM Employees WHERE DepartmentID = (SELECT ID FROM Departments WHERE Name = 'Sales'); Here, the inner query finds the ID of 'Sales' department, and the outer query finds employees in that department.

Result

The outer query uses the inner query's result to filter employees.

Understanding that queries can be nested helps solve complex data questions step-by-step.

3

Intermediate

Types of Subqueries and Their Uses

🤔Before reading on: Do you think subqueries can return multiple rows or just one? Commit to your answer.

Concept: Learn that subqueries can return single values, multiple rows, or even be used in different parts of a query.

Subqueries can be: - Scalar: returns one value, used in WHERE or SELECT. - Row: returns one row with multiple columns. - Table: returns multiple rows and columns, used with IN or EXISTS. Example: SELECT Name FROM Employees WHERE DepartmentID IN (SELECT ID FROM Departments WHERE Location = 'NY');

Result

The query finds employees in departments located in NY using a subquery that returns multiple IDs.

Knowing subquery types helps you choose the right one for your data question.

4

Intermediate

How Subqueries Nest Queries

🤔Before reading on: Do you think the inner query runs once or multiple times when nested? Commit to your answer.

Concept: Understand that the inner query runs first and its result is used by the outer query, creating a nesting effect.

When a query contains a subquery, the database first runs the subquery to get its result. Then, the outer query uses that result to complete its filtering or calculation. This nesting allows complex filtering based on dynamic data. Example: SELECT * FROM Orders WHERE CustomerID IN (SELECT ID FROM Customers WHERE Country = 'USA');

Result

The database finds all customers in the USA first, then finds orders for those customers.

Understanding the execution order clarifies why subqueries are called nested queries.

5

Advanced

Correlated vs Non-Correlated Subqueries

🤔Before reading on: Do you think all subqueries run independently or can they depend on the outer query? Commit to your answer.

Concept: Learn the difference between subqueries that run once and those that run for each row of the outer query.

Non-correlated subqueries run once and return a result used by the outer query. Correlated subqueries depend on the outer query's current row and run repeatedly. Example of correlated: SELECT e.Name FROM Employees e WHERE e.Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID); Here, the inner query uses e.DepartmentID from the outer query.

Result

The query finds employees earning more than the average salary in their own department.

Knowing this difference helps optimize queries and understand performance impacts.

6

Expert

Performance Implications of Nested Subqueries

🤔Before reading on: Do you think nested subqueries always slow down queries? Commit to your answer.

Concept: Explore how subqueries affect query speed and how databases optimize them.

Nested subqueries can slow queries if they run many times (like correlated subqueries). However, modern databases optimize some subqueries by rewriting them or caching results. Sometimes rewriting subqueries as JOINs improves performance. Example: Replacing correlated subquery with JOIN: SELECT e.Name FROM Employees e JOIN (SELECT DepartmentID, AVG(Salary) AS AvgSal FROM Employees GROUP BY DepartmentID) d ON e.DepartmentID = d.DepartmentID WHERE e.Salary > d.AvgSal;

Result

The rewritten query often runs faster by avoiding repeated subquery execution.

Understanding how subqueries impact performance guides writing efficient queries in real systems.

Under the Hood

When a query with a subquery runs, the database engine first executes the inner query to produce a temporary result set. This result is then passed to the outer query as a filter or value. For correlated subqueries, the inner query runs repeatedly for each row of the outer query, using values from that row. The database uses query planners and optimizers to decide the best way to execute these nested queries efficiently.

Why designed this way?

Subqueries were designed to let users express complex data questions naturally, breaking them into smaller parts. This approach avoids writing multiple separate queries and manual data combination. Alternatives like JOINs existed but sometimes subqueries are clearer or more intuitive. The nesting design balances expressiveness and simplicity, though it can impact performance if not used carefully.

┌─────────────────────────────┐
│ Outer Query                 │
│ ┌─────────────────────────┐ │
│ │ Subquery (Inner Query)  │ │
│ │ Executes first          │ │
│ │ Returns result set      │ │
│ └─────────────────────────┘ │
│ Uses subquery result to     │
│ filter or calculate         │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think subqueries always run once regardless of type? Commit yes or no.

Common Belief: Subqueries always run only once before the outer query.

Reality: Correlated subqueries run once per row of the outer query, which can be many times.

Why it matters: Assuming subqueries run once can lead to unexpected slow queries and performance problems.

Quick: Do you think subqueries and JOINs always produce the same results? Commit yes or no.

Common Belief: Subqueries and JOINs are interchangeable and always give the same output.

Reality: Subqueries and JOINs can produce different results or performance depending on how they are written and used.

Why it matters: Misusing subqueries instead of JOINs can cause wrong data or inefficient queries.

Quick: Do you think subqueries can only be used in WHERE clauses? Commit yes or no.

Common Belief: Subqueries are only allowed in WHERE conditions.

Reality: Subqueries can be used in SELECT, FROM, HAVING, and other clauses, not just WHERE.

Why it matters: Limiting subqueries to WHERE clauses restricts query design and misses powerful patterns.

Quick: Do you think subqueries always make queries slower? Commit yes or no.

Common Belief: Using subqueries always slows down query execution.

Reality: Some subqueries are optimized by the database and run efficiently; others may slow down queries if correlated or large.

Why it matters: Avoiding subqueries blindly can lead to more complex or less readable queries.

Expert Zone

1

Some databases optimize non-correlated subqueries by flattening them into JOINs internally, improving speed without changing query logic.

2

Correlated subqueries can sometimes be rewritten as APPLY operators or lateral joins for better performance in advanced SQL engines.

3

Subqueries in SELECT clauses can act like computed columns, but excessive use can cause repeated calculations and slowdowns.

When NOT to use

Avoid subqueries when performance is critical and the subquery runs per row (correlated). Instead, use JOINs or temporary tables. Also, for very large datasets, consider indexing or query refactoring to reduce nested query overhead.

Production Patterns

In real systems, subqueries are often used for filtering with IN or EXISTS, calculating aggregates per group, or simplifying complex filters. Experts combine subqueries with JOINs and window functions to write clear, maintainable, and efficient queries.

Connections

Functional Programming

Subqueries are like nested function calls where one function's output feeds another.

Understanding nested queries as nested functions helps grasp how data flows and dependencies work in SQL.

Mathematical Set Theory

Subqueries often represent subsets or conditions on sets, similar to set operations.

Seeing subqueries as set filters clarifies why IN and EXISTS behave the way they do.

Russian Nesting Dolls (Matryoshka)

Both involve objects nested inside similar objects, each layer depending on the inner one.

Recognizing this pattern helps appreciate the layered structure and dependency in nested queries.

Common Pitfalls

#1 Using a subquery that returns multiple rows where only one value is expected.

Wrong approach:

SELECT * FROM Employees WHERE DepartmentID = (SELECT ID FROM Departments WHERE Location = 'NY');

Correct approach:

SELECT * FROM Employees WHERE DepartmentID IN (SELECT ID FROM Departments WHERE Location = 'NY');

Root cause:

Confusing '=' which expects one value with 'IN' which accepts multiple values.

#2 Writing a correlated subquery without realizing it runs repeatedly, causing slow queries.

Wrong approach:

SELECT e.Name FROM Employees e WHERE e.Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID);

Correct approach:

SELECT e.Name FROM Employees e JOIN (SELECT DepartmentID, AVG(Salary) AS AvgSal FROM Employees GROUP BY DepartmentID) d ON e.DepartmentID = d.DepartmentID WHERE e.Salary > d.AvgSal;

Root cause:

Not recognizing that the subquery depends on outer query values and runs per row.

#3 Placing a subquery in a clause where it is not allowed or makes no sense.

Wrong approach:

SELECT (SELECT Name FROM Departments) FROM Employees;

Correct approach:

SELECT Name FROM Departments WHERE ID = (SELECT DepartmentID FROM Employees WHERE EmployeeID = 1);

Root cause:

Misunderstanding where subqueries can be used and how their results relate to the outer query.

Key Takeaways

Subqueries let you nest one query inside another to solve complex data questions step-by-step.

They run in a specific order: the inner query executes first, and its result guides the outer query.

There are different types of subqueries, including correlated ones that run repeatedly and non-correlated ones that run once.

Using subqueries wisely improves query clarity but can impact performance if not optimized.

Understanding subqueries deeply helps write efficient, readable, and powerful database queries.

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