Subqueries (v2 pilot) — Using One Query's Result Inside Another

Welcome to Series 2, Intermediate SQL course, Leo. This series is about answering bigger questions in steps — subqueries, CTEs, window functions — and today we start with the first: subqueries, a query inside another query.

Ha — you wanted a thank-you and got more work. That is the thank-you, Leo: she trusts your numbers, so she wants more of them. And her question looks like one — it's really two, and one is hidden inside the other.

One thing before we continue. Like in Series 1, the database comes bundled with DataPallas — only with a lot more data this time. Connect to it in CloudBeaver, then run SET search_path TO northwind_co_s first.

Your Series 1 queries ran on the starter data, and the company has grown since. So have you: the old database was tiny on purpose — few enough rows that you could picture every one. You write real queries now.

Leo, see these: Tiny, from the beginner series, and the Small northwind_co_s dataset, used by many courses — including this one — both come with DataPallas. Large is needed in a handful of episodes, like making your queries faster — there I'll show you how to install it.

OK, enough housekeeping Leo — let's come back to your manager's question: which customers spend more than the average customer? This is our goal for today and you'll answer it, but first I'll warm you up on something smaller: which products cost more than the average price? Question one: what's the average?

Question two. Paste your number into a WHERE, and ask for every product above it.

30 products, and every one of them is right — today.

Because 24.01 is a number you pasted in, and Classic Tonic is on the list by six cents. Change one price tomorrow and the real average moves — but WHERE "UnitPrice" > 24.01 keeps answering yesterday's question, and nothing tells you.

Then let the database work out the average. Where does it go?

Leo, you've met this refusal before, in the lesson on HAVING. WHERE looks at one row at a time, before anything has been added up — so at that moment there is no average to compare with.

Yes, you worked it out with a different query. So, for getting it to work, try handing the WHERE that exact query, the one that gave you the average. Put it in brackets, right where your number was — and the database runs that inner query first.

Here is your first subquery, in between brackets, right where your number was. (SELECT AVG("UnitPrice") FROM "Products") runs first and hands back one value — 24.01 today — and the outer query uses it exactly like the number you pasted in.

Here's the habit that saves you hours with subqueries: in CloudBeaver, select just the subquery and run it on its own. If it doesn't hand back what you expect, the whole query can't either.

How many beverages are there, Leo?

So decide which one you mean. More than every beverage is more than the dearest beverage: MAX("UnitPrice"). Ten prices go in, one value comes out — or, if you meant any of the ten, use IN, which we'll meet shortly.

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

Look at the inside. I picked category 9 on purpose, because I know 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.

Good, Leo. So far the subqueries we tried have handed back one value, like the average or the maximum price you just saw — but they can also hand back a list, or a whole table. What a subquery hands back decides where it can go. One value goes where a number could go. A list goes inside IN (…). And a whole table goes where a table goes — in the FROM. You've just done the first one.

Now let's see where a subquery that returns a list helps. Royal Raisins has been discontinued, and sales wants to tell everyone who bought it this year — which customers bought it in 2024?

The subquery hands back the customer of every 2024 order line for product id 39 (Royal Raisins), and IN keeps each customer whose id is somewhere in that list.

9 rows. Write that number down. Now run the whole query — and watch how many come back.

You worked that out exactly right, Leo. In the subquery a row is one order line — that is its grain. 9 of them, because Royal Raisins sold 9 times in 2024, and Yarrow Pantry is two of the 9.

Exactly. IN never counts lines: it asks each customer one question — is your id in that list? — and a yes is a yes however often the id appears.

Leo, you might think the outer query isn't doing much, and that the join inside could answer on its own. As we already saw, this join returns 9 rows, with Yarrow Pantry twice.

Fair question, Leo, and you are right: you could use a JOIN with DISTINCT to return the correct 8 customers here.

But it would be fragile. SELECT DISTINCT name does return the 8 customers here. But the moment a second column joins it, as in SELECT DISTINCT name, order id — and believe me, people do this often — the duplicates come back. The real problem is deeper: the JOIN itself is at the per-order-line grain, and you were looking for an answer about customers.

