What is the concept of a derived table concerned with MySQL subquery?
Actually, when a MySQL subquery starts at the FROM clause then the result set produced is referred to as a derived table. Such kind of subqueries that produce derived tables are called materialized subquery. It can be understood with the help of the following example which uses the data from ‘cars’ table −
mysql> Select * from Cars;
+------+--------------+---------+
| ID | Name | Price |
+------+--------------+---------+
| 1 | Nexa | 750000 |
| 2 | Maruti Swift | 450000 |
| 3 | BMW | 4450000 |
| 4 | VOLVO | 2250000 |
| 5 | Alto | 250000 |
| 6 | Skoda | 1250000 |
| 7 | Toyota | 2400000 |
| 8 | Ford | 1100000 |
+------+--------------+---------+
8 rows in set (0.02 sec)
Now, the following query will produce a derived table −
mysql> Select AVG(P) FROM(SELECT Name, SUM(Price) P FROM cars GROUP BY Name)AS totals;
+--------------+
| AVG(P) |
+--------------+
| 1612500.0000 |
+--------------+
1 row in set (0.76 sec)
The above-derived table returns the total price each car is having, then the outer query returns the average.
We must have to provide an alias for all derived tables. In this case, we gave our derived table an alias of totals.
If we run only the subquery, we can see the derived table −
mysql> SELECT Name, SUM(Price) P FROM cars GROUP BY Name ;
+--------------+---------+
| Name | P |
+--------------+---------+
| Alto | 250000 |
| BMW | 4450000 |
| Ford | 1100000 |
| Maruti Swift | 450000 |
| Nexa | 750000 |
| Skoda | 1250000 |
| Toyota | 2400000 |
| VOLVO | 2250000 |
+--------------+---------+
8 rows in set (0.03 sec)
Published on 21-Feb-2018 13:40:46
