Purpose
A conditional expression that must be satisfied for a group of rows to be included in the result data.
Syntax
Syntax Elements
- HAVING
- An introduction to the conditional clause in the SELECT statement.
- search_condition
- One or more conditional expressions that must be satisfied by the result rows. You can specify aggregate operators, scalar subqueries, and DEFAULT functions as conditional expressions with HAVING.
ANSI Compliance
The HAVING clause is ANSI SQL:2011-compliant.
Usage Notes
You cannot specify LOB columns in the HAVING search condition.
- A single group defined in the SELECT expression list, which has only aggregate results
- One or more groups defined in a GROUP BY clause
HAVING cannot be specified in a recursive statement of a recursive query. However, a nonrecursive seed statement in a recursive query can specify the HAVING clause.
If you specify the value for a row-level security constraint in a search condition, that value must be expressed in its encoded form.
You can specify a scalar subquery as an operand of a scalar predicate in the HAVING clause of a query.
- You can specify a DEFAULT function with a column name argument within a predicate. The system evaluates the DEFAULT function to the default value of the column specified as its argument. Once the system has evaluated the DEFAULT function, it treats it like a constant in the predicate.
- You can specify a DEFAULT function without a column name argument within a predicate only if there is one column specification and one DEFAULT function as the terms on each side of the comparison operator within the expression.
- Following existing comparison rules, a condition with a DEFAULT function used with comparison operators other than IS NULL or IS NOT NULL is unknown if the DEFAULT function evaluates to null.A condition other than IS NULL or IS NOT NULL with a DEFAULT function compared with a null evaluates to unknown.
IF a DEFAULT function is used with … THEN the comparison is … IS NULL - TRUE if the default is null
- Else it is FALSE
IS NOT NULL - FALSE if the default is null
- Else it is TRUE
See SQL Functions, Operators, Expressions, and Predicates for more information about the DEFAULT function.
You cannot specify a SAMPLE clause in a subquery used as a HAVING clause predicate.
Evaluation Order of WHERE, GROUP BY, and HAVING Clauses
When the WHERE, GROUP BY, and HAVING clauses are used together in a SELECT statement, the order of evaluation is as follows:
- WHERE
- GROUP BY
- HAVING
HAVING Rules
Tables referenced in a HAVING clause must be specified in one of the following:
- FROM clause
- WHERE clause
- SELECT expression list
- Non-aggregate condition
Aggregating a Join In a HAVING Clause
You can use the HAVING clause when referencing columns from two or more tables.
Example: Simple HAVING Clause Examples
The following example shows the use of HAVING as applied to the aggregate results of a single group defined in the SELECT list, which is particularly useful in a SELECT subquery.
SELECT COUNT(employee) FROM department WHERE dept_no = 100 HAVING COUNT(employee) > 10;
The following SELECT statements are additional examples of the correct use of the HAVING clause.
SELECT SUM(t.a) FROM t,u HAVING SUM(t.a)=SUM(u.a); SELECT SUM(t.a), SUM(u.a) FROM t,u HAVING SUM(t.b)=SUM(u.b); SELECT SUM(t.a) FROM t,u HAVING SUM(t.b)=SUM(u.b) AND u.b = 1 GROUP BY u.b;
Example: Grouping Departments by Average Salaries
Display salary ranges for specified departments whose salaries average more than $37,000:
SELECT dept_no, MIN(salary), MAX(salary), AVG(salary) FROM employee WHERE dept_no IN (100,300,500,600) GROUP BY dept_no HAVING AVG(salary) > 37000;
The result is:
dept_no MIN(salary) MAX(salary) AVG(salary) ------- --------------- --------------- --------------- 300 23,000.00 65,000.00 47,666.67 500 22,000.00 56,000.00 38,285.71
Example: Using a HAVING Clause to Aggregate a Join
The columns named price and sales_qty are from two different tables table_1, unit_price_cost, and table_2, sales_hist. Use the following SELECT statement to find which category of items is sold for a profit margin greater than $1000.
SELECT table_1.category, (table_2.price - table_2.cost) * SUM (table_1.sales_qty) AS margin FROM sales_hist AS table_1, unit_price_cost AS table_2 WHERE table_1.prod_no=table_2.prodno GROUP BY table_1.category, table_2.price, table_2.cost HAVING margin > 1000;
A subquery can have a join on a view with an aggregate operation and a HAVING clause that references more than one table.
Related Topics
For more information related to the HAVING clause, see: