Order of execution of SQL statements

(1) from
(3) JOIN
(2) on
(4) WHERE
(5) GROUP by (Start using aliases in Select, which can be used in subsequent statements)
(6) AVG, SUM....
(7) having
(8) SELECT
(9) DISTINCT
(Ten) ORDER by

First step: A Cartesian product is first performed on the first two tables in the FROM clause, at which time the virtual table vt1 is generated (selecting a relatively small table for the base table)
The next step is to apply the on filter, and the logical expression in on is applied to each row in the VT1, filtering out the rows that satisfy the on logical expression, and generating the virtual table VT2
The third step: if it is a outer join then this step will add the outer row, left outer jion to the left table in the second step to add the filter, if it is right outer join then you will be in the second step to add a row to filter out the table, so that the virtual table is generated VT3

Fourth step: If the number of tables in the FROM clause is more than two tables, then concatenate the VT3 and the third table to compute the Cartesian product and generate the virtual table, which is a repeat 1-3 step, and finally get a new virtual table vt3.

Fifth step: Apply the Where filter, refer to the where filter for the virtual table produced in the previous step, generate the virtual table VT4, there is a more important detail to say, for the query containing the OUTER JOIN clause, there is a confusing question, Do you want to specify a logical expression on the on filter or the where filter? The biggest difference between on and where is that if you apply a logical expression in on, then in the third step of the outer join you can add the removed row back again, and where the end of the removal. To give a simple example, there is a student table (class, name) and a score table (name, score), I now need to return to an X class of all the students, but there are several students in this class is missing, that is, in the score table is not recorded. In order to get the results we expect we need to specify the relationship between the student and the score table in the ON clause (student. Name = score. Name) then do we find that in the second step, the student records without taking the exam will not appear in VT2 because they are filtered out by the logical expression on. But we can use the left outer join to get back the students who are not in the exam, because we want to go back to all the students in the X class, if we apply the student to On. class = ' x ', leave outer Join will retrieve all the student records of Class X (thanks to the Netizen Concin for Concin), so the students can only be applied in the where filter. class = ' x ' because its filtering is final.

Sixth step: The GROUP BY clause combines the unique values in a group into a virtual table vt5. If group by is applied, then all subsequent steps can only get the VT5 column or aggregate function (count, SUM, AVG, and so on). The reason is that the final result set contains only one row for each group. Please keep this in mind.

Seventh step: Use the cube or Rollup option to generate a super-group for VT5 and generate VT6.
Eighth step: Apply the having filter to generate VT7. Having a filter is the first and only filter that is applied to the grouped data.
Nineth Step: Process the SELECT clause. Filter the columns in Vt7 that appear in select. Generate Vt8.

Tenth step: Apply the DISTINCT clause, remove the same row in the Vt8, and generate the Vt9. In fact, if the GROUP BY clause is applied then the distinct is redundant, because the same is true when grouping the unique values in a column into a group and returning only one row of records for each group, so the records will be different.

11th Step: Apply the ORDER BY clause. Sort Vt9 by Order_by_condition, at which point a cursor is returned instead of a virtual table. SQL is a collection-based theory, and the collection does not pre-order his rows, it is only a logical collection of members, and the order of the members is irrelevant. A query that sorts a table can return an object that contains the logical organization of a particular physical order. This object is called a cursor. Because the return value is a cursor, a query using an ORDER BY clause cannot be applied to a table expression. Sorting is very cost-wise, unless you have to sort, it is best not to specify an order by, and finally, in this step is the first and only step that can use aliases in the select list.

12th step: Apply the top option. The result is returned to the requestor, the user.

Doubts about the Blue font part

SQL query statement processing steps, flowchart

Order of execution of SQL statements