Shulou(Shulou.com)06/01 Report--

The following content mainly brings you SQL implementation order analysis, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

1. Handwritten SQL sequence

Select from join on where group by having order by limit

II. MySql execution sequence

From on join where group by having select distinctorder by limit

III. Understanding of MySql execution sequence

Step 1: load the first two tables of the from clause to calculate the Cartesian product and generate the virtual table vt1

Step 2: filter the data of the associated table that conforms to the on expression, keep the main table, and generate the virtual table vt2

Step 3: if you are using an external join, when you execute on, the data in the main table that does not meet the on condition will also be loaded as external rows.

Step 4: if the number of tables in the from clause is greater than 2, repeat steps 1 through 3 until all the tables are loaded, update vt3

Step 5: execute the where expression to filter out the data that does not meet the criteria to generate vt4

Step 6: execute the group by clause. After the group by clause is executed, the clause is combined into a unique value and contains only one row for each unique value, generating vt5. Once the group by is executed, all the steps that follow can only get the columns in the vt5 (columns contained in the group by clause) and aggregate functions.

Step 7: execute the aggregate function to generate vt6

Step 8: execute the having expression to filter the data in vt6. Having is the only conditional filter after grouping to generate vt7

Step 9: filter columns from vt7 to generate vt8

Step 10: execute distinct, remove the weight of vt8, and generate vt9. In fact, after the execution of group by, there is no need to execute distinct, because after grouping, each group will have only one piece of data, and each piece of data is different.

Step 11: sort the vt9. Instead of a virtual table, you return a cursor that records the sort order of the data. Aliases can be used here.

Step 12: execute the limit statement and return the result to the client

IV. Other

1. What is the difference between on and where?

To put it simply, when there is an externally related table, on mainly filters the externally related table, and the main table is retained. When there is no associated table, the two have the same effect.

For example, in the case of the left outer join, on is executed first to filter out the data in the joined table that does not conform to the on expression, while the filtering of where is the filtering of the main table.

2. Illustration

For the above analysis on the execution order of SQL, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.