Order of execution of SQL statements

write in front of the words: sometimes do not understand the SQL statement parts of the execution order, resulting in a deviation in understanding, or when writing SQL statements, it is necessary to understand the order of execution of SQL statements. Can have time to write a simple database, understanding will be more in-depth. Here are some of my understanding, take SQL SERVER2008 as an example, to illustrate.

Let's look at the following simple SQL statement:

1 Select 2 Top ten * from Student4 where age>20

Can this SQL statement be understood in the following two ways?

(1) First select the AGE>20 data from the student table and choose the previous 10 data from the data.

(2) First select the first 10 data from the student table, then choose the age>20 data from the data.

So which is the right one? You might say, it's not easy, it's definitely (1) right. Yes, for such a simple statement, can be seen at a glance, but for a slightly more complex SQL statements or more complex SQL statements, sometimes we do not see at a glance, in order to correctly parse the SQL statement and write the correct SQL statement, it is necessary to understand the SQL statement parts of the order of execution.

OK, let's look at one of the following SQL statements:

SELECT DISTINCT top 1table1.id,count (table1.name) as Namecount from Table1inner join Table2on Table1.id=table2.idwhere Ta Ble1.id<4group by table1.idhaving Table1.id<3order by table1.id Desc

Which Table1 and Table2 is I casually built two very simple tables, in order to explain the convenience, I did not make some with the actual combination of tables, or to understand the business logic, simply the easier the better, after all, the purpose is to illustrate the SQL statement execution order, Table1 and Table2 as follows:

　　　　

Table1 Table2

The following first gives the order of execution of the above SQL statements and then explains them:

(8) (9)  top 1(6)  (1) from Table1(3) inner join Table2(2) on Table1.id=ta Ble2.id(4) where Table1.id<4(5) GROUP by Table1.id(7) had table1.id<3  (ten) ORDER by table1.id Desc　

The red number gives the order of execution:

(1) From: Performs a Cartesian product on Table1 and Table2, which is a combination of rows of two tables, a total of 5*5=25 rows, generating a virtual table VT1

(2) On: Select all the rows from the table1.id=table2.id in VT1 to generate the virtual table VT2.

(3) INNER join: Here is the internal connection, is directly VT2, if it is a outer join, such as a left join, right joins, full join, you also need to follow the rules of the external connection, the VT1 does not match the row to VT2, generate VT3.

(4) Where: Select the Table1.id<4 table in VT3 to VT4 the virtual table.

(5) Group by: grouped according to Table1.id.

(6) COUNT: Performs an aggregate function, selects the number of rows corresponding to the table1.id, and produces the result to the virtual table VT5

(7) Having: Select all the results of table1.id<3 in VT5 to the virtual table VT6

(8) Select: Select the corresponding column in the VT6 to the virtual table VT7

(9) Distinct: Remove duplicate rows from VT7, generate VT8

ORDER BY: The results of VT8 are sorted by table1.id, where a new table VT9 is not generated, but a cursor VC9 is generated.

Top: Selects the specified number of rows from the beginning of the cursor VC9, which is 1 rows, generating the virtual table VT10.

After the above procedure, the final SQL statement returns VT10 to the user.

So later when the SQL statement, you can follow the order of the above SQL statements, read SQL statements can be read in the order above, so that the heart understand.

OK, so long-winded said so much, the last picture bar (really strange and ugly), a picture to win thousands of words, make a summary of their own, but also hope to be helpful to everyone.

Finish

Document Information

• Copyright Disclaimer: Free Reprint-Non-commercial-non-derivative-maintain attribution (Creative Commons 3.0 license)
• This article is for original articles, welcome reprint, follow up this blog will be updated constantly, so please keep this document information.
• This address: http://www.cnblogs.com/wuguanglei/p/4198272.html

Order of execution of SQL statements