Using Subqueries to Solve Queries: Nested SELECTs Explained

A subquery is a SELECT statement nested inside another SELECT. It runs first, produces a result, and the outer query uses that result. It cannot stand on its own — it exists to feed the outer query.

SELECT customer_name
FROM   customers
WHERE  credit_limit > (SELECT AVG(credit_limit) FROM customers);

The subquery computes the average credit limit across all customers. That single number feeds the outer query, which filters customers whose own credit limit exceeds it. On our data, the average of the six non-NULL limits is 54,166.67 — so customers above that threshold come back.

The difference from a simple query is that a simple query answers one question end to end. A subquery answers a smaller question that the outer query depends on. The two combine to answer the larger question.

A scalar subquery in the SELECT list returns one value for each row of the outer query. It usually references the outer row, which makes it correlated.

SELECT c.customer_name,
       (SELECT COUNT(*)
        FROM   orders o
        WHERE  o.customer_id = c.customer_id) AS order_count
FROM   customers c;

Each customer comes back with their order count beside them. The subquery is correlated — the reference to c.customer_id ties it to the current row. For each of the seven customers, the subquery runs once and returns a number.

On our data: Alice Corp 3, Bob Traders 2, Charlie Ltd 1, Diana Retail 1, and 0 for the three customers with no orders.

This pattern is clean to read and useful for small tables. On large tables, the subquery runs once per row, which can be very slow. Rewriting as a LEFT JOIN against a grouped subquery is the standard fix and produces the same output.

A subquery in the WHERE clause produces a value or list the outer query filters against. The operator you choose depends on what the subquery returns.

-- Scalar return: use =, >, <, etc.
SELECT customer_name
FROM   customers
WHERE  credit_limit = (SELECT MAX(credit_limit) FROM customers);

-- Multi-row return: use IN or EXISTS
SELECT customer_name
FROM   customers
WHERE  customer_id IN (SELECT customer_id FROM orders WHERE order_amount > 10000);

The first subquery returns a single value — the highest credit limit, 80,000 — and the outer query finds the customer who has it. The second returns a list of customer ids, and the outer query matches against that list.

The most common mistake is using = against a subquery that returns multiple rows. The database stops with "single-row subquery returns more than one row", which is the fastest reminder to switch to IN.

A subquery in the FROM clause produces a derived table — an intermediate result the outer query treats like a real table. It must have an alias.

SELECT c.customer_name, t.total_spend
FROM   customers c
JOIN  (
    SELECT customer_id, SUM(order_amount) AS total_spend
    FROM   orders
    GROUP BY customer_id
) t ON c.customer_id = t.customer_id
ORDER BY t.total_spend DESC;

The inner query aggregates orders by customer. The outer query joins that result to the customers table. Every customer appears once with their total spend next to them, sorted highest to lowest.

This is the standard way to combine an aggregated result with detail data. Grouping orders by customer_id in a single-level query would collapse the customer name along with everything else. The derived table keeps the aggregation at one level and the join at another.

On our data, Alice Corp comes back first with 28,500. Bob Traders second with 7,700. Charlie Ltd third with 22,000 — wait, that ordering looks off. Let me re-check. Alice 28,500, Charlie 22,000, Diana 18,000, Bob 7,700. So the sorted output is Alice Corp, Charlie Ltd, Diana Retail, Bob Traders.

A subquery in the HAVING clause filters groups after aggregation. The subquery produces a threshold or list, and the outer query keeps only the groups that clear it.

SELECT customer_id, SUM(order_amount) AS total_spend
FROM   orders
GROUP BY customer_id
HAVING SUM(order_amount) > (
    SELECT AVG(order_amount) * 2 FROM orders
);

The subquery computes twice the average order amount. The outer query keeps only the customers whose total spend exceeds that threshold.

On our data: the average order amount is about 10,885. Doubling gives 21,771. Only Alice Corp's total of 28,500 clears that bar. She is the only row in the result.

