SQL statement execution sequence

SQL statement execution sequence

Today, we think about the execution sequence of on, where, and having, and think about the execution sequence of the entire SQL statement.

The execution sequence of SQL statements is

(1) from
(2) on
(3) join
(4) where
(5) group by, count, sum, avg
(6) having
(7) select
(8) distinct
(9) order
(10) top

From this order, we can see that all the query statements are executed from. During the execution process, a virtual table is generated for each step, this virtual table is used as the input table for the next step.

1. if multiple tables exist after the from statement, the first two tables are taken out, and the table with a small number of rows is used as the base table. The two tables perform Cartesian product to generate the result table vtb1.

2. Apply the logical expression on to vtb1 to filter the rows that meet the logical expression and generate the result table vtb2.
Generally, the SQL editor requires the use of join and on, because if there is no on, a Cartesian product is generated. If you want Cartesian product as the result, then the on expression can write the 1 = 1 equality to bypass join. on must be matched.

3. if you use outer join, you need to add external rows. left outer jion adds the rows filtered by the left table in step 2, otherwise, add the rows filtered by the right table in step 2 to generate the virtual table v78.
If the number of tables after the from statement is greater than 2, Use vb23. continue to repeat the first three steps to obtain the final number of tables.

4. Apply the logic expression in the where clause to vb23. filter the rows that meet the logic expression and generate the result table vtb4.

5. Combine the unique values in the field after group by into a group to obtain vtb5. Aggregate operations such as count, sum, and avg are performed on the group.

6. Apply the logical expression in having to vtb5 to filter the rows that meet the logical expression and generate vtb6. (Having filter is the only Filter Applied to grouped data)

7. select columns from vtb6. Generate vtb7

8. Based on the distinct statement, remove the same row in vtb7 to generate vtb8. If group by is used, you do not need to use distinct, because all rows in the results of group by are different.

9. Sort vtb8 by the column specified by order_by and generate vtb9. Sorting is resource-consuming. We recommend that you do not use order by unless you have sequence Requirements for the results.

10. Return

 

You can see an image on the Internet to visually display the SQL statement execution sequence.