Detailed explanation of SQL execution order in MySQL

The execution order of SQL in MySQL

In daily development work, we often handwritten some SQL statements by ourselves, but how do these SQL statements be executed and what is the order of execution? I believe that all the big guys also understand this, so if you have a certain understanding of the execution order of the SQL language, you will better understand some SQL statements, so as to better write SQL statements, and also help tuning SQL. For example, first use subqueries to filter the data, then perform join operations or directly use join operations, and then perform data filtering.

SQL execution order

Below is a SQL statement that we often use and often write.

select distinct
    Query list（Fields to be checked）,
    max(), avg().... Aggregation function
from
    The tables on the lefts
Connection type(left|inner) join 
    The tables on the rights
on 
    Connection conditions
where
    Filter criteria
group by
    Grouped list(Which field to group)
having
    having_condition
order by
    Sort fields
limit
	pageSize

The following is the execution order of the SQL statement

Query statements are executed from. During the execution process, each step will generate a virtual table for the next step, which will be used as input to the next step.。

• First, a Cartesian product is performed on the first two tables in the from clause, and a virtual table vt1 is generated (select a relatively small table as the basic table).
• Next is to apply the on filter. The logical expressions in on will be applied to each row in vt1, filter out the rows that satisfy the on logical expression, and generate a virtual table vt2.
• If it is an outer join, then this step will add external rows. left outer jion will add the left table filtered in the second step. If it is a right outer join, then add the rows filtered out in the second step. This will generate a virtual table vt3.
• If there are two more tables in the from clause, then vt3 and the third table are connected to calculate the Cartesian product and generate a virtual table. This process is a repeating step of 1-3, and finally a new virtual table vt3 is obtained.
• Apply where filter, refer to where filter for the virtual table produced in the previous step, and generate virtual table vt4.
• Note the difference between where and on: first execute on, then execute where; on establishes an association relationship and executes when generating temporary tables. Where is filtering the data after the temporary table is generated.
• The group by clause combines the unique values ​​in it 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.
• Apply avg or sum options to generate supergroups for vt5 and generate vt6.
• Apply having filters to generate vt7. Having filters are the first and only filters to be applied to grouped data.
• Process the select clause. Filter out the columns that appear in select in vt7. Generate vt8.
• Apply distinct clause to deduplicate vt8 and generate vt9.
• Apply the order by clause. Sort vt9 by order_by_condition, and a cursor returned at this time, not a virtual table.
• Apply limit option. Generate vt10 return result to the requester, that is, the user.

MySQL execution order

SELECT statement definition

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

<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 a SELECT statement, the order of clauses is fixed. For example, the GROUP BY clause will not be preceded by the WHERE clause.

SELECT statement execution order

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

Start -> FROM clause -> WHERE clause -> GROUP BY clause -> HAVING clause -> SELECT clause -> ORDER BY 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.

After comparing, the execution order of MySQL and SQL is basically the same, and the standard order SQL statements are:

select empName, max(salary) as maxSalary 
from t_emp 
where empName is not null 
group by empName 
having max(salary) > 2000 
order by maxSalary

In the above example, the execution order of SQL statements is as follows:

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

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

(3). Execute the GROUP BY clause and group the t_emp table by empName column

(Note: You can only use the alias in select at the beginning of this step. It returns a cursor, not a table, so you cannot use the alias in select in where, but having can use it)

(4). Calculate the max() aggregation function, and press maxSalary to find some of the largest numerical values ​​in the total salary

(5). Execute the HAVING clause to filter employees whose total salary is greater than 2,000.

(7). Execute the ORDER BY clause and sort the last results by maxSalary.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.