HAVING combined with a subquery is how you filter groups against a value computed by another query. Same value every time the query runs, but computed dynamically from the current data — which is what makes it useful in reports that need to adjust to their own data.

A scalar subquery returns exactly one row and one column. It is the shape you use wherever a single value is expected.

SELECT customer_name
FROM   customers
WHERE  credit_limit = (SELECT MAX(credit_limit) FROM customers);

That returns George Wholesale — the only customer with the highest credit limit, 80,000. The subquery produces one number; the outer query compares each row against it.

Scalar subqueries can also sit in the SELECT list:

SELECT customer_name,
       credit_limit,
       (SELECT AVG(credit_limit) FROM customers) AS company_avg
FROM   customers;

Every row gets the same company_avg value alongside its own credit limit. Useful for quickly seeing which rows are above and below the average.

If the subquery ends up returning more than one row, the query fails with an error. The fix is either to make the subquery genuinely scalar (add an aggregate function) or to switch the comparison to IN.

Use a subquery that returns multiple rows and combine it with an operator that understands lists: IN, EXISTS, ANY, or ALL.

SELECT customer_name
FROM   customers
WHERE  customer_id IN (
    SELECT customer_id FROM orders WHERE order_amount > 10000
);

The subquery returns the ids of customers who have placed an order above 10,000. The outer query keeps only those customers.

On our data, that is Alice Corp (12000), Charlie Ltd (22000), and Diana Retail (18000). Three rows.

The same pattern works with EXISTS, which is often faster:

SELECT c.customer_name
FROM   customers c
WHERE  EXISTS (
    SELECT 1 FROM orders o
    WHERE  o.customer_id = c.customer_id AND o.order_amount > 10000
);

Same output, but EXISTS can stop as soon as it finds one matching order for a customer. On large tables, that short-circuit is what makes it the faster choice.

When a subquery returns multiple rows, the outer query needs an operator that accepts a list. The four options are IN, EXISTS, ANY, and ALL.

-- IN: match any value in the list
SELECT customer_name FROM customers
WHERE  customer_id IN (SELECT customer_id FROM orders WHERE order_amount > 10000);

-- EXISTS: check whether a match exists
SELECT c.customer_name FROM customers c
WHERE  EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- ANY: compare against any value
SELECT customer_name FROM customers
WHERE  credit_limit > ANY (SELECT order_amount FROM orders WHERE customer_id = 'C001');

-- ALL: compare against every value
SELECT customer_name FROM customers
WHERE  credit_limit > ALL (SELECT order_amount FROM orders WHERE customer_id = 'C001');

IN returns rows where the value matches one of the subquery's results. EXISTS returns rows where the subquery finds at least one match. ANY returns rows where the comparison holds for at least one subquery value. ALL returns rows where the comparison holds for every subquery value.

IN is the most common but has a NULL trap. If the subquery returns a NULL, comparisons against it become unknown, and the outer query may return nothing. EXISTS does not have this problem because it only checks row presence.

A correlated subquery references a column from the outer query, which is what links the two. It cannot run standalone, and the database evaluates it once per row of the outer query.

SELECT c.customer_name, c.credit_limit
FROM   customers c
WHERE  c.credit_limit > (
    SELECT AVG(o.order_amount)
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
);

The inner query filters by o.customer_id = c.customer_id — that is the correlation. For each customer, the inner query runs and computes their average order value. The outer query compares the customer's credit limit against that average.

On our data, customers with orders come back if their credit limit exceeds their own average order: Alice Corp (50,000 > 9,500), Bob Traders (25,000 > 3,850), Charlie Ltd (75,000 > 22,000), Diana Retail (40,000 > 18,000). Customers with no orders are excluded because their subquery returns NULL, and NULL fails the comparison.

Correlated subqueries are powerful for per-row comparisons but can be slow. Each execution reads from the orders table. On a million-row customers table, that is a million subquery executions. When performance matters, the fix is a join against a grouped subquery in the FROM clause.