Exactly, Leo. Instead of returning order lines and then patching them to get customers, it is better to ask for customers directly.

Leo, subqueries and joins are both tools you have at your disposal now. Depending on the context you will use either one, and many times both together in the same query. Here are some hints for when you need each.

Look at what the answer has to contain. Customers who bought Royal Raisins in 2024? Only customer names — the Royal Raisins product is just how you pick them. So: SELECT "CompanyName" … WHERE "CustomerID" IN (subquery) is asking for customers directly.

Now change the question: which customers bought Royal Raisins, and how many boxes each? The request needs information from two tables, Customers and Order Details — and only a join can put both side by side.

Not by nature. Databases often run both the same way, so choose by what the answer needs. When a query really is slow, you read its plan and measure — you don't guess. In Series 3, I'll teach you how to tune SQL query performance and how to read execution plans.

Leo, that was quite a long detour. Let's now come back to your manager's question. Customers who spend more than the average — as we discussed, that is really two questions, so, question one: what is the average?

Run it. And count what comes back.

Can nearly every customer be above the average customer?

Right. So whose average is 595.13?

One row per what — the question from the report lesson, back again. A customer's total is lines added together, so it almost always beats one line. By coincidence, the one customer left out, Upland Food Hall, bought only two lines in five years — that is why it was the only one below your number.

Leo, I'll use this opportunity to show a subquery which returns a table. As I told you, a subquery can return a number, a list or a whole table. This one is the query you wrote earlier: it builds one row per customer, with that customer's total — and the outer query averages that table.

Because FROM expects a table, and every table needs a name — so the outer query can say what it is reading from. Some databases refuse a subquery in the FROM without one.

These are two very different averages — over two hundred times apart. Neither of them is wrong. They answer different questions — and only one of them is your manager's.

This is the whole monster query, Leo — let's see if you can read it. It is the query you built earlier, and at the end, a HAVING that compares each customer's total with a subquery that builds a table of customer totals and hands back their average. A table, inside a value, inside a query.

Good instinct, Leo, but no. That 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. Subqueries that do depend on the outer row exist too — those are worked out row by row, and that is for later in this series.

Leo, you explained the query better than I could. Here is what it returns: 30 customers who spend more than the average, not 118. Emerald Grocers is the last one above the line, on 175249.7.

That's the power and the danger of a subquery in one sentence. The outer query trusts whatever the subquery hands it.

Keep that complaint, Leo — it's a good one. Later in this series we'll improve the situation: every one of those subqueries gets a name, and the query reads top to bottom. For today: read it from the inside out.

But I disagree that you can't read it — a moment ago you explained this monster better than I could. And ten minutes to understand a query like this is perfectly normal.

Let's move on, Leo. Before sending these 30 largest spenders back, read the second line of your manager's note. 2024: orders are up 10 per cent on last year — but invoicing is down. Which is it?

Two numbers, so two subqueries — and a value can go anywhere one value can go, even in the select list. The first is your report's money, for orders placed in 2024. The second reads "Invoices": one row for every invoice the company sent, with its date and its amount.

About 3.6 million ordered, but only 3.2 million invoiced. Quite a big difference between them.

Neither. They answer different questions. Ordered measures demand — what customers asked us for. Invoiced is what the company actually billed. Your largest spenders query summed order lines, so it was about the first one.

That is our mystery. Throughout this series you'll gather your SQL knowledge piece by piece, and we'll apply it, step by step, to solve it.

Leo, we're almost done. Before we go: three checks before you trust a subquery. Run the inside on its own. Check its shape, because the shape decides where it can go. And check its grain, because that is the one that never raises an error.

That's subqueries, Leo. The subquery runs first, and what it hands back — a value, a list or a table — decides where it can go. You caught the trap yourself: the right average, of the wrong thing.

We'll do it all, Leo. Next lesson is CASE: labels for rows, and several kinds of order counted side by side — a column each, in one query.