Derived table (subquery) with MySQL

MySQL has added support for derived tables (also called subqueries). In very simple terms, a derived table is a virtual table returned from a SELECT statement. This concept is similar to temporary tables, but using derived tables in your SELECT statements is much simpler because they don’t require all the steps that temporary tables do.

The following image illustrates a query that uses a derived table:
 

 

 

Example:

In the example below, we have two tables, called “Clients” and “Orders”, which contain the following data:
 

 

 
Now the following query will produce a derived table:

Select AVG(T) 
FROM(
  SELECT OrderNbr, SUM(Total) T 
  FROM Orders
  GROUP BY OrderNbr
)AS totals;

Output:

+----------------+
|     AVG(T)     |
+----------------+
|     689.582000 |
+----------------+

 
The above derived table returns the total price of each order, then the external query returns the average.

We must provide an alias for all derived tables. In this case, we have given our derived table the alias “totals”.

If we run only the subquery, we can see the result of the derived table.

SELECT OrderNbr, SUM(Total) T 
FROM Orders
GROUP BY OrderNbr;

Output:

+----------+-----------+
| OrderNbr |     T     |
+----------+-----------+
|   9902   |  254.00   |
|   9902   |  300.92   |
|   9902   |  195.33   |
|   9902   |  1953.33  |
|   9902   |  744.33   |
+----------+-----------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More