A subquery is a query nested inside another query — used in WHERE, FROM, SELECT, or with EXISTS. It lets you use the result of one query within another, enabling complex filtering and computations.
A subquery is a query nested inside another query — used in WHERE, FROM, SELECT, or with EXISTS. It lets you use the result of one query within another, enabling complex filtering and computations.
-- find users who have placed an order (their id is in the orders table)
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders); -- the subquery returns a list of user_ids
-- find products more expensive than the average
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products); -- subquery returns a single value
A subquery in WHERE provides values to filter against — a list (with IN) or a single value (with comparison operators).
-- for each user, find those with above-average orders FOR THEIR COUNTRY
SELECT name FROM users u
WHERE order_count > (
SELECT AVG(order_count) FROM users
WHERE country = u.country -- references the OUTER query's row (correlated)
);
A correlated subquery references the outer query — it runs once per outer row (potentially slower). An uncorrelated subquery runs once.
-- users who have at least one order (often more efficient than IN)
SELECT name FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id -- just checks existence
);
EXISTS checks whether a subquery returns any rows — often more efficient than IN for existence checks (it can stop at the first match).
-- use a subquery result as a table
SELECT country, avg_age FROM (
SELECT country, AVG(age) AS avg_age FROM users GROUP BY country
) AS country_stats
WHERE avg_age > 30;
Many subqueries can be rewritten as JOINs (often more efficient/readable).
WHERE id IN (SELECT ...) → can often be a JOIN
Use subqueries for clarity in filtering/aggregation; JOINs for combining data.
CTEs (WITH) are often a cleaner alternative to complex nested subqueries.
Subqueries are an important SQL technique for complex querying — using the result of one query within another enables filtering and computations that simple queries can't express, so understanding them is valuable for writing real-world queries.
Knowing the forms — subqueries in WHERE (filtering by a list with IN or a single value with comparisons), correlated subqueries (referencing the outer query, running per row), EXISTS (efficient existence checks), and subqueries in FROM (derived tables) — covers the common patterns for expressing complex logic.
Understanding the distinction between correlated and uncorrelated subqueries is important for performance (correlated subqueries run once per outer row and can be slow).
Equally valuable is knowing the relationship to JOINs and CTEs: many subqueries can be rewritten as JOINs (often more efficient and readable) or CTEs (cleaner for complex cases), so understanding when to use each — subqueries for filtering/aggregation clarity, JOINs for combining data — is a practical skill.
Since real-world queries often require nesting (filtering by aggregated results, existence checks, comparing to computed values), and since understanding subqueries, their performance characteristics (especially correlated ones), and their relationship to JOINs/CTEs is important for writing effective, efficient complex queries, mastering subqueries is valuable, frequently-applied knowledge for SQL beyond basic querying and a common interview topic that demonstrates ability to express complex data requirements.