A subquery is a SELECT nested inside another SQL statement. A non-correlated subquery runs once, independent of the outer query, and its result is reused; a correlated subquery references a column from the outer query and is conceptually re-evaluated for every row the outer query processes, which can be far slower on a large table if the query planner can't optimize it away.
Subquery in WHERE: non-correlated
The inner query runs once, independently, and its result feeds directly into the outer WHERE clause.
SELECT name FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- the inner query runs ONCE, computing a single averageCorrelated subqueries: re-evaluated per outer row
Because the inner query references orders.customer_id from the outer table, it's conceptually re-run once per row of the outer query — correct, but potentially slow on large tables without good indexing.
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id AND o.total > 1000
);
-- for each customer row, checks: does a matching order over $1000 exist?Subquery in FROM: a derived table
A subquery in FROM acts as a temporary, unnamed table for the rest of the query — useful for pre-aggregating data before joining or filtering it further. Most engines require an alias for it.
SELECT category, avg_price
FROM (
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
) AS category_averages
WHERE avg_price > 100;