Detailed analysis of SQL execution steps

Detailed analysis of SQL execution steps

Let's first look at the order in which statements are 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 various stages of query processing:

1. FROM performs a Cartesian set on the left and right tables in the from clause to generate a virtual table VT1
2. ON performs on-filtering on virtual table VT1, and only those rows that meet the join condition are inserted into virtual table VT2
3. JOIN If outer join is specified, the unmatched rows in the retained table are added as outer rows to virtual table VT2, resulting in virtual table VT3. If the from clause contains more than two tables, repeat steps 1 to 3 for VT3 and the next table in the result table generated by the previous connection until all tables are processed.
4. WHERE performs a where filter on virtual table VT3, and only those that meet the conditions are inserted into virtual table VT4.
5. GROUP BY groups the records in VT4 according to the columns in the group by clause to generate VT5.
6. CUBE|ROLL UP Perform CUBE or ROLLUP operation on table VT5 to generate table VT6.
7. HAVING applies the having filter to virtual table VT6, and only records that meet the conditions will be inserted into virtual table VT7
8. SELECT executes the select operation for the second time, selects the specified columns, and inserts them into the virtual table VT8.
9. DISTINCT removes duplicate data and generates a virtual table VT9.
10. ORDER BY Sorts the records in virtual table VT9 according to the specified requirements and generates virtual table VT10
11. LIMIT retrieves the records of the specified row, generates a virtual table VT11, and returns it to the query user

If you have any questions, please leave a message or come to the community to discuss. Thank you for reading and I hope it can help you. Thank you for your support of this site!