A brief understanding of MySQL SELECT execution order

The complete syntax of the SELECT statement is:

(7) SELECT 
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

Note: The sequence number before the syntax is the SELECT execution order

The MySQL SELECT execution order is divided into 10 steps. As marked above, the first operation to be executed is the FROM operation, and the last operation to be executed is the LIMIT operation. Each operation will generate a virtual table, which is used as the input for processing. These virtual tables are transparent to the user, but only the last virtual table will be returned as the result. If a clause is not specified in the statement, the corresponding step will be skipped.

Let's take a closer look at each stage of query processing.

SELECT a.customer_id, COUNT(b.order_id) as total_orders
 FROM table1 AS a
 LEFT JOIN table2 AS b
 ON a.customer_id = b.customer_id
 WHERE a.city = 'hangzhou'
 GROUP BY a.customer_id
 HAVING count(b.order_id) < 2
 ORDER BY total_orders DESC;

1. FORM: Calculate the Cartesian product of the table on the left and the table on the right of FROM to generate the virtual table VT1.

2. ON: Perform ON filtering on virtual table VT1, and only those rows that meet <join-condition> will be recorded in virtual table VT2.

3. JOIN: If OUTER JOIN is specified (such as left join, right join), the unmatched rows in the retained table are added to virtual table VT2 as external rows, generating virtual table VT3.

4. WHERE: Filter the virtual table VT3 based on the WHERE condition. Only records that meet <where-condition> will be inserted into virtual table VT4.

5. GROUP BY: Group the records in VT4 according to the columns in the group by clause to generate VT5.

6. HAVING: Apply having filtering to virtual table VT5, and only records that meet <having-condition> will be inserted into virtual table VT6.

7. SELECT: Execute the select operation, select the specified column, and insert it into the virtual table VT7.

8. DISTINCT: Remove duplicate records in VT7. Generate virtual table VT8.

9. ORDER BY: Sort the records in virtual table VT8 according to <order_by_list> to generate virtual table VT9.

10. LIMIT: Take out the records of the specified row, generate virtual table VT10, and return the result.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.