Nested Queries

Introduction

Beyond simple subqueries, nested queries can be many levels deep — answering complex business questions in one statement. Modern SQL prefers CTEs for the same job, but nested queries remain powerful.

Understanding the topic

Core concepts to understand:

• Multi-level subqueries can answer 'top N per group' style questions.
• Often rewritable to CTEs or window functions for clarity.
• Optimizer may collapse them into joins.
• Watch out for repeated identical subqueries — extract to CTE.
• Format with indentation for readability.

Syntax reference

Visual workflow / architecture:

SELECT *
FROM (
  SELECT user_id, sum(amount) AS spend
  FROM (
    SELECT * FROM orders WHERE status = 'paid'
  ) paid
  GROUP BY user_id
) totals
WHERE spend > 1000;

Real-world use

Reports computing 'paid customers above $X this month with at least Y orders' often start as nested queries before being refactored to CTEs.

Best practices

• Refactor deeply nested queries to CTEs for readability.
• Use window functions for top-N-per-group.
• EXPLAIN to confirm the optimizer collapses or not.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. When to refactor a nested query to a CTE?
• Q2. Rewrite a nested query using window functions.
• Q3. Do nested queries always perform worse?
• Q4. Example of a 3-level nested query.
• Q5. Tools to explain nested plans.