Microsoft Official: SELECT statement logical processing order

The following steps show the logical processing order or binding order of the SELECT statement. This order determines when the objects defined in one step are available to the clauses in the next step.

For example, if a query processor can bind to (access) a table or view defined in the FROM clause, those objects and their columns are available for all subsequent steps.

Conversely, because the SELECT clause is in step 8, any column aliases or derived columns that are defined in the clause cannot be referenced by the preceding clause. However, they can be referenced by subsequent clauses, such as an ORDER BY clause.

Note that the actual physical execution of the statement is determined by the query processor, so the order in this list may be different.

(1) From

(2) On

(3) JOIN

(4) WHERE

(5) GROUP by

(6) With CUBE or with ROLLUP

(7) Having

(8) SELECT

(9) DISTINCT

(10) ORDER by

(11) TOP

(8) SELECT (9) DISTINCT (one) TOP

(1) From

(3) JOIN (2) on

(4) WHERE

(5) GROUP by

(6) with {CUBE ROLLUP}

(7) Having

(10) ORDER by

For associative operations on tables joined by joins, if a row that does not satisfy the join condition is also within our query, we must put the join condition on the back, not in the where, if we put the join condition behind the where, then all the Left,right, Such operations will have no effect, and in this case, the effect is exactly the same as the inner connection. For those conditions that do not affect the selection of rows, either on or behind.

Remember: All connection conditions must be placed behind, otherwise all left, and right will be used as a device, without any effect.

(8) SELECT (9) DISTINCT

(one) <TOP_specification> <select_list>

(1) from <left_table>

(3) <join_type> join <right_table>

(2) on <join_condition>

(4) WHERE <where_condition>

(5) GROUP by <group_by_list>

(6) with {CUBE ROLLUP}

(7) Having

(ten) ORDER by <order_by_list>

Each step produces a virtual table that is used as input to the next step. Only the table that is generated in the last step is returned to the caller. Such as

If there is no clause, skip the appropriate step.

1. From: Performs a Cartesian product on the first two tables in the FROM clause, generating the virtual table VT1.

2. On: Apply the on filter to the VT1. Only those lines that make <join_condition> true are inserted into the VT2.

3. OUTER (Join): If OUTER JOIN is specified, a matching row in the reserved table is added to VT2 as an outer row to generate VT3.

If the FROM clause contains more than two tables, repeat steps to steps for the result table and the next table generated by the previous join until

All tables are processed.

4. Apply the where filter to the VT3. Only rows that make <where_condition> true are inserted into the VT4.

5. GROUP BY: Groups the rows in VT4 by the list of columns in the GROUP BY clause, generating VT5.

6. Cuberollup: Insert the super-group into the VT5 to generate VT6.

7. Having: Apply a having filter to VT6. Only groups that make

8. Select: Processes the select list, generating VT8.

9. DISTINCT: Remove duplicate rows from VT8, resulting in VT9.

ORDER BY: The rows in VT9 are sorted by the list of columns in the ORDER by clause, and a table (VC10) is generated.

TOP: Selects the specified number or scale of rows from the beginning of the VC10, generates the table VT11, and returns it to the caller.

Microsoft Official: SELECT statement logical processing order