Subqueries — Using One Query's Result Inside Another

The report came back with a question

The top 10 customers report from the last lesson of Series 1 went to the manager, and it came back with a question: which customers spend more than the average customer?

That question contains a second question: what is the average customer? Without SQL beyond Series 1, the only way to answer it is to run one query, write the number down, and paste it into a second query. This lesson shows how to let the database answer the first question inside the second one, with a subquery: a query in brackets that runs first, and whose result the outer query uses.

Warm up on one table

Before customers, which needs three tables, start with something smaller: which products cost more than the average price?

First question: what is the average price?

SELECT ROUND(AVG("UnitPrice"), 2)
       AS "Average price"
FROM "Products";

Second question: which products cost more than that? Paste the number into a WHERE:

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE "UnitPrice" > 24.01
ORDER BY "UnitPrice" DESC;

30 of the 80 products, and the answer is correct today. The problem is the number 24.01: it was pasted in by hand. Classic Tonic is on the list by six cents. If a price changes tomorrow, the real average moves, but the query still compares with 24.01. It keeps answering yesterday's question, and nothing reports that it is out of date.

Why the average cannot go in the WHERE

The obvious fix is to let the database calculate the average in the same place:

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE "UnitPrice" > AVG("UnitPrice");

PostgreSQL refuses it:

SQL Error [42803]: ERROR: aggregate functions are not allowed in WHERE
  Position: 71

This is the rule from the HAVING vs WHERE lesson. WHERE looks at one row at a time, before anything has been added up, so when it runs there is no average to compare with.

"But the average exists: I worked it out a minute ago." It does, but with a different query. So hand the WHERE that exact query, the one that gave you the average.

A query in brackets

Put it in brackets, where the pasted number was:

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE "UnitPrice" > (SELECT AVG("UnitPrice")
                     FROM "Products")
ORDER BY "UnitPrice" DESC;

The database runs the query in brackets first. It returns one value, the average price, and the outer query compares every product with that value, exactly as it did with the pasted-in 24.01. The result is the same 30 products. The difference is that tomorrow the brackets return tomorrow's average, and there is no number to keep up to date.

Run the inside on its own

A subquery is a complete query. In CloudBeaver, select just the text inside the brackets and run it (CloudBeaver runs only the selected text). It should return what you expect, here a single number. If the inside returns something unexpected, the whole query cannot be right either.

1. Select only the part in brackets.
2. Run it on its own, and check that it returns what you expected.
3. Then run the whole query.

One value, or the query stops

Now a question of our own: which products cost more than our drinks? Beverages are category 1, so the brackets return the beverages' prices:

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE "UnitPrice" > (SELECT "UnitPrice"
                     FROM "Products"
                     WHERE "CategoryID" = 1);

SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression

There are 10 beverages, so the brackets return 10 prices, and > compares one value with one value. The error is really a question: which of the 10 prices is meant? "More than every beverage" means more than the most expensive beverage, so ask for that one value with MAX. (If you meant "any of the 10", the tool is IN, coming up shortly.)

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE "UnitPrice" > (SELECT MAX("UnitPrice")
                     FROM "Products"
                     WHERE "CategoryID" = 1)
ORDER BY "UnitPrice" DESC;

Fourteen products cost more than the most expensive beverage, Alpine Ale, at 39.1.

Two of the rows behind the "…" cost exactly the same, 43.12. ORDER BY "UnitPrice" does not say which of two equal prices comes first, so the database may return them in either order. When the order of tied rows matters, add a second column to the ORDER BY, such as "ProductName".

No error, no rows

Now a quiet one. Take the same query and change a single digit, category 9:

SELECT "ProductName", "UnitPrice"
FROM "Products"
WHERE "UnitPrice" > (SELECT MAX("UnitPrice")
                     FROM "Products"
                     WHERE "CategoryID" = 9)
ORDER BY "UnitPrice" DESC;

It returns 0 rows, and no error. Did it work? Look at the inside: there is no category 9, so the brackets found no products, and a subquery that finds nothing hands back NULL. Nothing is ever greater than NULL, so the WHERE keeps no rows and raises no error.

