Lesson 9: Subqueries

A subquery is a query nested inside another — used when a filter or value itself depends on the result of a separate query.

Real-time example

Find customers who've placed an order above the platform's average order value.

SELECT name FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
    WHERE amount > (SELECT AVG(amount) FROM orders)
);

What's happening

The innermost subquery runs first, computing one number (the average); the outer queries then use that number to filter.

Deeply nested subqueries hurt readability — a CTE (next lesson) often expresses the same logic more clearly.