MySQL derived table (Derived Table) simple usage example analysis

This article uses an example to describe the simple usage of MySQL derived tables. Share with you for your reference, the details are as follows:

Regarding this derived table, we first need to know that the derived table is a virtual table returned from the select statement. A derived table is similar to a temporary table, but using a derived table in a SELECT statement is much simpler than using a temporary table because it does not require the step of creating a temporary table. So when a standalone subquery is used in the FROM clause of a SELECT statement, we call it a derived table. Without further ado, let's explain in detail:

SELECT 
  column_list
FROM
* (SELECT 
* column_list
* FROM
* table_1) derived_table_name;
WHERE derived_table_name.column > 1...

The places marked with an asterisk are where derived tables are used. To explain this in more detail, let's look at a specific example. Next, we need to obtain the top 5 products with the highest sales revenue in 2018 from the orders table and orderdetails table in the database. Let’s first look at the fields in the following table:

Let's first look at the following sql:

SELECT 
  productCode, 
  ROUND(SUM(quantityOrdered * priceEach)) sales
FROM
  orderdetails
    INNER JOIN
  orders USING (orderNumber)
WHERE
  YEAR(shippedDate) = 2018
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;

This SQL statement uses the orderNumber field shared by the two tables as the node for the joint query. After that, it uses time as the condition and the productCode field as the grouping basis. After that, it obtains the grouping field and the alias field after calculation, and then uses the sales field as the sorting basis, and finally extracts the first five results. That's probably it. We can regard the completed result set as a temporary table or something else. Let's take a look at the result set:

+-------------+--------+
| productCode | sales |
+-------------+--------+
| S18_3232 | 103480 |
| S10_1949 | 67985 |
| S12_1108 | 59852 |
| S12_3891 | 57403 |
| S12_1099 | 56462 |
+-------------+--------+
5 rows in set

That's it. Since we are learning about derived tables, we can of course use the result of this query as a derived table and associate it with the products table. The structure of the products table is as follows:

mysql> desc products;
+--------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| productCode | varchar(15) | NO | PRI | | |
| productName | varchar(70) | NO | | NULL | |
| productLine | varchar(50) | NO | MUL | NULL | |
| productScale | varchar(10) | NO | | NULL | |
| productVendor | varchar(50) | NO | | NULL | |
| productDescription | text | NO | | NULL | |
| quantityInStock | smallint(6) | NO | | NULL | |
| buyPrice | decimal(10,2) | NO | | NULL | |
| MSRP | decimal(10,2) | NO | | NULL | |
+--------------------+---------------+------+-----+---------+-------+
20 rows in set

Now that we have understood the table structure, let's look at the following sql:

SELECT 
  productName, sales
FROM
# (SELECT 
# productCode, 
# ROUND(SUM(quantityOrdered * priceEach)) sales
# FROM
#orderdetails
# INNER JOIN orders USING (orderNumber)
# WHERE
# YEAR(shippedDate) = 2018
# GROUP BY productCode
# ORDER BY sales DESC
# LIMIT 5) top5_products_2018
INNER JOIN
  products USING (productCode);

The # part above is the SQL we had before. I marked it with # to make it easier for everyone to understand. Please don’t use it when you write. Now let's take a look at what this sql means. It just treats the part marked with # as a table and does a simple joint query. However, this table, which we call a derived table, will be cleared immediately after use, so we can consider using it when simplifying complex queries. Without further ado, let's take a look at the result set:

+-----------------------------+--------+
| productName | sales |
+-----------------------------+--------+
| 1992 Ferrari 360 Spider red | 103480 |
| 1952 Alpine Renault 1300 | 67985 |
| 2001 Ferrari Enzo | 59852 |
| 1969 Ford Falcon | 57403 |
| 1968 Ford Mustang | 56462 |
+-----------------------------+--------+
5 rows in set

Then, let’s briefly summarize:

• First, the subquery is executed to create a result set or derived table.
• Then, an outer query joins the top5_products_2018 derived table with the products table on the productCode column.

That’s it. This is the end of our understanding and use of simple derived tables. Let’s try a slightly more complicated one. First, assume that customers in 2018 must be divided into three groups: platinum, white gold, and silver. In addition, we need to know the number of customers in each group, as follows:

• Customers with a total order amount greater than 100,000 are Platinum customers;
• Gold customers are those with a total order amount of 10,000 to 100,000
• Customers with a total order amount of less than 10,000 are silver customers.

To build this query, first, we need to put each customer into the corresponding group using a case expression and a group by clause, as shown below:

SELECT 
  customerNumber,
  ROUND(SUM(quantityOrdered * priceEach)) sales,
  (CASE
    WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
    WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
    WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
  END) customerGroup
FROM
  orderdetails
    INNER JOIN
  orders USING (orderNumber)
WHERE
  YEAR(shippedDate) = 2018
GROUP BY customerNumber 
ORDER BY sales DESC;

Let's look at an example of a result set:

+----------------+--------+---------------+
| customerNumber | sales | customerGroup |
+----------------+--------+---------------+
| 141 | 189840 | Platinum |
| 124 | 167783 | Platinum |
| 148 | 150123 | Platinum |
| 151 | 117635 | Platinum |
| 320 | 93565 | Gold |
| 278 | 89876 | Gold |
| 161 | 89419 | Gold |
| ************many data is omitted here*********|
| 219 | 4466 | Silver |
| 323 | 2880 | Silver |
| 381 | 2756 | Silver |
+----------------+--------+---------------+

Now we can use the table obtained from the above query as a derived table to perform associated queries and group them to get the desired data. Let's take a look at the following SQL to get a feel for it:

SELECT 
  customerGroup, 
  COUNT(cg.customerGroup) AS groupCount
FROM
  (SELECT 
    customerNumber,
      ROUND(SUM(quantityOrdered * priceEach)) sales,
      (CASE
        WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
        WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
        WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
      END) customerGroup
  FROM
    orderdetails
  INNER JOIN orders USING (orderNumber)
  WHERE
    YEAR(shippedDate) = 2018
  GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;

I believe that you are all smart people who understand what it means better than me, so I won’t elaborate on it. After finishing, let’s look at the result set:

+---------------+------------+
| customerGroup | groupCount |
+---------------+------------+
| Gold | 61 |
| Platinum | 4 |
| Silver | 8 |
+---------------+------------+
3 rows in set

Okay, let's stop here.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.