Chapter One logical query processing

different points of execution orderA significant difference between SQL Server and other programming languages is thatOrder of processing logic。    It is well known that other programming languages are executed in the order of encoding, but for SQL, the first processed clause is from, and the last one is handled by the SELECT clause, although he appears first in the syntax specification. Each step produces a virtual table, which will be used as input to the next step, the virtual tables obtained during the process are not available, and only the virtual tables generated by the last step are available. logical Query Processing phaseFor the logical query processing of SQL statements, you can use the expression:

Introduction to the Logical query processing phase

2. From : performs a cartesian product (Cartesian product) (cross join) on the first two tables in the FROM clause, generating a virtual table VT1
4. On : apply an on filter to VT1. Only those lines that make <join_condition> true are inserted into the VT2.
6. OUTER (Join): If OUTER join is specified (relative to cross join or (INNER join), the reserved table (preserved table: Left outer join marks the left table as a reserved table, right outer join marks the right table as a reserved table, A full outer join that marks two tables as a reserved table) does not find a matching row to be added to VT2 as an outer row, generating VT3. If the FROM clause contains more than two tables, repeat steps 1 through 3 for the result table and the next table that were generated for the previous join until all the tables have been processed.
8. Where: apply a where filter to VT3. Only rows that make <where_condition> true are inserted into the VT4.
10. GROUP By: groups the rows in VT4 by the list of columns in the GROUP BY clause, generating VT5.
12. cube| ROLLUP: Inserts a Hyper-group (suppergroups) into VT5, generating VT6.
14. Having : apply a having filter to VT6. Only groups that make
16. Select: processes the select list, producing VT8.
18. DISTINCT: removes duplicate rows from VT8, resulting in VT9.
20. ORDER BY: generates a cursor (VC10) by sorting the rows in VT9 by the list of columns in the ORDER by clause.
22. TOP: selects the specified number or scale of rows from the beginning of the VC10, generates the table VT11, and returns the caller.

 Note:Many people do not know about the on and where conditions, and feel that placing the filter on and on the back is the same. Is it really the same? Actually, it's not. Mainly reflected in the following two points:(1)The results are different. We say that the where condition is to filter the results of the final virtual table, while the on condition filters the results of the current virtual table and prepares for the next step.    The number of result records that directly affect the data. (2)efficiency is different. We know that when SQL is connected to a table, it produces a Cartesian product that, regardless of whether it is a inner join or an out-of-left join, can increase the virtual table of the data, and if we filter the data in time, the resulting virtual table will be small. But if we all filter the results in the where condition, then the virtual tables he produces can be very large.     Think about filtering the data in 10 data and filtering the data in 10,000 data, which is more efficient and obvious. Sometimes when I optimize performance, the first step is not to look up indexes or anything. My first step is to optimize SQL writing based on business, and if not, consider the rest. Many times, we say that queries are slow because we have a problem with our writing. So, I usually write a statement, it is possible to filter the data in on the try not to write in the where (provided that the accuracy of the data)  three-valued logicThere are three value cases in a logical expression of sql: True,false and unknown. The three-valued logic is unique to the SQL language, and unknown in SQL typically appears in a logical expression of NULL values. Null The result of comparison with other values is still null.   fixed-value logicThere is a concept of "operation at the same time" in SQL, which means that for the same action statement, all data is unmodified until the execution is complete. Example: The above data is the initial data, the following will be updated operation can be clearly seen in the "Fixed value logic."

Chapter One logical query processing