Subqueries and nested queries in DBMS Theory - Deep Dive

Overview - Subqueries and nested queries

What is it?

Subqueries and nested queries are SQL queries written inside another SQL query. They allow you to use the result of one query as input for another. This helps break complex questions into smaller, manageable parts. Subqueries can appear in SELECT, FROM, WHERE, or HAVING clauses.

Why it matters

Without subqueries, writing complex database questions would be very difficult and messy. They let you ask layered questions, like 'find all customers who bought products cheaper than the average price.' This makes data retrieval more powerful and flexible, helping businesses and applications get precise answers quickly.

Where it fits

Before learning subqueries, you should understand basic SQL queries, including SELECT, WHERE, and JOIN operations. After mastering subqueries, you can explore advanced SQL topics like window functions, common table expressions (CTEs), and query optimization.

Mental Model

Core Idea

A subquery is a question inside another question, where the inner question’s answer helps solve the outer question.

Think of it like...

Imagine you want to find friends who live in cities where the average temperature is below 20°C. First, you ask 'What is the average temperature of each city?' Then, you use that answer to find your friends in those cooler cities. The first question is the subquery inside the bigger question.

Main Query
  ├─ WHERE condition uses
  │    └─ Subquery result
  └─ SELECT columns

Example:
SELECT name
FROM customers
WHERE city IN (
  SELECT city
  FROM weather
  WHERE avg_temp < 20
)

Build-Up - 7 Steps

1

Foundation

Understanding Basic SQL Queries

Concept: Learn how simple SQL queries retrieve data from one table using SELECT and WHERE.

A basic SQL query looks like this: SELECT column1, column2 FROM table_name WHERE condition; For example, to get all customers from 'New York': SELECT * FROM customers WHERE city = 'New York';

Result

Returns all rows from the customers table where the city is New York.

Knowing how to write simple queries is essential before adding complexity with subqueries.

2

Foundation

What is a Subquery?

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

A subquery is a complete query inside parentheses used within another query. Example: SELECT name FROM customers WHERE city = ( SELECT city FROM offices WHERE office_id = 1 ); Here, the inner query finds the city of office 1, and the outer query finds customers in that city.

Result

Returns customers who live in the same city as office 1.

Understanding that queries can be nested helps break down complex data questions.

3

Intermediate

Subqueries in WHERE Clause

🤔Before reading on: do you think a subquery in WHERE can return multiple values or only one? Commit to your answer.

Concept: Learn how subqueries can return single or multiple values to filter data.

Subqueries in WHERE can return one value (scalar) or multiple values (list). Example with multiple values: SELECT name FROM customers WHERE city IN ( SELECT city FROM offices ); This finds customers in any city where an office exists.

Result

Returns customers located in any city that has an office.

Knowing the difference between single and multiple value subqueries prevents errors and expands query power.

4

Intermediate

Subqueries in FROM Clause

🤔Before reading on: do you think subqueries in FROM act like temporary tables or just filters? Commit to your answer.

Concept: Subqueries in FROM create temporary tables that the outer query can use like normal tables.

Example: SELECT avg_price FROM ( SELECT product_id, AVG(price) AS avg_price FROM sales GROUP BY product_id ) AS avg_sales WHERE avg_price > 100; Here, the subquery calculates average prices per product, then the outer query filters those averages.

Result

Returns average prices of products where the average price is greater than 100.

Using subqueries in FROM lets you organize complex calculations as if they were tables.

5

Intermediate

Correlated Subqueries Explained

🤔Before reading on: do you think correlated subqueries run once or multiple times? Commit to your answer.

Concept: Correlated subqueries depend on the outer query and run repeatedly for each row processed.

Example: SELECT c1.name FROM customers c1 WHERE c1.balance > ( SELECT AVG(c2.balance) FROM customers c2 WHERE c2.city = c1.city ); The inner query calculates average balance for the city of each outer row's customer.

Result

Returns customers whose balance is above the average balance in their city.

Understanding correlated subqueries reveals how SQL can compare each row to a dynamic value.

6

Advanced

Performance Considerations with Subqueries

🤔Before reading on: do you think subqueries always run faster than joins? Commit to your answer.

Concept: Subqueries can be less efficient than joins; knowing when to use each affects speed and resource use.

Subqueries, especially correlated ones, may run many times, slowing queries. Example: SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'NY'); This can be rewritten as a JOIN for better performance: SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.city = 'NY';

Result

JOIN version often runs faster and uses fewer resources than subquery version.

Knowing performance trade-offs helps write efficient queries for large databases.

7

Expert

Nested Subqueries and Query Optimization

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

Concept: Deeply nested subqueries can be optimized by the database engine, but understanding how helps write better queries.

Databases transform nested subqueries into joins or temporary tables internally. Example: SELECT * FROM ( SELECT * FROM ( SELECT * FROM sales WHERE year = 2023 ) AS recent_sales WHERE amount > 1000 ) AS filtered_sales; Though nested, the engine may flatten this for speed. Knowing how your DB optimizes helps avoid unnecessary complexity.

Result

Well-structured nested queries can perform well if the database optimizes them properly.

Understanding internal optimization prevents overcomplicating queries and improves maintainability.