IN checks whether a value matches any value in a list the subquery returns. It is the most intuitive of the multi-row operators.

SELECT c.customer_name
FROM   customers c
WHERE  c.region IN (
    SELECT DISTINCT c2.region
    FROM   customers c2
    JOIN   orders o ON c2.customer_id = o.customer_id
    WHERE  o.order_amount > 15000
);

The subquery returns the regions where someone has placed a big order. The outer query returns every customer in those regions — not just the ones who ordered. On our data, Charlie Ltd's 22,000 order and Diana Retail's 18,000 order are both above 15,000, so their regions (North and West) qualify, and every customer in those regions appears.

The DISTINCT in the subquery is not strictly necessary — IN handles duplicates fine — but it makes the intent clearer and can help the optimizer with smaller result sets.

The one gotcha with IN: NULL. If the subquery returns a NULL, comparisons against it are unknown rather than false. For most IN queries this does not matter — the outer rows that would match a real value still match. It is NOT IN where the trap bites.

EXISTS returns true if the subquery finds at least one row. It does not care what the row contains — only whether it exists.

SELECT c.customer_name
FROM   customers c
WHERE  EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Every customer who has placed at least one order comes back. On our data, that is Alice Corp, Bob Traders, Charlie Ltd, and Diana Retail — four rows. The three customers with no orders are excluded.

The SELECT 1 inside the subquery is a convention. You could write SELECT * or SELECT o.order_id and the result would be identical. The database does not read the column values — it just checks whether any row matches the WHERE condition in the subquery.

EXISTS is usually faster than IN because it can stop as soon as it finds one matching row. For a customer with a million orders, EXISTS reads one and returns. IN may need to evaluate the full subquery list before it can compare.

NOT EXISTS returns true when the subquery finds no matching rows. It is the standard pattern for finding rows that have no corresponding data in another table.

SELECT c.customer_name
FROM   customers c
WHERE  NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Every customer with no orders comes back. On our data, that is Ethan Supplies, Fiona Mart, and George Wholesale — the three customers who have never placed an order.

The reason to prefer NOT EXISTS over NOT IN is NULL safety. If the subquery inside a NOT IN returns even one NULL, the whole condition evaluates to unknown for every row, and the query returns nothing. Silent wrongness. NOT EXISTS does not have this issue because it only checks row presence.

The performance profile is also usually better with NOT EXISTS. It can stop as soon as it finds a match, and if no match exists, that is exactly the answer the outer query needs.

Aggregates inside subqueries are how you compare each row against a value that depends on the whole table, or on a group within it.

SELECT c.customer_name, c.credit_limit
FROM   customers c
WHERE  c.credit_limit > (
    SELECT AVG(credit_limit) FROM customers
);

The subquery computes the company-wide average credit limit, and the outer query filters customers above that average. The subquery runs once, produces a single number, and the outer query uses it for every row.

For per-group comparisons, the subquery becomes correlated:

SELECT c.customer_name, c.credit_limit
FROM   customers c
WHERE  c.credit_limit > (
    SELECT AVG(c2.credit_limit)
    FROM   customers c2
    WHERE  c2.region = c.region
);

Now each customer is compared against the average for their own region. The correlation is on c2.region = c.region, so the subquery runs once per distinct region — or once per row, depending on the optimizer.

COUNT works the same way. COUNT(*) inside a correlated subquery gives the number of orders per customer. COUNT(DISTINCT ...) counts unique values. Every aggregate function that works in a normal query works in a subquery.

INSERT INTO ... SELECT ... copies rows produced by a query into a table. The SELECT can be a full query with joins, aggregates, and subqueries of its own.

INSERT INTO high_value_customers (customer_id, customer_name, total_spend)
SELECT c.customer_id,
       c.customer_name,
       SUM(o.order_amount)
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.order_amount) > 15000;

