Order of execution of the SELECT statement and its clauses in the MySQL statement

Logical query processing steps for the execution of the SELECT statement:
(8) SELECT (9) DISTINCT
(one) <TOP_specification> <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) with {CUBE | ROLLUP}
(7) Having (10) ORDER by <order_by_list>

1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP by
6.WITH CUBE or with ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER by
11.TOP

Microsoft points out that the actual physical execution order of the SELECT statement may differ from the order of the query processor.

Each step produces a virtual table that is used as input to the next step. Only the table that is generated in the last step is returned to the caller.
If there is no clause, skip the appropriate step.
1. From: Performs a Cartesian product on the first two tables in the FROM clause, generating the virtual table VT1.
2. On: Apply the on filter to the VT1. Only those lines that make <join_condition> true are inserted into the VT2.
3. OUTER (Join): If OUTER JOIN is specified, a matching row in the reserved table is added to VT2 as an outer row to generate VT3.
If the FROM clause contains more than two tables, repeat steps 1 through 3 for the result table and the next table generated by the previous join until
All tables are processed.
4. Apply the where filter to the VT3. Only rows that make <where_condition> true are inserted into the VT4.
5. GROUP BY: Groups the rows in VT4 by the list of columns in the GROUP BY clause, generating VT5.
6. cube| ROLLUP: Inserts a super-group into the VT5 to generate VT6.
7. Having: Apply a having filter to VT6. Only groups that make 8. Select: Processes the select list, generating VT8.
9. DISTINCT: Remove duplicate rows from VT8, resulting in VT9.
ORDER BY: The rows in VT9 are sorted by the list of columns in the ORDER by clause, and a table (VC10) is generated.
TOP: Selects the specified number or scale of rows from the beginning of the VC10, generates the table VT11, and returns it to the caller.

Example one:

SELECT Id,count (ID) as Total

From STUDENT

GROUP by ID

Having total>2

Do you think this SQL statement looks familiar? Yes, a very basic group query. However, it cannot perform successfully because the having execution order is above select.

The actual execution sequence is as follows:

1.FROM STUDENT
2.GROUP by ID
3.HAVING total>2
4.SELECT Id,count (ID) as Total
It is clear that total is the new alias generated after the last sentence of select Id,count (ID) as Total is executed. Therefore, total is not recognized when the having TOTAL>2 executes.

Example Two

 
SELECT Id,count (ID) as Total

From STUDENT

GROUP by ID

ORDER by Total

The actual order of execution for this is:

1.FROM STUDENT
2.GROUP by ID
3.SELECT Id,count (ID) as Total
4.ORDER by Total
This time there is no problem and can be executed successfully. What if the order by total is replaced by the order by COUNT (ID)?

SELECT Id,count (ID) as Total

From STUDENT

GROUP by ID

ORDER by COUNT (ID)

Actual execution order:

1.FROM STUDENT
2.GROUP by ID
3.SELECT Id,count (ID) as Total
4.ORDER by COUNT (ID)

Yes, it can be executed successfully, looking at the SQL execution plan, which is the same as the order by total above. ORDER by is executed after select, so alias total can be used.

Example Three

SELECT firstname+ ' +lastname as NAME, COUNT (*) as Count

From STUDENT

GROUP by NAME

Actual execution order:

From STUDENT

GROUP by NAME

SELECT firstname+ "+lastname as Name,count (*) as COUNT

Obviously, the alias name was not created when the group by name was executed, so it cannot be executed successfully.

Order of execution of the SELECT statement and its clauses in the MySQL statement