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)

Sai Subramanyam

Updated on 30-Jul-2019 22:30:21

• Related Questions & Answers
• What is the use of comparison operators with MySQL subquery?
• What is the concept of CTAS (CREATE TABLE AS SELECTED) in MySQL?
• What is the concept of thread?
• What is the concept of Divestiture?
• What is the concept of hedging?
• Get second largest marks from a MySQL table using subquery?
• What kind of output is returned by MySQL scalar subquery? What are the restrictions on using it with MySQL query?
• What is the concept of Monolithic kernel?
• What is the concept of Rolling Settlement?
• What is a Concept Hierarchies?
• How can we filter data with the help of MySQL subquery?
• What is the concept of datagram packet switching?
• What is the concept of system call mechanism?
• What is the concept of interest rate swaps?
• How can we create a MySQL view with a subquery?