Zero rows can mean two things: there really is no answer, or the question was wrong. Run the inside on its own to tell which: a NULL means the question needs fixing.

Three shapes

What a subquery returns decides where it can go:

The queries above used a value. Next, a list.

A list: IN

Royal Raisins, product 39, has been discontinued, and sales wants to tell the customers who bought it this year. Which customers bought Royal Raisins in 2024? The brackets return the customer of every 2024 order line for Royal Raisins, and IN keeps each customer whose "CustomerID" appears in that list:

SELECT "CompanyName"
FROM "Customers"
WHERE "CustomerID" IN (
  SELECT o."CustomerID"
  FROM "Orders" o
  JOIN "Order Details" d
    ON d."OrderID" = o."OrderID"
  WHERE d."ProductID" = 39
    AND o."OrderDate" >= DATE '2024-01-01'
    AND o."OrderDate" <  DATE '2025-01-01'
)
ORDER BY "CompanyName";

First do what you were told: run the inside on its own. It returns 9 rows, one customer id per order line, because Royal Raisins sold on 9 order lines in 2024. Write that number down, then run the whole query. It is tempting to predict 9 customers. The result has 8:

Yarrow Pantry bought Royal Raisins twice. The two queries have different grains: inside the brackets, one row is one order line (9 of them); outside, one row is one customer (8 of them). IN never counts lines. It asks one yes-or-no question for each customer, "is this customer's id in the list?", so each customer appears once, however many times their id appears inside the brackets.

The same question written as a join returns one row per matching order line:

SELECT c."CompanyName"
FROM "Customers" c
JOIN "Orders" o
  ON o."CustomerID" = c."CustomerID"
JOIN "Order Details" d
  ON d."OrderID" = o."OrderID"
WHERE d."ProductID" = 39
  AND o."OrderDate" >= DATE '2024-01-01'
  AND o."OrderDate" <  DATE '2025-01-01'
ORDER BY c."CompanyName";

9 rows, with Yarrow Pantry twice. Both queries are correct SQL, but only the first answers the question: who to tell is a list of customers, not a list of order lines. This is the repeated-rows problem from the report lesson, seen from the other side: there, count(DISTINCT …) removed the repeats; here, IN never creates them.

Subquery or join?

The outer query looks anemic: the join does most of the work, and DISTINCT could remove the duplicates. So with a JOIN plus DISTINCT, are subqueries needed at all?

You could indeed use a JOIN with DISTINCT to get the correct 8 customers here, but it is fragile:

The real problem is deeper. The join itself is at the order-line grain, and the question is about customers. It should feel strange to answer a question about customers with a query on "Order Details", even if a hack can patch the result. Instead of returning order lines and then patching them into customers, ask for customers directly.

Both are tools you have now, and many queries use both together. A rule of thumb is to look at what the answer has to contain:

• Only columns from one table, and the other table just picks the rows: a subquery. "Customers who bought Royal Raisins in 2024" needs only customer names; the product is just how you pick them.
• Columns from both tables: a join. "Which customers bought Royal Raisins, and how many boxes each?" needs "Customers" and "Order Details" side by side, and only a join can put them there.

Is one of them faster? Not by nature: databases often run both the same way, so choose by what the answer needs, not by speed. When a query really is slow, read its plan and measure; do not guess.

Your turn

How many products sell at or below the average price? 30 are above it, so predict the rest before you run it:

SELECT count(*) AS "Products"
FROM "Products"
WHERE "UnitPrice" <= (SELECT AVG("UnitPrice")
                      FROM "Products");

50: every product that is not above the average, because no product costs exactly the average.

Now the customers

Back to the manager's question: which customers spend more than the average customer? Same method as with prices. First, the average:

SELECT ROUND(AVG("UnitPrice" * "Quantity"
         * (1 - "Discount")), 2) AS "Average"
FROM "Order Details";

Then the report from the last lesson, with a HAVING at the end that keeps the customers whose total is above 595.13:

SELECT c."CompanyName",
       ROUND(SUM(d."UnitPrice" * d."Quantity"
         * (1 - d."Discount")), 2) AS "Total sales"
