Execution sequence of GroupBy, Having, Where, Orderby statements

An SQL statement usually produces multiple temporary views, so the execution sequence of these keywords is very important, because you must understand whether this keyword is used to operate the fields before the corresponding view is formed or the temporary view is formed. This issue is especially important when the view with an alias is used. GroupBy and Having, Where, Orderby

An SQL statement usually produces multiple temporary views, so the execution sequence of these keywords is very important, because you must understand whether this keyword is used to operate the fields before the corresponding view is formed or the temporary view is formed. This issue is especially important when the view with an alias is used. Group By, Having, Where, Order

An SQL statement usually produces multiple temporary views, so the execution sequence of these keywords is very important, because you must understand whether this keyword is used to operate the fields before the corresponding view is formed or the temporary view is formed. This issue is especially important when the view with an alias is used.

The Group By and Having, Where, Order by keywords are executed in the following Order: Where, Group By, Having, Order.

• FirstWhere deletes records that do not meet the conditions in the most original records (so we should filter out records that do not meet the conditions in the where statement as much as possible, so as to reduce the number of groups)
• ThenUse the grouping conditions specified after the Group By keyword to Group the filtered view.
• NextThe system filters out records that do not meet the conditions after the keyword Having.
• LastSort the view By Order By statement, and the final result is generated.

Among the four keywords, the column name of the final view can be used only in the Order By statement, for example:

SELECT FruitName, ProductPlace, Price, ID AS IDE, DiscountFROM T_TEST_FRUITINFOWHERE (ProductPlace = N'china')ORDER BY IDE

IDE is only available in the order by statement. In other condition statements, if you want to reference a column name, you can only use ID instead of IDE.

PS:

• All fields that appear after 'group by' must appear after 'select' at the same time;

• All fields that appear after select but not in aggregate functions must appear after group by at the same time.

• The having clause is limited to columns and aggregate expressions defined in the SELECT statement.

(Article: programmer's home)