Analysis of Statement Execution Order of sql and MySQL

One question today is whether as alias can be used in insert, into and update in mysql and delete statements. At present, I am still looking at it, but I found some useful knowledge when looking up the data. I will share it with you, that is, the execution sequence of sql and MySQL statements:
sql and mysql execution sequence, found that the internal mechanism is a kind of. The biggest difference is in the reference of aliases.

1. sql execution sequence

(1)from

(2) on

(3) join

(4) where

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

(6) avg,sum....

(7)having

(8) select

(9) distinct

(10) order by

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

Step 1: First perform a Cartesian product of the first two tables in the from clause and generate the virtual table vt1 (select the 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, and the rows satisfying the logical expression in on will be filtered out to generate the virtual table vt2

Step 3: If it is outer join, then external rows will be added in step 1, left ES80jion will add the rows filtered in step 2 in the left table, and if it is right outer join, then the rows filtered in step 2 in the right table will be added, thus generating virtual table vt3

Step 4: If there are more than two tables in the from clause, join vt3 with the third table to calculate Cartesian product and generate virtual table. This process is a repeated step 1-3, and finally a new virtual table vt3 is obtained.

Step 5: Apply where filter, reference where filter to the virtual table produced in step 1, and generate virtual table vt4. There is an important detail to be said here. For the query containing outer join clause, there is a confusing problem. Do you specify logical expressions in on filter or where filter? The biggest difference between on and where is that if the logical expression is applied in on, the removed row can be added back again in step 3 outer join, and the final removal of where. To give a simple example, there is a student table (class, name) and a grade table (name, grade). Now I need to return the grades of all the students in an x class, but several students in this class are absent from the exam, which means there is no record in the grade table. In order to get the results we expect, we need to specify the relationship between the student and the grade table in the on clause (student. name = grade. name). So do we find that when performing step 2, For students who did not take the exam, the records will not appear in vt2. Because they are filtered out by the logical expressions of on, But we can use left outer join to get back the students in the left table (students) who didn't take the exam. Because we want to return all the students in x class, if we apply students in on. Class = 'x', left outer join will retrieve all the student records in x class (thanks to the correction of netizen Kang Qinmou __ Kang Qinmiao), so we can only apply students in where filter. Class = 'x' because its filtering is final.

Step 6: The group by clause combines the values of the only ones in vt5 into a group, resulting in the virtual table vt5. If group by is applied, all subsequent steps will only result in columns of vt5 or aggregate functions (count, sum, avg, and so on). The reason is that the final result set contains only 1 row for each group. Please keep this point in mind.

Step 7: Apply the cube or rollup options to generate a supergroup for vt5 and generate vt6.

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

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

Step 10: Apply the distinct clause and remove the same row from vt8 to generate vt9. In fact, if the group by clause is applied, then distinct is redundant, because when grouping, the only 1 values in the column are divided into 1 groups, and only one row of records is returned for each group, so all records will be different.

Step 101: Apply the order by clause. Sort vt9 by order_by_condition, which returns 1 cursor instead of a virtual table. sql is based on the theory of sets. A set does not sort its rows in advance. It is only a logical set of members, and the order of members is irrelevant. A query that sorts a table can return 1 object that contains a logical organization of a specific physical order. This object is called a cursor. Queries using the order by clause cannot be applied to table expressions because the return value is a cursor. 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 11 steps that can use aliases in the select list.

Step 102: Apply the top option. At this time, the result is returned to the requester, that is, the user.

2. Execution sequence of mysql

SELECT statement definition

A completed SELECT statement contains several optional clauses. The SELECT statement is defined 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, the GROUP BY clause, etc. are optional.

In an SELECT statement, the order of clauses is fixed. For example, the GROUP BY clause does not precede the WHERE clause.

SELECT statement execution order

The execution order of the subsentences in the SELECT statement is different from the input order of the subsentences in the SELECT statement, so it is not executed from the SELECT clause, but in the following order:

Begin- > 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 produced for the next clause, and if a clause does not exist, it will be skipped

Compared with 1, the execution sequence of mysql and sql is basically 1, and the standard sequence of SQL statements is:

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

In the above example, the SQL statement is 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 are not NULL

(3). Execute the GROUP BY clause and group the tb_Grade tables by "Student Name" column (Note: The alias in select can only be used at the beginning of this step, and he returns a cursor instead of a table, so the alias in select can not be used in where, but having can be used. Thank you for raising this question by netizen zyt1369)

(4). Calculate the max () aggregation function, and find the largest values in the total score according to the "total score"

(5). Execute the HAVING clause and screen the total score of the course greater than 600.

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

I will continue to look for the problems I encounter, and of course I hope that the Great God can teach me diligently.

Summarize

The above is the whole content of the sentence execution sequence analysis of sql and MySQL in this article. Please leave a message to correct your shortcomings. This site will reply to you in time.

Interested friends can refer to: mysql in statement subquery slow optimization skills example, MYSQL subquery and nested query optimization instance analysis, several more important MySQL variables, etc., I hope to help everyone.