FROM "Customers" c
JOIN "Orders" o
  ON o."CustomerID" = c."CustomerID"
JOIN "Order Details" d
  ON d."OrderID" = o."OrderID"
GROUP BY c."CompanyName"
HAVING SUM(d."UnitPrice" * d."Quantity"
         * (1 - d."Discount")) > 595.13
ORDER BY "Total sales" DESC;

118 rows come back. Northwind Company has 120 customers; one of them, Yarrow Supermarket, has never placed an order, so the joins leave 119. And 118 of those 119 are "above average", including Tundra Grocers with 1239.25, against a best customer on 797164. Nothing errors, and the answer cannot be right. A list of above-average customers that holds almost every customer is not measuring against the average customer.

The average of what?

595.13 is the average of the money column in "Order Details", and every row of that table is one order line. So 595.13 is the average order line. A customer's total is many order lines added together, so it almost always beats one line. The query compared each customer with an order line.

The one customer the query left out proves it. Upland Food Hall has placed a single order of two lines, worth 465.75 in total: less than one average line, and the only customer whose whole history is that small.

This is the question from the report lesson again: one row represents what? To compare a customer with the average customer, first build one total per customer, then take the average of those totals. That needs the third shape: a table in brackets.

A table in brackets

SELECT ROUND(AVG("Total"), 2) AS "Average customer"
FROM (SELECT o."CustomerID",
             SUM(d."UnitPrice" * d."Quantity"
               * (1 - d."Discount"))
               AS "Total"
      FROM "Orders" o
      JOIN "Order Details" d
        ON d."OrderID" = o."OrderID"
      GROUP BY o."CustomerID") AS t;

The query in brackets returns a table with one row per customer and that customer's total. The outer query reads that table in its FROM and averages the totals.

The table in brackets is given a name, AS t. PostgreSQL 16 and DuckDB accept a subquery in FROM without a name, but older versions of PostgreSQL, MySQL and SQL Server refuse it, so always name it.

The two averages are far apart:

Neither is wrong. They answer different questions, and the manager asked about customers.

Customers above the average customer

Now put the table in brackets inside the HAVING. The brackets build the table of customer totals and return its average, a single value, and HAVING compares each customer's total with it:

SELECT c."CompanyName",
       ROUND(SUM(d."UnitPrice" * d."Quantity"
         * (1 - d."Discount")), 2) AS "Total sales"
FROM "Customers" c
JOIN "Orders" o
  ON o."CustomerID" = c."CustomerID"
JOIN "Order Details" d
  ON d."OrderID" = o."OrderID"
GROUP BY c."CompanyName"
HAVING SUM(d."UnitPrice" * d."Quantity"
         * (1 - d."Discount"))
     > (SELECT AVG(t."Total")
        FROM (SELECT o2."CustomerID",
                     SUM(d2."UnitPrice"
                       * d2."Quantity"
                       * (1 - d2."Discount")) AS "Total"
              FROM "Orders" o2
              JOIN "Order Details" d2
                ON d2."OrderID" = o2."OrderID"
              GROUP BY o2."CustomerID") AS t)
ORDER BY "Total sales" DESC;

The inner query uses different aliases, o2 and d2, so it is clear which tables belong to which query.

30 customers spend more than the average customer. Emerald Grocers, with 175249.7, is the last one above 126193.54; Kestrel Pantry, with 121607.73, is the first one below it.

The outer query is exactly the same as before. Only the query in brackets changed, and the answer went from 118 customers to 30. The outer query trusts whatever the brackets return, which is what makes subqueries both useful and easy to get wrong.

Reading a nested query

The last query is a monster: a table, inside a value, inside a query. Read it from the inside out:

1. The deepest subquery is your own query from before: the spend per customer.
2. The next one, with AVG, turns all those spends into a single average.
3. The HAVING keeps only the customers who spent more than that average.
4. The joins, from "Customers" down to "Order Details", reach the money; the SELECT shows "CompanyName" and "Total sales"; ORDER BY puts the biggest at the top.

