Detailed analysis of SQL Execution steps and SQL Execution steps
Detailed analysis of SQL Execution steps
First, let's look at the sequence of statements executed.
(8)select (9)distinct A (1)from Ta (3)join Tb (2)on XXX (4)where XXX (5)group by XXX (6)with {cube|roll up} (7)having XXX (10)order by XXX (11)limit XXX
Next, let's take a look at the specific analysis and query processing stages:
- FROM executes Cartesian sets on the left and right tables in the from clause to generate virtual table VT1
- ON performs on filtering ON the virtual table VT1. Only the rows that match the join condition are inserted into the virtual table VT2.
- If an outer JOIN is specified for a join operation, the unmatched rows in the table are retained as external rows and added to the virtual table VT2 to generate the virtual table VT3. If the from clause contains more than two tables, repeat steps 1-1 for the VT3 and next tables in the result table generated by the previous connection ~ Step 3, until all tables are processed.
- WHERE performs where filtering on the virtual table VT3. Only those that meet the conditions are inserted into the virtual table VT4.
- Group by groups records in VT4 Based on the columns in the group by clause to generate VT5.
- CUBE | roll up performs CUBE or ROLLUP operations on table VT5 to generate table VT6.
- HAVING applies the having filter to the virtual table VT6. Only qualified records are inserted into the virtual table VT7.
- For the second SELECT operation, select the specified column and insert it to the virtual table VT8.
- DISTINCT removes duplicate data and generates virtual table VT9.
- Order by sorts the records in virtual table VT9 according to the specified requirements to generate virtual table VT10
- LIMIT extracts records of the specified row, generates the virtual table VT11, and returns the records to the query user.
If you have any questions, please leave a message or go to the community on this site for discussion. Thank you for reading this article. Thank you for your support!