Course contentsShow
← Data Science
Lesson 973 of 2,14525. SQL Subqueries and CTEsFree lesson

Nested Subqueries in FROM

Build layered queries by placing subqueries within subqueries in the FROM clause for step-by-step logic.

Nested Subqueries in FROM

You'll learn how to place subqueries inside other subqueries within the FROM clause to build complex, multi-layered queries that solve problems step-by-step.

The Core Idea

When one level of subquery isn't enough, you can nest them. Think of it like Russian nesting dolls: the innermost subquery runs first, its result becomes a table for the next level up, and so on. Each layer adds another transformation or aggregation step, letting you break down complicated logic into digestible chunks.

You've already used subqueries in FROM (derived tables). Now imagine that within one of those derived tables, the data comes from another subquery instead of a base table.

Why Nest Subqueries?

Sometimes you need to:

  1. Aggregate your data
  2. Then filter those aggregates
  3. Then aggregate again at a different level

Without nesting, this would be impossible in a single query.

Basic Pattern

SELECT outer_col
FROM (
    SELECT middle_col
    FROM (
        SELECT inner_col
        FROM base_table
        WHERE condition
    ) AS inner_derived
    GROUP BY middle_col
) AS outer_derived;

The innermost subquery runs first, creating a temporary result set. The middle subquery treats that as its source table. The outermost query works with the middle subquery's results.

Practical Example Structure

Imagine calculating the average of department totals:

  • Inner query: Sum sales by department
  • Outer query: Average those department sums

Each nesting level must have its own alias, and you reference columns progressively upward through the layers.

Key Takeaway: Nested subqueries in FROM let you build queries in logical stages—each layer processes the previous layer's output, enabling complex multi-step transformations that would otherwise require multiple separate queries or temporary tables.