MySQL Select Execution Order

The complete syntax for 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>(Ten) LIMIT<Limit_number>

Description: The ordinal of the preceding syntax is the Select execution order

MySQL's Select execution sequence is divided into 10 steps, as noted above, the first to do is the from operation, the last is to perform a limit operation. Each of these operations produces a virtual table, which acts as a processing input, except that the virtual tables are transparent to the user, but only the last virtual table is returned as a result. If you do not specify a clause in the statement, the corresponding step is skipped.

Let's examine each phase of the query process in detail:

 SELECTA.CUSTOMER_ID,COUNT(b.order_id) astotal_orders fromTable1 asa Left JOINTable2 asb ona.customer_id=b.customer_idWHEREA.city= 'Hangzhou' GROUP  bya.customer_id having Count(b.order_id)< 2 ORDER  byTotal_ordersDESC;

1, form: to the left of the table and the right table to calculate the Cartesian product, generating virtual table VT1.
2, on: The virtual table VT1 on the filter, only those matching the <join-condition> line will be recorded in the virtual table VT2.
3. Join: If a outer join is specified (such as a LEFT JOIN or right join), rows that are not matched in the reserved table are added as outer rows to the virtual table VT2, resulting in a virtual table VT3.
4. Where: the Where condition is filtered on the virtual table VT3. Only records that match <where-condition> will be inserted into the virtual table VT4.
5, GROUP by: According to the column in the GROUP BY clause, the records in VT4 are grouped to produce VT5.
6, having: the virtual table VT5 application has filtering, only the records that match 7. Select: Perform a select operation, select the specified column, and insert into the virtual table VT7.
8, DISTINCT: The VT7 in the record to go to heavy. Generates virtual table VT8.
9. ORDER BY: The records in virtual table VT8 are sorted by <order_by_list>, resulting in virtual table VT9.
10, LIMIT: Take out the records of the specified row, generate the virtual table VT10, and return the results.

detailed execution sequence of SQL logical query statements

MySQL Select Execution Order