What is a derived table, and what can you do with one that you cannot do with a plain subquery?
A derived table is a subquery in the FROM clause. You give it a name, and then you can use it like any other table.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
SELECT totals.customer_id, totals.total
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) AS totals
WHERE totals.total > 100
ORDER BY totals.total DESC;
So what can you do with one that you cannot do with a plain subquery? Three things.
The first one is that you can group by a column that it computed. The derived table works out the value first, and then the outer query groups by it as if it were an ordinary column of a table.
The second one is that you can join with it and with other tables, all in one query. A subquery in the select list cannot be joined with.
The third one is that you can filter it, sort it and add a limit to it, all in the outer query, without changing the subquery itself.
There are two rules that you have to follow. It needs an alias, so that it has a name to be referred to by. And the columns that come out of it keep the names that the inner query gave them, so the inner query should give its columns clear names.
The next question in this topic shows the first of those three abilities.