Subquery in the FROM clause

The next corridor leads us to derived tables. This time the subquery will not land in

WHERE

, but in the FROM clause - and it will serve as ready material from which the outer query draws further.

Derived table (subquery in FROM)

A subquery placed in

FROM

acts like a temporary table - we call it a derived table. In MySQL it must be given an alias:

1SELECT AVG(liczba) AS srednia_ksiazek
2FROM (
3  SELECT autor_id, COUNT(*) AS liczba
4  FROM ksiazki
5  GROUP BY autor_id
6) AS zestawienie;

The inner query counts each author's books. The outer query treats that result like an ordinary table (

zestawienie

) and computes the average from it. Without the alias

AS zestawienie

the Library reports an error.

Why a derived table?

A derived table lets you first prepare the data, and only then process it further. We use it most often when we want to:

• compute an aggregate from another aggregate (e.g. the average of counters),
• filter the result of a grouping and then process it further,
• give temporary names to columns referenced by the outer query.

Remember the alias

In MySQL every derived table must have an alias - even if the outer query does not use it. A forgotten alias is the most common mistake in this corridor. Always give one, e.g.

AS zestawienie

or

AS t

.

@name