Correlated subqueries
In short: A correlated subquery uses a column of the outer query, so it is evaluated again for every row, while a subquery in FROM turns its result into a new table.
Problem
Expensive for whom?
From the previous lesson you know which products are above the average price in the shop:
Everything from the kitchen is “expensive”, and no bread is. But bread is not compared with a frying pan. The manager wants to know which products are expensive for their own category: the cheese against the dairy, the coffee against the drinks.
The idea
Correlated subquery
A subquery can use a column from the row that the outer query is looking at right now:
p is the product outside, p2 are the products in the subquery. The condition p2.category_id = p.category_id says “the products of its category”. For white bread the subquery works out the bread average (1.60 €), for the white cheese the dairy average (4.66 €).
Such a subquery is called correlated: it depends on the outer row and is worked out again for every row. The ordinary subquery from the previous lesson gives the same answer for all rows.
It would not work without the short names p and p2: both are the products table, and the database has to know which category_id is outside and which is inside.
The idea
In WHERE
Now the manager's question: which products are above the average of their category?
Nine products, at least one from every category: the wholegrain bread is above the bread average, and the laundry powder is above the cleaning average.
The gift card is missing, even though it costs 20 €. Its category is NULL, p2.category_id = NULL is not true for any row, the average is NULL and the comparison is unknown. The same NULL as in the NULL lesson, only better hidden.
Try it
The last order
For every customer show their name and the date of their last order (column last_order). The customers without orders must be in the list too.
Solution
SELECT name,
(SELECT MAX(o.ordered_at) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c;(SELECT MAX(o.ordered_at) FROM orders o WHERE o.customer_id = c.id) as a column. 20 rows. Teodora and Yavor have no orders, so theirs is NULL. With JOIN and GROUP BY they would disappear, unless it is a LEFT JOIN.
More precisely, if you are curious
In principle a correlated subquery runs once for every outer row: 20 customers, 20 times. Databases often rewrite it into something faster, but with big tables it is worth comparing with JOIN and GROUP BY. How to see what the database does is in the module on speed.
A correlated subquery also gives the number of the last order, not just its date. In the module on summarising you saw that SQLite allows it with MAX and a free column, while other databases do not. Here is the way that works everywhere:
18 rows, one for every customer with orders. If a customer has two orders on their last day, both will come out.
See it
The average order value
Accounting asks for the average value of one order. The first attempt:
It takes two steps: first the value of every order, then the average of those values. You can already do the first step:
The idea
Subquery in FROM
The result of every query is a table. So it can go in FROM, where you write a table name:
The inner query makes a table with 60 rows, one per order. The outer one reads it like any other table and takes the average: 32.73 €. This is called a subquery in FROM (a derived table). The name after AS is what you call the new table.
More precisely, if you are curious
SQLite does not require a name for a subquery in FROM, but MySQL insists on one. Always give it a name and the query will run everywhere.
Try it
How many products in an order
The warehouse is planning boxes and asks: how many different products does one order have on average? Round to two decimal places (column avg_lines). The field already counts the products in every order.
Solution
SELECT ROUND(AVG(lines), 2) AS avg_lines
FROM (
SELECT order_id, COUNT(*) AS lines
FROM order_items
GROUP BY order_id
) AS t;The count for every order becomes a table in FROM, and outside it is ROUND(AVG(lines), 2): 2.53 products. 152 lines in 60 orders.
Exercise
Two tasks
1. Marketing wants to win back the customers who have not ordered since the summer.
Show the names of the customers whose last order is before 1 July 2026.
Solution
SELECT name FROM customers c
WHERE (SELECT MAX(o.ordered_at) FROM orders o WHERE o.customer_id = c.id) < '2026-07-01';WHERE (SELECT MAX(o.ordered_at) FROM orders o WHERE o.customer_id = c.id) < '2026-07-01'. Eight customers. Teodora and Yavor are not there: without orders the last date is NULL, and a comparison with NULL is unknown.
2. How much does one customer spend in the shop on average?
Work out the average amount a customer has spent on their orders, without the cancelled ones, rounded to two decimal places (column avg_spent). Count only the customers with at least one order.
Solution
SELECT ROUND(AVG(spent), 2) AS avg_spent
FROM (
SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id
) AS t;In FROM is the total of every customer (JOIN of orders and order_items, WHERE o.status <> 'cancelled', GROUP BY o.customer_id), and outside it is ROUND(AVG(spent), 2): 94.28 €. The average is over 18 customers. The two without orders are not in the subquery, so they do not pull the average down.
Test