Every customer whose total spend exceeds 15,000 gets copied into the summary table. On our data: Alice Corp (28,500), Charlie Ltd (22,000), and Diana Retail (18,000).

You can also embed a subquery inside the SELECT of the INSERT:

INSERT INTO top_customers (customer_id, customer_name)
SELECT customer_id, customer_name
FROM   customers
WHERE  credit_limit = (SELECT MAX(credit_limit) FROM customers);

That copies the one customer with the highest credit limit. Both forms are standard — pick the one that reads more naturally for the job.

The column count and order in the INSERT INTO must match the SELECT's output. Getting them out of order is the most common mistake, and the resulting data corruption is invisible until someone queries the table and sees names in the amount column.

A subquery in the SET clause provides the value to update with. It is usually correlated, so each row gets its own computed value.

UPDATE customers c
SET    credit_limit = (
    SELECT MAX(o.order_amount)
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
)
WHERE  EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Every customer with orders gets their credit limit reset to their own largest order. The WHERE clause is essential — without it, customers with no orders would have their credit limit set to NULL, because the correlated subquery returns NULL when there are no rows to aggregate.

On our data: Alice Corp's credit limit becomes 12,000 (her largest order). Bob Traders becomes 4,500. Charlie Ltd becomes 22,000. Diana Retail becomes 18,000. Ethan Supplies, Fiona Mart, and George Wholesale are untouched.

The mistake to avoid is writing an UPDATE without the WHERE clause when the subquery is correlated. If you want to update every row, that is fine. But if some rows should be excluded because the subquery would produce NULL for them, the WHERE clause prevents an accidental NULL overwrite.

A subquery in the WHERE clause of a DELETE identifies which rows to remove, based on a condition computed from other data.

DELETE FROM customers
WHERE  customer_id IN (
    SELECT customer_id FROM orders WHERE order_status = 'Cancelled'
);

That removes customers who have at least one cancelled order. Whether that is what you actually want depends on the business rule — "customers with cancelled orders" is different from "customers whose only orders were cancelled."

For the second interpretation, the subquery needs a different shape:

DELETE FROM customers
WHERE  customer_id IN (
    SELECT customer_id
    FROM   orders
    GROUP BY customer_id
    HAVING COUNT(*) = COUNT(CASE WHEN order_status = 'Cancelled' THEN 1 END)
);

That removes customers whose every order is cancelled. The two forms are easy to confuse, and the difference is important — one deletes customers with any cancelled order, the other deletes only the ones who have no other activity.

Before running any DELETE with a subquery, run the equivalent SELECT first and check the rows it would affect. There is no undo for a DELETE, and the subquery has a way of producing a different result set than the person writing it expected.

Two patterns work: NOT IN and NOT EXISTS. They both find rows in one table that have no matching row in another, but they behave differently when NULLs are involved.

-- NOT IN: simpler but has a NULL trap
SELECT customer_name
FROM   customers
WHERE  customer_id NOT IN (SELECT customer_id FROM orders);

-- NOT EXISTS: NULL-safe, usually the better choice
SELECT c.customer_name
FROM   customers c
WHERE  NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Both return Ethan Supplies, Fiona Mart, and George Wholesale on our data. But the NOT IN version would return zero rows the moment the orders table contained a single row with a NULL customer_id. The NOT EXISTS version would keep working correctly.

The reason is NULL semantics. NOT IN compares each outer value against every value in the subquery's list. When one of those values is NULL, the comparison is unknown, and unknown fails every NOT IN condition. Every outer row gets dropped. NOT EXISTS does not compare values — it only asks whether a matching row exists. A NULL customer_id in the orders table simply does not match any outer row, which is the correct behaviour.

My rule: use NOT EXISTS by default, and only reach for NOT IN when you are certain the subquery cannot return NULLs. On real data, that certainty is rarely justified.

ANY and ALL compare a value against a list the subquery returns. ANY succeeds if the comparison is true for at least one value. ALL succeeds only if the comparison is true for every value.

