Detailed description of SQL statement execution sequence and SQL statement execution sequence

Detailed description of SQL statement execution sequence and SQL statement execution sequence

Most of us do not need to deal with databases when we develop software. Especially for erp development, we have to deal with databases more frequently, because SQL is different from other programming languages, the most obvious feature is the order of processing code. In programming languages with large numbers, the code is processed in the encoding order, but in SQL, the first clause to be processed is the FROM clause. Although the first SELECT statement appears, but it is almost always processed.

Each step generates a virtual table, which is used as the input for the next step. These virtual tables are unavailable to callers (client applications or external queries. Only the table generated in the last step will be returned to the caller. If a clause is not specified in the query, the corresponding steps are skipped. The following is a brief description of each logical step for SQL server 2000 and SQL Server 2005.

(8)SELECT (9)DISTINCT (11)<Top Num> <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 
Introduction to the logical Query Processing Stage
FROM: Perform Cartesian product (Cartesian product) on the first two tables in the FROM clause to generate a virtual table VT1.
ON: Apply the ON filter to VT1. VT2 is inserted only when <join_condition> is true.
OUTER (JOIN): If outer join (compared with cross join or (inner join) is specified, preserved table: The left outer join marks the left table as a reserved table, the right external join marks the right table as a reserved table, and the full external join marks both tables as reserved tables.) unmatched rows are not found and added to VT2 as external rows, generate VT3. if the FROM clause contains more than two tables, repeat steps 1 to 3 for the result table generated by the previous join and the next table until all tables are processed.
WHERE: Apply the WHERE filter to VT3. Vt4is inserted only for rows with <where_condition> true.
Group by: GROUP the rows in VT4 BY the column list in the group by clause to generate VT5.
CUBE | ROLLUP: inserts Suppergroups into VT5 to generate VT6.
HAVING: Apply the HAVING filter to VT6. Vt7is inserted only when SELECT: process the SELECT list and generate VT8.
DISTINCT: removes duplicate rows from VT8 to generate VT9.
Order by: sorts the rows in VT9 BY column list in the order by clause to generate a cursor (VC10 ).
TOP: select a specified number or proportion of rows from the beginning of VC10, generate table VT11, and return to the caller.
Note: Step 10: sort the rows returned in the previous step BY column list in the order by clause, and return the cursor VC10. this step is the first and only step to use the column alias in the SELECT list. Unlike other steps, this step returns a cursor instead of a valid table. SQL is based on the set theory. A set does not sort its rows in advance. It is only a logical set of members, and the order of members is irrelevant. You can return an object for sorting a table, including rows organized in a specific physical order. ANSI calls this object a cursor. Understanding this step is the basis for a correct understanding of SQL.
Because this step does not return the table (but returns the cursor), queries using the order by clause cannot be used as table expressions. Table expressions include views, inline Table value functions, subqueries, derived tables, and shared expressions. The result must be returned to the client application that expects a physical record. For example, the following derived Table query is invalid and an error is generated:
select * from(select orderid,customerid from orders order by orderid) as d
The following view also produces errors.
create view my_viewasselect *from ordersorder by orderid
In SQL, table expressions do not allow queries with an ORDER BY clause, but there is an exception in the T-SQL (applying the TOP option ).
Therefore, remember not to assume any specific sequence for the rows in the table. In other words, do not specify the order by Clause unless you are sure to ORDER the rows. Sorting requires cost. SQL Server needs to perform an ordered index scan or use a sort operator.
Now, this tutorial is over. I believe you have more or less knowledge about the SQL statement execution sequence. For more information, visit the official website of.