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.

Practice

1. What is a subquery in a database query?

easy

A. A query nested inside another query

B. A query that updates data

C. A query that deletes tables

D. A query that creates indexes

Solution

1. Step 1: Understand the definition of subquery

   A subquery is a query written inside another query to help filter or calculate data.

2. Step 2: Identify the correct description

   Among the options, only "A query nested inside another query" matches the definition of a subquery.

3. Final Answer:

   A query nested inside another query -> Option A

4. Quick Check:

   Subquery = Nested query [OK]

Hint: Subquery means query inside another query [OK]

Common Mistakes:

• Confusing subquery with update or delete operations
• Thinking subquery creates indexes
• Mixing subquery with table creation

2. Which of the following is the correct syntax to use a subquery in the WHERE clause?

easy

A. SELECT * FROM table WHERE column = (SELECT value FROM other_table);

B. SELECT * FROM table WHERE SELECT value FROM other_table = column;

C. SELECT * FROM table WHERE column IN SELECT value FROM other_table;

D. SELECT * FROM table WHERE column = SELECT value FROM other_table;

Solution

1. Step 1: Review correct subquery syntax in WHERE clause

   The subquery must be enclosed in parentheses and used as a value to compare with a column.

2. Step 2: Check each option

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); correctly uses parentheses around the subquery. Options A, B, and D have syntax errors or missing parentheses.

3. Final Answer:

   SELECT * FROM table WHERE column = (SELECT value FROM other_table); -> Option A

4. Quick Check:

   Subquery in WHERE uses parentheses [OK]

Hint: Subquery in WHERE must be inside parentheses [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Placing subquery before column
• Using IN without parentheses

3. Consider the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What does this query return?

SELECT name FROM Employees WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who do not work in Sales

B. Names of all employees regardless of department

C. Names of employees who work in the Sales department

D. Department names of all employees

Solution

1. Step 1: Understand the subquery

   The subquery finds the dept_id for the department named 'Sales'.

2. Step 2: Apply the subquery result in the main query

   The main query selects employee names whose dept_id matches the Sales dept_id.

3. Final Answer:

   Names of employees who work in the Sales department -> Option C

4. Quick Check:

   Subquery filters Sales dept_id, main query filters employees [OK]

Hint: Subquery finds Sales dept_id, main query filters employees [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing employee names with department names
• Ignoring subquery filtering

4. Identify the error in this query:

SELECT name FROM Employees WHERE dept_id = SELECT dept_id FROM Departments WHERE dept_name = 'HR';

medium

A. Wrong table name used

B. Missing parentheses around the subquery

C. Using = instead of IN

D. Missing WHERE clause in subquery

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses when used in WHERE clause comparisons.

2. Step 2: Identify the error in the query

   The query misses parentheses around the subquery, causing a syntax error.

3. Final Answer:

   Missing parentheses around the subquery -> Option B

4. Quick Check:

   Subquery needs parentheses [OK]

Hint: Always put subqueries in parentheses [OK]

Common Mistakes:

• Omitting parentheses
• Confusing = with IN unnecessarily
• Incorrect table names

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 emp_name FROM Employees WHERE salary > (SELECT MAX(salary) FROM Employees);

B. SELECT emp_name FROM Employees WHERE salary > AVG(salary);

C. SELECT emp_name FROM Employees WHERE salary > (SELECT salary FROM Employees);

D. SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id);

Solution

1. Step 1: Understand the requirement

   We need to compare each employee's salary to the average salary of their own department.

2. Step 2: Analyze each option

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); uses a correlated subquery that calculates the average salary for the employee's department. Options A, B, and C do not correctly calculate the average per department or use wrong aggregates.

3. Final Answer:

   SELECT emp_name FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = Employees.dept_id); -> Option D

4. Quick Check:

   Correlated subquery compares salary to dept average [OK]

Hint: Use correlated subquery with dept_id to get average salary [OK]

Common Mistakes:

• Using global average instead of per department
• Using MAX instead of AVG
• Not correlating subquery with outer query