Subqueries

A subquery (or nested query) is a SQL query placed inside another query - used to compute an intermediate result that the outer query then filters, compares against, or otherwise builds on.

Subquery in a WHERE Clause

-- Customers who have placed at least one order above 5000
SELECT * FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders WHERE amount > 5000
);

Subquery in a SELECT Clause

-- Show each customer alongside their total number of orders
SELECT first_name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

Subquery in a FROM Clause

-- Treat a subquery's result as a temporary table
SELECT city, AVG(total_spent)
FROM (
    SELECT c.city, SUM(o.amount) AS total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.city
) AS customer_totals
GROUP BY city;

Correlated vs Non-Correlated Subqueries

A non-correlated subquery runs independently of the outer query and executes once. A correlated subquery references a column from the outer query and re-runs for every row the outer query processes - more flexible, but often slower.