Subqueries
In short: A subquery is a SELECT inside another query that returns one value or a list for WHERE, used with IN, EXISTS or NOT EXISTS.
Problem
Above average
The manager wants to see the products that cost more than the average price in the shop. You already know two queries: one finds the average price, the other looks for products above a given number.
You could run the first, copy the number and run the second. But tomorrow the prices will be different, and the copied number will be out of date.
The idea
Subquery
First the average price:
Now the same query, in brackets, in place of the number:
A query in brackets inside another query is called a subquery. The database runs it first, gets 7.036, and then checks every product with price > 7.036. Eight products come out.
This subquery returns one number, so it goes where a number would go.
More precisely, if you are curious
If a subquery in a comparison returns several rows, SQLite quietly takes the first one. Category 8 (“Kitchen”) has three products, but the query compares with only one of them:
Which row is “the first” is not promised. PostgreSQL and SQL Server give an error in this case, saying the subquery returned more than one row. If a subquery can return several rows, use IN.
Try it
Pricier than the kashkaval
Show the name and price of the products that cost more than the kashkaval cheese. Do not type its price by hand: get it with a subquery.
Solution
SELECT name, price FROM products
WHERE price > (SELECT price FROM products WHERE name = 'Kashkaval cheese');WHERE price > (SELECT price FROM products WHERE name = 'Kashkaval cheese'). The cheese is 9.50 €, and there are 6 pricier products, from the cutting board (11.40 €) to the knife set (39 €).
The idea
IN with a list from a query
You already know IN with a list typed by hand: city IN ('Varna', 'Burgas'). The list can also come from a subquery.
Which customers ordered in October 2026? Their numbers are in orders, and their names are in customers:
The subquery returns the numbers of the customers with orders from 1 October on. The outer query shows the customers whose id is in that list.
Subqueries can be nested inside each other. Each step is a separate query that you can run and check on its own.
Try it
Who buys coffee
The coffee supplier is launching a new flavour and wants to write to the people who have already bought “Coffee beans”. Show their names. The field starts with the first step: the product number.
Solution
SELECT name FROM customers
WHERE id IN (
SELECT customer_id FROM orders
WHERE id IN (
SELECT order_id FROM order_items
WHERE product_id = (SELECT id FROM products WHERE name = 'Coffee beans')
)
);Three steps from the inside out: the product number (17), the orders with a line for it (order_items), and the customers of those orders (orders). Three people come out: Maria, Viktoria and Simona. A JOIN works too: the result is compared, not the method.
The idea
EXISTS
Sometimes you do not care what the subquery returns, only whether it returns anything. Which customers have no orders yet?
EXISTS (…) is true if the subquery returns at least one row. NOT EXISTS is the opposite. What comes after SELECT in the subquery does not matter, so it is SELECT 1.
Here the subquery looks at the outer row: o.customer_id = c.id, where c is the customer that the outer query is checking right now. This is a new idea, and the next lesson looks at it closely.
See it
NOT IN and a single NULL
The manager is arranging the menu of the website and asks which categories have no products at all. They expect “Food” and “Home”: those hold only subcategories. Here is the first attempt:
This is the trap from the NULL lesson, but now NULL comes from the subquery and cannot be seen in the query. NOT EXISTS compares no values: it asks whether there is such a product, and NULL does not get in its way:
When the subquery is over a column that can be NULL, use NOT EXISTS or add WHERE category_id IS NOT NULL to the subquery.
Exercise
Two tasks
1. The quality team wants to call the customers who have at least one cancelled order.
Show the names of the customers with at least one cancelled order.
Solution
SELECT name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id AND o.status = 'cancelled'
);WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'cancelled'). Seven customers. It also works with id IN (SELECT customer_id FROM orders WHERE status = 'cancelled').
2. Marketing is preparing a “Write a review” campaign for the products nobody has reviewed yet.
Show the names of the products without a single review (the reviews table).
Solution
SELECT name FROM products p
WHERE NOT EXISTS (SELECT 1 FROM reviews r WHERE r.product_id = p.id);WHERE NOT EXISTS (SELECT 1 FROM reviews r WHERE r.product_id = p.id). 12 products, the gift card among them.
Test