Analysis of statement execution order of sql and MySQL

I encountered a problem today: Can I use the as alias in insert into, update, and delete statements in MySQL? I am still looking into it, but I found some useful knowledge while looking up the information, which I would like to share with you, about the execution order of SQL and MySQL statements:
The execution order of sql and mysql shows that the internal mechanisms are the same. The biggest difference is in the reference to the alias.

1. SQL execution order

(1)from

(2) on

(3) join

(4) where

(5) group by (start using the alias in select, which can be used in subsequent statements)

(6) avg,sum....

(7)having

(8) select

(9) distinct

(10) order by

From this sequence, we can easily find that all query statements are executed starting from from. During the execution process, each step will generate a virtual table for the next step, and this virtual table will serve as the input of the next execution step.

Step 1: First, perform a Cartesian product on the first two tables in the from clause to generate a virtual table vt1 (select a relatively small table as the base table)

Step 2: The next step is to apply the on filter. The logical expression in on will be applied to each row in vt1, filtering out the rows that meet the on logical expression and generating the virtual table vt2.

Step 3: If it is an outer join, then this step will add the outer rows. The left outer join adds the rows filtered in the second step from the left table. If it is a right outer join, then the rows filtered out in the second step from the right table are added. This generates a virtual table vt3.

Step 4: If the number of tables in the from clause exceeds two, then vt3 is connected to the third table to calculate the Cartesian product and generate a virtual table. This process is a repetition of steps 1-3 to finally obtain a new virtual table vt3.

Step 5: Apply the where filter and reference the where filter to the virtual table produced in the previous step to generate the virtual table vt4. There is an important detail that must be mentioned here. For queries containing outer join clauses, there is a confusing question: should the logical expression be specified in the on filter or in the where filter? The biggest difference between on and where is that if you apply a logical expression in on, the removed rows can be added back in the third step outer join, while the removal of where is final. To give a simple example, there is a student table (class, name) and a score table (name, score). I now need to return the scores of all students in class x, but several students in this class missed the exam, which means there are no records in the score table. In order to get the expected result, we need to specify the relationship between the student and grade table in the on clause (student.name = grade.name). Then we find that when executing the second step, the student records who did not take the exam will not appear in vt2 because they are filtered out by the logical expression of on. However, we can use left outer join to retrieve the students who did not take the exam in the left table (students), because we want to return all students in class x. If student.class='x' is applied in on, left outer join will retrieve all student records in class x (thanks to netizen Kang Qinmou__Kang Qinmiao for the correction), so we can only apply student.class='x' in the where filter because its filtering is final.

Step 6: The group by clause combines the unique values ​​in into a group to obtain the virtual table vt5. If group by is applied, then all subsequent steps can only obtain vt5 columns or aggregate functions (count, sum, avg, etc.). The reason is that the final result set contains only one row for each group. Please keep this in mind.

Step 7: Apply cube or rollup option to generate supergroup for vt5 and generate vt6.

Step 8: Apply the having filter to generate vt7. The having filter is the first and only filter applied to the grouped data.

Step 9: Process the select clause. Filter out the columns that appear in the select in vt7. Generate vt8.

Step 10: Apply the distinct clause to remove identical rows from vt8 and generate vt9. In fact, if the group by clause is applied, then distinct is redundant. The reason is that when grouping, the unique values ​​in the column are grouped together, and only one row of records is returned for each group, so all records will be different.

Step 11: Apply the order by clause. Sort vt9 by order_by_condition, and return a cursor instead of a virtual table. SQL is based on set theory. A set does not pre-sort its rows. It is just a logical collection of members, and the order of the members is irrelevant. A query that sorts a table can return an object that contains the logical organization of the data in a specific physical order. This object is called a cursor. Because the return value is a cursor, queries using the order by clause cannot be applied to table expressions. Sorting is very costly. Unless you have to sort, it is best not to specify order by. Finally, this step is the first and only step where you can use aliases in the select list.

Step 12: Apply top options. Only then will the result be returned to the requester, i.e. the user.

2. MySQL execution order

SELECT statement definition

A completed SELECT statement contains several optional clauses. The definition of the SELECT statement is as follows:

SQL Code

<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>]

The SELECT clause is required, and other clauses such as the WHERE clause and GROUP BY clause are optional.

In a SELECT statement, the order of clauses is fixed. For example, the GROUP BY clause will not be located before the WHERE clause.

SELECT statement execution order

The execution order of clauses in a SELECT statement is different from the order in which the clauses are entered in the SELECT statement, so the execution does not start from the SELECT clause, but is executed in the following order:

Start -> FROM clause -> WHERE clause -> GROUP BY clause -> HAVING clause -> ORDER BY clause -> SELECT clause -> LIMIT clause -> final result After each clause is executed, an intermediate result will be generated for the next clause to use. If a clause does not exist, it will be skipped.

By comparison, the execution order of mysql and sql is basically the same. The standard order of SQL statements is:

select candidate name, max(total score) as max total score from tb_Grade  
where candidate name is not null  
group by candidate name having max(total score) > 600  
order by max total score

In the above example, the SQL statements are executed in the following order:

(1). First, execute the FROM clause to assemble the data from the data source from the tb_Grade table.

(2) Execute the WHERE clause to filter all data in the tb_Grade table that is not NULL.

(3). Execute the GROUP BY clause to group the tb_Grade table by the "Student Name" column (Note: You can use the alias in the select only from this step on. It returns a cursor, not a table, so the alias in the select cannot be used in the where clause, but it can be used in the having clause. Thanks to netizen zyt1369 for raising this question)

(4) Calculate the max() aggregate function and find the largest values ​​in the total score according to the "total score"

(5). Execute the HAVING clause to filter courses with a total score greater than 600 points.

(7). Execute the ORDER BY clause and sort the final results by "Max score".

I will continue to search for the problems I encountered, and of course I also hope that the masters can teach me.

Summarize

The above is all the content of this article about the analysis of the statement execution order of SQL and MySQL. If there are any shortcomings, please leave a message and the editor will reply to you in time.

Interested friends can refer to: MySQL in statement subquery efficiency optimization tips, MYSQL subquery and nested query optimization example analysis, several important MySQL variables, etc., I hope it will be helpful to everyone.