Does the database work the average out again for every customer? With 119 customers that would sound slow, but no. The subquery never mentions the customer being checked, so it does not change from row to row: the database works it out once and reuses the number, exactly like the 24.01 you used to paste in, only now the database fills it in. Subqueries that do depend on the outer row exist too. They are worked out row by row, and that comes later in this series.

The query works, but it is hard to read: brackets inside brackets inside a HAVING. Ten minutes to understand a query like this is perfectly normal. A later lesson in this series gives each subquery a name, so a query like this reads from top to bottom.

Ordered, and invoiced

The manager's note had a second line: "2024: orders up 10% on last year, but invoicing is down. Which is it?"

That is two totals for 2024. The first is the money the report already adds up, for the orders placed in 2024. The second comes from "Invoices", a table with one row for every invoice the company sent: the order it bills, the date it was sent, and the amount.

Two values, side by side

Each total is a value in brackets, and a value can go anywhere a single value can go, including the select list. So one query can return both, next to each other:

SELECT (SELECT ROUND(SUM(d."UnitPrice" * d."Quantity"
                 * (1 - d."Discount")), 2)
        FROM "Order Details" d
        JOIN "Orders" o
          ON o."OrderID" = d."OrderID"
        WHERE o."OrderDate" >= DATE '2024-01-01'
          AND o."OrderDate" <  DATE '2025-01-01') AS "Ordered 2024",
       (SELECT ROUND(SUM("Amount"), 2)
        FROM "Invoices"
        WHERE "InvoiceDate" >= DATE '2024-01-01'
          AND "InvoiceDate" <  DATE '2025-01-01') AS "Invoiced 2024";

The first bracket is the report's money, joined to "Orders" so that it can see "OrderDate", and kept for the orders placed in 2024. The second adds up the invoices sent in 2024. Select either bracket on its own and run it: each returns one number.

The outer SELECT has no FROM, because all it does is return the two values. PostgreSQL and DuckDB accept that; older versions of Oracle need FROM dual at the end.

Neither total is wrong

It is tempting to ask which of the two numbers is wrong. Neither is. The two totals answer different questions. What customers ordered measures demand: how much they asked us for. What was invoiced is what the company actually billed. The report answered the first question; the manager's note is about the gap between the two. In 2024, orders grew, and invoicing fell.

Why would invoicing fall while orders grew? It arrived as the manager's question. It leaves this lesson as Leo's, in four parts, and the next lessons follow them one step at a time: what is missing, where, since when, and how much? Not every lesson in this series stops to work on it — most of them have their own work to do — but the ones that do pick up exactly where the last left off.

Before you trust a subquery

1. Run the inside on its own.
2. Check its shape: a value, a list or a table. The shape decides where it can go, and the wrong shape stops the query with an error.
3. Check its grain: one row of the inside represents what? The wrong grain does not produce an error. It produces 118 customers out of 119 who are "above average".

Practice

There are ten koans for this lesson. They ask different questions from this page, on different tables: orders compared with the average freight, the products one customer bought, suppliers compared with the average supplier and sales reps compared with the average rep, so copying a query from this page will not answer them.

cd c:/DataPallas/db/datazeus
.\zeus.bat koans learnsql series2 _00

On macOS or Linux, run ./zeus.sh koans learnsql series2 _00 from the same folder.

The file to edit is:

c:\DataPallas\db\datazeus\tests\src\koans\groovy\datazeus\learnsql\series2\_00\SubqueriesKoans.groovy

Each koan has a ___ where the answer belongs. Replace one, save, run the command again, and watch that koan go green.

The koans follow this page: a value in brackets, a comparison that must receive one value, a list with IN and the extra rows a join returns instead, a table in FROM and its grain, and the average of the right thing. The last two give only the question, and the whole query has to be written from scratch.

What is next

In 2024, customers ordered more and the company invoiced less. Something between the order and the invoice is missing, and that raises the questions this series follows: what is missing, where, since when, and how much?

The next lesson is CASE: how a query turns a value into a label, and how one query counts several kinds of order side by side, in a column each.