SQL and MySQL course Β· Module 8: Subqueries

Subquery in the FROM clause

2 min read Β·
In this lesson3

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:

SELECT AVG(liczba) AS srednia_ksiazek
FROM (
  SELECT autor_id, COUNT(*) AS liczba
  FROM ksiazki
  GROUP BY autor_id
) 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.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What is a derived table and what does it require in MySQL?

  2. 2. What happens in MySQL if a derived table in the FROM clause is not given an alias?

Hands-on tasks in the game

  • Vertical ordering

    Arrange (top to bottom) the lines of the query computing the average number of books per author from a derived table:

  • Code editor

    Write a query that computes the average (AVG) number of books per author. In the FROM clause place a subquery that returns autor_id and COUNT(*) AS liczba from the ksiazki table, grouped by autor_id. Give this derived table the alias zestawienie. In the outer query compute AVG(liczba).

  1. QuestionDerived table
  2. QuestionMissing derived table alias
  3. Put in orderAn average from a derived table
  4. Coding taskAverage number of books per author

Useful articles