Derived tables, ANY & ALL

Query a subquery as if it were a table; compare against a set.

A subquery as a table

A subquery in the FROM clause is a derived table — you query its result like any other table (it needs an alias):

SELECT genre, avg_price
FROM (
  SELECT genre, avg(price) AS avg_price FROM books GROUP BY genre
) g
WHERE avg_price > 15;

ANY and ALL compare a value against every value a subquery returns: - price > ALL (...) — greater than every one - price > ANY (...) — greater than at least one (= ANY is the same as IN)

Both give the intermediate result a name; only the CTE puts that name somewhere the rest of the query can reach more than once.

ALL must beat every value, so it collapses to the largest; ANY need beat only one, so it collapses to the smallest. That is also why = ANY is exactly IN.