SQL Kitchen

Lesson · Recipe № 012

Subqueries

Lesson 12
Recipe

Subqueries

A subquery is a full SELECT written inside another query's parentheses. SQL runs the inner one first, then uses its result as if it were a value the outer query already knew.

Query

SELECT name, price
FROM wines
WHERE price > (SELECT AVG(price) FROM wines)
ORDER BY price DESC;

↓ result

name       price
Xinomavro  16
Assyrtiko  14

When the inner query returns a whole list instead of one number, compare against it with IN (or NOT IN for the opposite) instead of = or >.

Query

SELECT name, price
FROM wines
WHERE price <= (SELECT AVG(price) FROM wines)
ORDER BY price DESC;

↓ result

name          price
Agiorgitiko   12
Malagousia    11
Moschofilero  10
Mavrodafni    9

A subquery can go anywhere a value or a list is expected: in a WHERE clause comparing against a single number, or feeding IN a whole list of matches pulled from another table entirely.

On the rail · tonight's orders

ORDER 034

for Konstantinos

Konstantinos thinks the wine list has drifted upmarket without him noticing. He wants every wine priced above the cellar's own average, to see how many that really is.

Your query returns

Every wine priced above the average wine price.

+60 XPStart order →
ORDER 035

for Eleni

Eleni wants that same spotlight on her most experienced hands again, every dish cooked at a station led by someone with more than 5 years on the job, but this time without needing to list the staff table in the join.

Your query returns

The name of every dish whose station is led by someone with more than 5 years experience.

+60 XPStart order →
ORDER 036

for Marina

Marina wants to know which dishes have nothing to do with our VIP customers' favorite stations.

Your query returns

The name of every dish whose station isn't a favorite of any VIP customer.

+65 XPStart order →
← Recipe № 011 · Multiple JOINsRecipe № 013 · CTEs →