-- Greater than at least one of Alice Corp's orders
SELECT customer_name, credit_limit
FROM   customers
WHERE  credit_limit > ANY (
    SELECT order_amount FROM orders WHERE customer_id = 'C001'
);

Alice Corp's orders are 12,000, 7,500, and 9,000. > ANY means greater than at least one — that is, greater than the smallest, 7,500. Most customers qualify.

-- Greater than all of Alice Corp's orders
SELECT customer_name, credit_limit
FROM   customers
WHERE  credit_limit > ALL (
    SELECT order_amount FROM orders WHERE customer_id = 'C001'
);

> ALL means greater than the largest, 12,000. Bob Traders (25,000), Charlie Ltd (75,000), Diana Retail (40,000), Ethan Supplies (55,000), George Wholesale (80,000), and Alice Corp (50,000) all qualify.

The mnemonic that makes it stick: > ANY is > MIN(...) and > ALL is > MAX(...). ANY is easy to beat, ALL is hard to beat.

ANY and ALL show up less often in production code than IN and EXISTS, but they are worth knowing because they express intent more precisely when the comparison is a range rather than equality.

Five patterns cover most subquery optimization work. They are listed roughly in order of impact.

1. Convert correlated subqueries to joins. A correlated subquery runs once per outer row. A join against a grouped subquery runs once for the whole table. Same output, dramatically different cost.

-- Slow: correlated subquery
SELECT c.customer_name
FROM   customers c
WHERE  c.credit_limit > (SELECT AVG(order_amount) FROM orders WHERE customer_id = c.customer_id);

-- Fast: join against a grouped subquery
SELECT c.customer_name
FROM   customers c
JOIN  (SELECT customer_id, AVG(order_amount) AS avg_order FROM orders GROUP BY customer_id) t
       ON c.customer_id = t.customer_id
WHERE  c.credit_limit > t.avg_order;

2. Use EXISTS instead of IN for existence checks on large data. EXISTS can stop as soon as it finds a match. IN may evaluate the whole subquery.

3. Use NOT EXISTS instead of NOT IN. Both for the NULL safety reason and because the optimizer usually handles it better.

4. Push filters into the subquery. If the outer query only cares about orders above a threshold, filter inside the subquery so it produces fewer rows to combine.

5. Index the columns the subquery filters on. An index on orders.customer_id turns each subquery execution from a full table scan into an index lookup. On large tables this is often the single biggest win.

The way to confirm which of these is the problem is to run EXPLAIN ANALYZE. If the subquery's node in the plan has a high iteration count, it is running per outer row. If it has a "seq scan" attached, it needs an index.

A subquery in the WHERE clause of an UPDATE can enforce a condition before the update runs. If the condition fails, no rows are updated — the query simply returns zero rows affected.

UPDATE customers
SET    credit_limit = 60000
WHERE  customer_id = 'C002'
AND    credit_limit < (SELECT AVG(credit_limit) FROM customers);

The update only happens if Bob Traders' credit limit is below the company average. His limit is 25,000, and the average is about 54,166, so the condition is true and the update runs. If the same query ran against Charlie Ltd (75,000), nothing would happen — his limit is already above the average, so the WHERE clause fails and no rows are updated.

This pattern is useful when you want to update conditionally based on the state of the table. Rather than checking the condition in application code, checking it in the SQL ensures the check and the update happen atomically — the data cannot change between the check and the write.

The same idea works in DELETE. A DELETE with a subquery condition only removes rows that still satisfy the condition at execution time. If a concurrent transaction modified the rows, the delete simply affects fewer rows.

Subqueries can appear in multiple positions in the same query. A common pattern is two subqueries in the WHERE clause combined with AND or OR.

SELECT c.customer_name
FROM   customers c
WHERE  c.region = (SELECT region FROM regions WHERE region_name = 'Priority')
AND    c.credit_limit > (SELECT AVG(credit_limit) FROM customers);

