SQL Subqueries
A SQL subquery is a query nested inside another query, whose result the outer query uses as a filter condition, a computed column or a virtual table. Common forms are a subquery in a WHERE clause, a subquery in a FROM clause, and a correlated subquery, which references the outer query and effectively runs once per outer row.
A subquery is a SQL query nested inside another SQL query. The inner query runs (conceptually) first, producing a result that the outer query then uses, as a filter condition, a computed column, or a virtual table to select from.
The three common shapes
- Subquery in a WHERE clause: filters the outer query based on a value or set of values computed by the inner query.
- Subquery in a FROM clause: treats the inner query's result as if it were a table, letting the outer query select from or join against it.
- Correlated subquery: an inner query that references a column from the outer query, so it effectively re-runs once per outer row rather than once overall.
A worked example: subquery in WHERE
Find every product priced above the average price of all products:
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
The inner query SELECT AVG(price) FROM products computes one number, the average price, and the outer query filters against that single value.
A worked example: correlated subquery
Find every employee who earns more than the average salary in their own department (not the company-wide average):
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
This inner query references e.department_id from the outer query, so it's correlated, it effectively re-computes a different average for each department as the outer query moves through each employee row.
Where this matters in real systems
Subqueries are extremely common in real reporting queries: finding outliers relative to a group average, filtering to only rows that do or don't exist in a related table, or building an intermediate aggregated result to join against. It's also worth knowing that many subqueries can be rewritten as an equivalent JOIN, and for correlated subqueries specifically, database query planners sometimes optimize a JOIN-based version better than the subquery version, so both forms are worth being comfortable with.
What interviewers actually ask about SQL subqueries
- "What is a subquery?" A query nested inside another query, whose result the outer query uses as a filter, computed value, or virtual table.
- "What's a correlated subquery?" A subquery that references a column from the outer query, effectively re-running once per outer row instead of once overall.
- "Can every subquery be rewritten as a JOIN?" Many can, and it's worth knowing both forms, since a query planner sometimes handles one better than the other for a given case.
- "Give an example of when you'd need a correlated subquery instead of a simple one." Comparing each row against a group-specific value, like comparing an employee's salary against their own department's average rather than the company-wide average.
Key takeaways
- A subquery is a query nested inside another, whose result the outer query filters on, selects from, or joins against.
- A correlated subquery references a column from the outer query and effectively re-runs per outer row.
- Subqueries are common for comparing rows against a group aggregate, or filtering based on related-table existence.
- Many subqueries have an equivalent JOIN form, and it's worth being comfortable writing both.
More in SQL
Browse all DBMS topics
Log in to save which topics you've read across every subject.
Frequently asked questions
What is a subquery?
A query nested inside another query, whose result the outer query uses as a filter, computed value, or virtual table.
What's a correlated subquery?
A subquery that references a column from the outer query, effectively re-running once per outer row instead of once overall.
Can every subquery be rewritten as a JOIN?
Many can, and it's worth knowing both forms, since a query planner sometimes handles one better than the other for a given case.
Give an example of when you'd need a correlated subquery instead of a simple one.
Comparing each row against a group-specific value, like comparing an employee's salary against their own department's average rather than the company-wide average.