Subqueries: a query inside a query
Every query so far has stood alone. A subquery is a query inside a query - a
full SELECT wrapped in parentheses and used as a value somewhere else in the
outer query, usually inside WHERE. The database runs the inner query first,
gets back a value, then finishes the outer query as if you'd typed that value
in by hand.
The simplest and most common shape is a scalar subquery: one that returns
exactly one row and one column, so it can stand in for a single number.
(SELECT AVG(amount) FROM orders) computes the average order amount once, and
you can compare every row's amount against it - no JOIN, no GROUP BY,
just a query used as a number.
Same users and orders tables as the join lessons - this one only needs
orders.
Your task: return each order's item and amount where the amount is
greater than the average amount across all orders.
You'll practice:
- Writing a subquery that returns a single value
- Using that value inside a
WHERE comparison