The first subquery produces the priority region's id. The second produces the company-wide average credit limit. Both run once. The outer query requires both conditions to be true.

Subqueries in different positions can also coexist:

SELECT c.customer_name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count,
       (SELECT SUM(order_amount) FROM orders o WHERE o.customer_id = c.customer_id) AS total_spend
FROM   customers c
WHERE  c.customer_id IN (SELECT customer_id FROM orders WHERE order_amount > 10000);

That query has a scalar subquery in the SELECT list for the count, another for the total, and a multi-row subquery in the WHERE clause. Three subqueries in one query, each doing its own job.

When queries have four or five subqueries, they can start to be hard to read. Rewriting them as CTEs usually improves clarity:

WITH order_summary AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(order_amount) AS total_spend
    FROM   orders
    GROUP BY customer_id
)
SELECT c.customer_name, s.order_count, s.total_spend
FROM   customers c
JOIN   order_summary s ON c.customer_id = s.customer_id
WHERE  c.customer_id IN (SELECT customer_id FROM orders WHERE order_amount > 10000);

Same result, easier to scan, and often faster because the aggregation runs once.

Subqueries and NULL interact in a few predictable ways. The trick is knowing which pattern is safe and which one silently produces the wrong answer.

Replacing NULLs with display values. A subquery can compute a fallback value to show when the column itself is NULL:

SELECT c.customer_name,
       COALESCE(c.credit_limit,
                (SELECT AVG(credit_limit) FROM customers)) AS effective_limit
FROM   customers c;

For Fiona Mart, whose credit limit is NULL, the subquery provides the company average as a substitute. For everyone else, their real limit appears.

Avoiding NOT IN when NULLs are possible. As covered earlier, the fix is NOT EXISTS. That is the single most important NULL-related subquery rule.

Being explicit about NULL in comparisons. If a correlated subquery can return NULL, the outer comparison will silently exclude those rows. Use COALESCE around the subquery, or wrap it in a CASE that handles the NULL case:

SELECT c.customer_name
FROM   customers c
WHERE  COALESCE(
    (SELECT AVG(order_amount) FROM orders WHERE customer_id = c.customer_id),
    0
) < c.credit_limit;

Now customers with no orders are treated as having an average of zero, and their credit limit is compared against that. Without the COALESCE, those customers would drop out of the result.

Handling NULL in the SELECT list's scalar subquery. A scalar subquery returns NULL when it finds no rows. Wrap it in COALESCE if that NULL should be zero or a placeholder:

SELECT c.customer_name,
       COALESCE((SELECT MAX(order_amount) FROM orders WHERE customer_id = c.customer_id), 0) AS largest_order
FROM   customers c;

A correlated subquery is the standard way to compare each row of one table against values derived from another table. The pattern shows up whenever you want per-row comparisons that depend on the current row's context.

SELECT c.customer_name, c.credit_limit
FROM   customers c
WHERE  c.credit_limit > (
    SELECT AVG(o.order_amount)
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
);

Each customer's credit limit is compared against their own average order amount. The correlation on o.customer_id = c.customer_id links the subquery to the outer row.

On our data, customers with orders come back if their limit exceeds their average order — Alice Corp, Bob Traders, Charlie Ltd, and Diana Retail. Customers with no orders are excluded because the subquery returns NULL for them.

Two patterns worth knowing beyond this one:

Compare against the max in another table. A scalar subquery with MAX produces the largest value in the other table, and the outer query compares each row against it. Useful for "find the row that matches the extreme in another table" questions.

Compare against a group of values. A subquery returning a list can feed IN, EXISTS, ANY, or ALL for more complex comparisons. IN checks membership. EXISTS checks for the existence of a match. ANY and ALL handle range comparisons against multiple values.

When the comparison gets complex enough — multiple subqueries with different correlations — consider whether a CTE would be clearer. A CTE names the intermediate result, which lets you build the comparison in stages instead of nesting everything into one query.