SQL and MySQL course Β· Module 8: Subqueries
Subquery in the FROM clause
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. What is a derived table and what does it require in MySQL?
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).