Under the Hood

When a query with a subquery runs, the database first executes the inner query to get its result. This result is then used by the outer query to filter or join data. For correlated subqueries, the inner query runs repeatedly for each row of the outer query. The database engine uses query planners to decide whether to execute subqueries as separate steps, convert them into joins, or use temporary tables to improve speed.

Why designed this way?

Subqueries were introduced to let users express complex questions naturally without manually breaking them into multiple queries. They provide modularity and clarity. Alternatives like joins existed but were sometimes less intuitive for layered questions. Over time, database engines improved to optimize subqueries internally, balancing ease of use with performance.

Outer Query
  ├─ Executes
  │    └─ Inner Query (Subquery)
  │         ├─ Returns result set
  │         └─ May run once or many times (correlated)
  └─ Uses subquery result to filter or join data

Execution Flow:
[Start] -> [Run Inner Query] -> [Get Result] -> [Run Outer Query using Result] -> [Final Output]

Myth Busters - 4 Common Misconceptions

Quick: Do subqueries always return only one value? Commit to yes or no.

Common Belief: Subqueries always return a single value because they are inside parentheses.

Reality: Subqueries can return multiple values, especially when used with IN or EXISTS clauses.

Why it matters: Assuming single value causes errors or wrong queries when multiple results are returned.

Quick: Do correlated subqueries run once or multiple times? Commit to your answer.

Common Belief: All subqueries run only once before the outer query.

Reality: Correlated subqueries run once for each row processed by the outer query, which can be costly.

Why it matters: Misunderstanding this leads to inefficient queries that slow down applications.

Quick: Is a subquery always slower than a join? Commit to yes or no.

Common Belief: Subqueries are always slower than joins and should be avoided.

Reality: Sometimes subqueries are optimized internally and run as fast or faster than joins, depending on the database and query.

Why it matters: Avoiding subqueries blindly can make queries more complex and less readable without performance gain.

Quick: Can subqueries appear anywhere in a SQL statement? Commit to yes or no.

Common Belief: Subqueries can be used anywhere in SQL statements without restrictions.

Reality: Subqueries have specific allowed places like SELECT, FROM, WHERE, and HAVING clauses, but not all positions support them.

Why it matters: Trying to use subqueries in unsupported places causes syntax errors and confusion.

Expert Zone

1

Some databases optimize uncorrelated subqueries by caching results, but correlated subqueries often cannot be cached, impacting performance.

2

Using EXISTS with correlated subqueries can be more efficient than IN when checking for existence because it stops searching after the first match.

3

Nested subqueries can sometimes be rewritten as lateral joins or apply operators in advanced SQL dialects for better performance and clarity.

When NOT to use

Avoid subqueries when simple joins can express the same logic more clearly and efficiently. For very large datasets, consider using common table expressions (CTEs) or window functions instead. Also, avoid deeply nested subqueries that reduce readability and complicate maintenance.

Production Patterns

In real systems, subqueries are often used to filter data based on aggregates, like 'customers with orders above average value.' They appear in reports, dashboards, and data validation rules. Experienced developers rewrite subqueries as joins or CTEs for performance and maintainability, especially in complex analytics queries.

Connections

Common Table Expressions (CTEs)

Builds-on

CTEs provide a named temporary result set similar to subqueries but improve readability and allow reuse within a query.

Functional Programming

Same pattern

Subqueries resemble nested function calls where the output of one function feeds into another, showing how layered problem solving works across domains.

Mathematical Set Theory

Builds-on

Subqueries often operate on sets of data, filtering or comparing subsets, reflecting set operations like union, intersection, and subset.

Common Pitfalls

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

Wrong approach:

SELECT name FROM customers WHERE city = (SELECT city FROM offices);

Correct approach:

SELECT name FROM customers WHERE city IN (SELECT city FROM offices);

Root cause:

Confusing single-value equality (=) with multi-value membership (IN) causes errors when subquery returns multiple rows.

#2 Writing correlated subqueries without realizing they run repeatedly, causing slow queries.

Wrong approach:

SELECT * FROM orders WHERE EXISTS (SELECT * FROM customers WHERE customers.id = orders.customer_id AND customers.status = 'active');

Correct approach:

Rewrite using JOIN:
SELECT orders.* FROM orders JOIN customers ON customers.id = orders.customer_id WHERE customers.status = 'active';

Root cause:

Not understanding execution cost of correlated subqueries leads to inefficient queries.

#3 Placing subqueries in unsupported SQL clauses causing syntax errors.

Wrong approach:

SELECT * FROM customers WHERE (SELECT city FROM offices) = 'New York';

Correct approach:

SELECT * FROM customers WHERE city = (SELECT city FROM offices WHERE office_id = 1);

Root cause:

Misplacing subqueries without proper filtering or context causes invalid SQL.

Key Takeaways

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

They can appear in different parts of a query, such as WHERE, FROM, or SELECT, each serving a unique purpose.

Correlated subqueries depend on the outer query and run multiple times, which can impact performance.

Understanding when to use subqueries versus joins or CTEs is key to writing efficient and readable SQL.

Database engines optimize subqueries internally, but knowing their mechanics helps avoid common mistakes and improve query speed.