Lesson 9 of 12 · 9 min

Subqueries

A subquery is a query inside brackets, used inside another query. The inner query runs first and its result is used by the outer one.

A single value

When the subquery returns one value, you can compare against it:

SELECT name, joined
FROM customers
WHERE joined = (SELECT MIN(joined) FROM customers);   -- the first customer

This is better than typing the value in yourself: if the data changes, the query still gives the right answer.

A list of values

With IN, a subquery can return a whole column:

SELECT name FROM products
WHERE id IN (SELECT product_id FROM orders WHERE quantity >= 10);

And NOT IN finds the opposite. (Be careful: if the list contains a NULL, NOT IN matches nothing; LEFT JOIN ... IS NULL from the last lesson avoids that trap.)

EXISTS

EXISTS (subquery) is true when the subquery returns at least one row. It is often the clearest way to say "has any":

SELECT name FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);

Your task

Show the name and price of products that cost more than the average price of all products, most expensive first. Work out the average with a subquery.

query.sql
-- Products above the average price

SQLite runs right here in your browser. The first run downloads about 0.7 MB; after that it is instant.

Discussion

Stuck? Ask here. Be kind, and share hints rather than full answers.

Loading the discussion…