Subqueries and CTEs: Nested Query Techniques

Introduction

Subqueries and Common Table Expressions (CTEs) allow breaking complex problems into manageable steps by nesting one query inside another.

They are essential tools in advanced SQL for any Database Management System, enabling queries that would otherwise be impossible or extremely hard to write in a single SELECT statement.

Explanation

Some SQL problems are too complex to solve in a single query this is where subqueries and CTEs help.A subquery is a SELECT statement embedded within another SQL statement. It can appear in the SELECT clause (returning a scalar value), the FROM clause (returning a derived table), or the WHERE clause (returning values for comparison). Subqueries in WHERE can use IN, NOT IN, EXISTS, NOT EXISTS, =, or comparison operators.

A correlated subquery references columns from the outer query — it executes once for every row in the outer query. This is powerful but can be slow on large datasets. A CTE (Common Table Expression) uses the WITH clause to define a named temporary result set that can be referenced multiple times in the main query.

Real World Example

Finding customers whose total purchase amount is above the average for their city requires comparing each customer's spend to a city-level average — a correlated subquery or CTE makes this readable and maintainable.

Technical Breakdown

When to use each technique:

• Scalar subquery in SELECT: When you need a single value per row from another table — e.g., each product's rank within its category.

• Subquery in WHERE with IN: When filtering rows based on values returned by another query. Use NOT IN to find records not present in the subquery result.

• EXISTS / NOT EXISTS: More efficient than IN for large datasets — stops scanning as soon as the first matching row is found.

• CTE (WITH clause): When the same subquery result is used multiple times, or when you want to break a complex query into readable named steps. CTEs also support recursion for hierarchical data.

Example

-- Subquery in WHERE: customers who ordered a specific product
SELECT name, email
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT o.customer_id
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE oi.product_id = 42
);

-- EXISTS (more efficient for large tables)
SELECT name, email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.customer_id = c.customer_id
      AND oi.product_id = 42
);

-- CTE for multi-step analysis
WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(total_amount)                AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
ranked_months AS (
    SELECT month, revenue,
           RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
    FROM monthly_sales
)
SELECT month, revenue, revenue_rank
FROM ranked_months
WHERE revenue_rank <= 3;

Common Mistakes

• Using IN with a subquery that can return NULL — if the subquery returns any NULL, NOT IN returns no rows (because x NOT IN (1, 2, NULL) evaluates to UNKNOWN). Use NOT EXISTS instead.

• Writing correlated subqueries that execute millions of times — always check if a JOIN can replace a correlated subquery for better performance.

• Forgetting to alias derived tables in FROM subqueries — MySQL requires every subquery in FROM to have an alias.

Interview Tips

CTEs are a favorite interview topic. Explain that CTEs improve readability by naming intermediate results, can be referenced multiple times (unlike subqueries), support recursion, and help the query optimizer in some databases. A good practice is to rewrite a complex nested subquery using a CTE — this demonstrates both clarity of thought and strong SQL skills.