A query inside a query

01 / 05

Two goes is a nuisance

Say you want "the orders bigger than average". The way you write now, that takes two queries.

One to get the average, then a second where you type that number in by hand. Every time the number moves you rewrite it, and if you copy it down wrong the result goes wrong with it.

SELECT AVG(quantity) FROM orders;

Result

02 / 05

Wrap it in brackets and drop it in

You can embed another query, wrapped in brackets, inside a query. That is called a subquery (the inner query).

Where you were typing 3 by hand, you just put (SELECT AVG(quantity) FROM orders) instead. Now you can write it without knowing the number.

SELECT * FROM orders  WHERE quantity > (    SELECT AVG(quantity) FROM orders  );

Result

2 | 3 | 2 | 4 | web
3 | 2 | 1 | 4 | shop
9 | 5 | 1 | 6 | shop

03 / 05

The inside runs first

It runs from the inside out. The brackets are worked out first into a single value, and the outside runs using that value.

So from the outside, a subquery is no different from an ordinary value. =, >, < — all your usual comparison operators work on it as they are.

SELECT * FROM orders  WHERE quantity = (    SELECT MAX(quantity) FROM orders  );

Result

9 | 5 | 1 | 6 | shop

04 / 05

The inner query can have conditions too

A subquery is an ordinary query, so you may write a WHERE in it. "Bigger than the average of the app orders", for instance — you can narrow down the yardstick itself.

It does not matter that the inside and the outside look at the same table. They simply run separately.

SELECT * FROM orders  WHERE quantity > (    SELECT AVG(quantity) FROM orders    WHERE channel = 'app'  );

Result

2 | 3 | 2 | 4 | web
3 | 2 | 1 | 4 | shop
5 | 5 | 4 | 3 | web
8 | 2 | 2 | 3 | app
9 | 5 | 1 | 6 | shop

05 / 05

Give back one value

A subquery sitting to the right of = or > should be written to return one row, one column. An aggregate function does that naturally; when it does not, cut it to one row with ORDER BY and LIMIT 1.

Below, the inside works out "the item number of the biggest single order" and the outside pulls only that item's orders. Let us write some.

SELECT * FROM orders  WHERE item_id = (    SELECT item_id FROM orders    ORDER BY quantity DESC    LIMIT 1  );

Result

5 | 5 | 4 | 3 | web
9 | 5 | 1 | 6 | shop