We have been paying little attention to the execution sequence of Select statements in SQL, and we are very casual about the use of keywords. As for the efficiency problem, because the data volume in the table is not very large, so I am not very concerned about it.
We have been paying little attention to the execution sequence of Select statements in SQL, and we are very casual about the use of keywords. As for the efficiency problem, because the data volume in the table is not very large, so I am not very concerned about it.
When writing a statement today, execute it in the query analyzer.
It takes less than 10 s to use another method, which is less than 1 s. The colleague said that it is because of the SQL sentence execution sequence. I have seen a few moments before
I was a little impressed with Guan's book. I found some materials on the Internet and learned from it.
Logical Query Process
The Code is as follows:
(8) SELECT (9) DISTINCT
(11)
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP
(6) WITH {CUBE | ROLLUP}
(7) HAVING
(10) ORDER
Each step generates a virtual table, which is used as the input for the next step. Only the table generated in the last step is returned to the caller. For example
If no clause exists, skip the corresponding step.
1. FROM: Perform Cartesian product on the first two tables in the FROM clause to generate a virtual table VT1.
2. ON: Apply the ON filter to VT1. Only those VT2 is inserted only when the row is true.
3. OUTER (JOIN): If outer join is specified, the row that does not match in the table is retained and added to VT2 as an external row to generate VT3.
If the FROM clause contains more than two tables, perform steps 1 to 3 on the result table generated by the previous join and the next table
Until all tables are processed.
4. Apply the WHERE filter to VT3. Only enable A row with the value of TRUE is inserted into VT4.
5. group by: GROUP the rows in VT4 BY the column list in the group by clause to generate VT5.
6. CUBE | ROLLUP: inserts the supergroup into VT5 to generate VT6.
7. HAVING: Apply the HAVING filter to VT6. Only enable If this parameter is set to TRUE, VT7 is inserted to the group.
8. SELECT: process the SELECT list and generate VT8.
9. DISTINCT: Remove duplicate rows from VT8 to generate VT9.
10. order by: sorts the rows in VT9 BY the column list in the order by clause to generate a table (VC10 ).
11. TOP: select a specified number or proportion of rows from the beginning of VC10, generate table VT11, and return it to the caller.
The following is a supplement from other netizens:
It seems that the execution sequence of each keyword is unclear when you write SQL statements, and the SQL statements that you organize often lack good logic. (sorry, if your SQL statements are often "pieced together", do you have to reflect on them ?, Haha ).
This is indeed a good job, but it is difficult for the server to find the keyword to be executed in our disorganized SQL statement.
Efficiency, because our sensory nerves are not sensitive to changes below seconds, we temporarily think that the order of SQL statements we write is irrelevant! ", Huh, huh. In fact, the server will have a detailed record of each SQL parsing time. You can take a look at the time difference between the SQL statements you are used to writing and the SQL statements written in standard order.
Therefore, we recommend that you write SQL statements in the standard sequence during your work. Professional and practical, but I think the most important thing is that you feel comfortable.
The standard SQL parsing sequence is:
(1). FROM clause to assemble data FROM different data sources
(2) The WHERE clause filters records based on specified conditions.
(3) group by clause, which divides data into multiple groups
(4). Use Aggregate functions for Calculation
(5). Use the HAVING clause to filter groups.
(6) Calculate all expressions
(7). Use order by to sort the result set
For example, in the student renewal table (temporarily recorded as tb_Grade), group the records whose "examinee name" is not empty by "examinee name" and filter the grouping results, select a score greater than 600 for the "Total score.
The standard order SQL statement is:
Select candidate name, max (total score) as max total score
From tb_Grade
Where examinee name is not null
Group by candidate name
Having max (total score)> 600
Order by max total score
In the preceding example, the SQL statement execution sequence is as follows:
(1) first, execute the FROM clause to assemble data FROM the tb_Grade table.
(2) execute the WHERE clause to filter data that is not NULL in the tb_Grade table.
(3) execute the group by clause to GROUP the tb_Grade table BY the "Student name" column.
(4) Calculate the max () Aggregation Function and calculate the maximum values in the total score by "total score ".
(5). Execute the HAVING clause to filter course scores greater than 600.
(7) Execute the order by clause to sort the final result BY "Max score.
After reading this, I believe everyone knows the execution sequence of select statements in SQL! Haha!
