Execution order of SELECT statements in SQL Server _mssql

Source: Internet
Author: User
Today, when you write a statement, you execute it inside the Query Analyzer
To use the 10s, another way of writing is only less than 1s time, colleagues say because of the sequence of SQL sentence execution reasons. I've seen a little bit before.
Close the book, have a little impression, to find information on the Internet, under study.
Logical query Processing steps
Copy Code code as follows:

(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 (10) ORDER BY <order_by_list>

Each step produces a virtual table that is used as input to the next step. Only the tables that were generated in the last step are returned to the caller. Such as
If a clause is not in effect, skip the appropriate step.
1. From: Performs a Cartesian product on the first two tables in the FROM clause to generate a virtual table VT1.
2. On: Apply on filter to VT1. Only those rows that make <join_condition> true are inserted into the VT2.
3. OUTER (Join): If a OUTER JOIN is specified, the matching row in the reserved table is added to the VT2 as the outer line, generating VT3.
If the FROM clause contains more than two tables, repeat steps 1 through 3 for the resulting table and the next table generated by the last join until
Finish processing all the tables.
4. Apply a where filter to the VT3. Only rows that make <where_condition> true are inserted into the VT4.
5. GROUP BY: Groups the rows in the VT4 by the list of columns in the GROUP BY clause, generating VT5.
6. cube| ROLLUP: Insert the super group into the VT5 and generate the VT6.
7. Having: Applying having a filter to VT6. Only groups that make 8. Select: Process the select list to produce VT8.
9. DISTINCT: Remove duplicate rows from VT8, resulting in VT9.
Orders by: Sorts the rows in the VT9 by the column list in the ORDER BY clause, generating a table (VC10).
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.
This is supplemented by other netizens:
As if you were writing an SQL statement because you did not know the order in which each keyword was executed, often organized SQL statements lack of good logic, with the feeling of "patchwork" (sorry, if your SQL statements are often "patchwork", then you have to reflect on it?
This is really cool yourself, but the machine, the server also needs to be in our messy SQL statement to find the next sentence to execute the keyword where.
Efficiency, because our sensory nerves are not sensitive to the changes below the second, consider that the SQL order is irrelevant, "no change anyway!", hehe. In fact, the server for each SQL parsing time will have a detailed record, you can look at the custom written by the SQL and in the standard order of sq L Analytic time difference is not how big.
Therefore, it is recommended that you work in peacetime SQL statements in accordance with the standard order to write, one is professional, the second is practical, hehe, but I think the most important thing is to feel comfortable in the heart.
The parsing order of the standard SQL is:
(1). FROM clause, assembling data from different data sources
(2). WHERE clause to filter records based on specified criteria
(3). GROUP BY clause, dividing data into multiple groupings
(4). Using Aggregate functions for calculation
(5). Using the HAVING clause to filter groups
(6). Calculation of all expressions
(7). To sort the result set by using ORDER by
For example: In the Student performance table (Tb_grade), the "candidate name" content is not empty records in accordance with the "candidate name" group, and screening group results, selected "Total score" greater than 600 points.
The standard sequence of SQL statements is:
Select Candidate Name, Max (total score) as Max Total
From Tb_grade
Where candidate name is not NULL
Group BY candidate name
Having Max (total score) > 600
ORDER by Max Total Score
In the example above, the SQL statements are executed in the following order:
(1). First execute FROM clause, assemble data source data from Tb_grade table
(2). Executes a WHERE clause to filter all data in the Tb_grade table that is not NULL
(3). Execute GROUP BY clause, Group Tb_grade table by "Student name" column
(4). Compute the MAX () aggregate function and find out the largest number in the total score by "Total Score"
(5). To perform a HAVING clause, the total score of the course is greater than 600.
(7). Execute the ORDER BY clause to sort the final result by "Max score".
Well, after reading these, I'm sure you all know the order of execution of the SELECT statements in SQL. Ha ha!
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.