having-clause
The HAVING clause specifies a result table that consists of those groups of the intermediate result table for which the search-condition is true. The intermediate result table is the result of the previous clause. If this clause is not GROUP BY, the intermediate result table is considered a single group with no grouping columns of the previous clause of the subselect.
Each column-name in search-condition must be one of the following:
- Unambiguously identify a grouping column of the intermediate result table
- Be specified within an aggregate function1
- Be a correlated reference. A column-name is a correlated reference if it identifies a column of a table, view, common-table-expression, or nested-table-expression that is identified in an outer subselect
A group of the intermediate result table to which the search condition is applied supplies the argument for each function in the search condition, except for any function whose argument is a correlated reference.
If the search condition contains a subquery, the subquery can be thought of as being executed each time the search condition is applied to a group of the intermediate result table, and the results used in applying the search condition. In actuality, the subquery is executed for each group only if it contains a correlated reference. For an illustration of the difference, see Example 4 and Example 5.
A correlated reference to a group of the intermediate result table must either identify a grouping column or be contained within an aggregate function.
When HAVING is used without GROUP BY, any expression or column name in the select list must appear within an aggregate function.
The RID built-in function and the ROW CHANGE expression cannot be specified in a HAVING clause unless they are within an aggregate function.
Row access controls do not affect the operation of the HAVING clause.