Execution sequence of select statements

I borrowed a paragraph from the SQL Server 2005 Technology Insider: T-SQL query by Itzik Ben-Gan, lubor Kollar, Dejan sarka to explain:

 
(8) Select (9) distinct (11) <top_specification> <select_list> (1) from <lef t_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 (having_condition) (10) order by <order_by_condition>
 

From this order, we can see that all the query statements are executed from. During execution, each step generates a virtual table for the next step, which serves as the basis for the next step.

Step 1: From

First, a Cartesian product is executed for the first two tables in the from clause. In this case, a virtual table vt1 is generated.

Step 2: On

The next step is to apply the on filter. The logical expression in on will apply to each row in vt1, filter the rows that meet the on logical expression, and generate the virtual table VT2.

Step 3: Join

For outer join, the external row is added in this step, and left outer jion adds the left table filtered in step 2, if right outer join is used, add the rows filtered out by the right table in step 2 to generate the virtual table vt3.

Step 4: Multi-table

If the number of tables in the from clause is more than two tables, connect vt3 to the third table to calculate the Cartesian Product and generate a virtual table. This process repeats steps 1-3, finally, a new virtual table vt3.

Step 5: Where

Apply the where filter and reference the where filter to the virtual table produced in the previous step to generate the virtual table vt4. In this case, we have to explain the important details. For queries that contain the Outer Join clause, there is a confusing question: In the on filter or use the where filter to specify the logical expression? The biggest difference between on and where is that if a logical expression is applied to on, you can add the removed row again in step 3 outer join, and the final result of Where is removed.

Step 6: group

Group to generate virtual table vt4

Step 7: having

Apply having filter to vt4 to generate virtual table vt5

Step 8: select

Process the select list and generate a virtual table vt6

Step 9: distinct

Remove duplicate rows in vt6 to generate virtual table vt7

Step 10: Order

Sort the rows in vt7 by column list in the order by clause to generate a cursor vc8

Step 2: Top

Select a specified number or proportion of rows from the beginning of vc8, generate the virtual table vt9, and return it to the caller

==========================================

The complete execution sequence of the SQL SELECT statement:

1And from clause to assemble data from different data sources;
2The where clause filters record rows based on specified conditions;
3,GroupBy clause divides data into multiple groups;
4Use Aggregate functions for computing;
5Use the having clause to filter groups;
6Calculate all expressions;
7Use order by to sort the result set.

==========================================

Iii. SQL statement Extension

1. select1.1 selective insert statement 1.1.1 insert into Table1 (field1) Select field2 from Table2

Table 1 must exist.

1.1.2 select field1 into Table1 from Table2

If table 1 does not exist, a table named Table1 and field named field1 will be automatically created at runtime.

1.2 open other data sources

/* Oraclesvr is the name of the linked server. In this example, an Oracle database alias named orcldb has been created. */

Exec sp_add1_server 'oraclesvr ', -- connection server name oraclesvr, sysname type

'Msdaora ', -- provide the provider_name Data SourceProgram, Which is Oracle

'Orcldb' -- Data Source Name

Go

Select * From openquery (oraclesvr, 'select name, ID from Joe. Titles ')

If there are multiple SQL Server instances:

Select * from [servername \ InstanceName.] pubs. DBO. authors.

Note: The complete name of an object includes four identifiers: Server Name, database name, owner name, and Object Name. The format is as follows:

[[[Server.] [database].] [owner_name].] object_name

The name in the middle can be omitted, but the name in the middle cannot be omitted. For example, server... Object_name

2. update2.1 multi-Table update

Update Table1 set table1.field 1 = table2.field2 from

Table1, Table2/* Guess that the join mode is fully connected. Full [outer] Join */

Where table1.field3 = Table2. filed3

Knowledge: The from statement in the SQL Server Update statement can be followed by multiple tables. Oracle does not support this usage.

In ORACLE: Update Table1 set table1.field1 =

(Select table2.field2 from Table2 where. field3 = Table2. filed3)

3. Specification of insert3.1 insert statement

In SQL Server 2000 and SQL Server 2005

Standard statement: insert into table (field) values (value)

Tip: the access statements are incorrect because the SQL statements are not standardized. Therefore, you must follow the regular syntax when writing SQL statements.

4. e4.1 standard Deletion

Standard statement: delete from table where Condition

Tip: Same as insert

4.2 Delete other 4.2.1 truncate

Syntax: truncate table table_name

Deleting all rows in the table does not record the deletion operation of a single row, but does not record logs. The speed is faster than that of Delete.

4.2.2 drop

Statement: Drop table table_name

Delete tables and related tables. If you have an FK constraint, You cannot delete the tables. The system tables cannot be used last year.

5. Order

Function: Sort

Tip: Order by newid () random sorting