Order of execution of SELECT statements in SQL

Source: Internet
Author: User

It seems that since the writing of the SQL statement is not clear the order of the execution of the various keywords, often the organization of SQL statements lack of good logic, with the feeling of "patchwork" (sorry, if your SQL statements are often "pieced together", then you have to reflect on it?, hehe). It's really cool, but it's hard. The server also needs to look for the next keyword in our cluttered SQL statement where it needs to be executed. Efficiency, because our sensory nerves are not sensitive to changes in the second, it is considered that the SQL order of self-written is irrelevant, "nothing changes anyway!" In fact, the server for each SQL parsing time will be detailed records, you can see the custom written by the SQL and the standard sequence of the SQ L How big the time difference is.
Therefore, we recommend that you work in peacetime SQL statements in accordance with the standard sequence of writing, one is professional, the second is practical, hehe, but I think the main thing is to feel comfortable in the heart.
The parsing order of standard SQL is:
(1). FROM clause, assemble 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 calculations
(5). Use the HAVING clause to filter the grouping
(6). Calculate All expressions
(7). Use order by to sort the result set

Example: In the Student score table (Tb_grade), the "Examinee name" content is not empty records according to "examinee name" group, and filter the results of the group, select "Total Score" greater than 600 points.
The standard sequential SQL statements are:
Select Candidate Name, Max (total) as Max Total
From Tb_grade
Where examinee name is not NULL
Group BY candidate name
Having Max (total) > 600
ORDER BY Max Total

In the example above, the SQL statements are executed in the following order:
(1). First execute the FROM clause to assemble data from the Tb_grade table from the data source
(2). Execute a WHERE clause to filter data that is not NULL for all data 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 () aggregate function and the maximum number of the total scores by "total"
(5). The HAVING clause is executed, and the total score of the screening course is greater than 600.
(7). Execute the ORDER BY clause to sort the final results by "Max score".

Order of execution of SELECT statements in SQL

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.