Filters groups after aggregation (like WHERE for GROUP BY).
HAVING <condition>## Overview HAVING filters the grouped result set produced by GROUP BY. While WHERE filters individual rows before grouping, HAVING filters entire groups after aggregation. The HAVING condition typically references aggregate functions (COUNT, SUM, AVG, MIN, MAX) but may also reference columns that appear in the GROUP BY list. ## Execution Order HAVING is evaluated immediately after GROUP BY and aggregation, but before the SELECT list, DISTINCT, ORDER BY, and LIMIT. This means column aliases defined in the SELECT list are not visible in the HAVING clause. Aggregate expressions in HAVING are computed over the same groups as those in the SELECT list. ## Behavior - **Aggregate filtering**: The primary use of HAVING is to filter groups based on aggregate values. Groups for which the HAVING condition evaluates to FALSE or UNKNOWN are excluded from the result. - **Non-aggregate references**: HAVING may reference columns from the GROUP BY list directly (e.g., `HAVING region = 'US'`). However, this is semantically equivalent to a WHERE clause and less efficient since the filter is applied after grouping. The optimizer may push such non-aggregate predicates from HAVING down to WHERE when doing so is safe. - **Complex conditions**: HAVING supports the same logical operators as WHERE, including AND, OR, NOT, IN, BETWEEN, and subqueries. Multiple conditions can be combined freely. - **NULL handling**: Groups where the HAVING condition evaluates to NULL (UNKNOWN) are excluded, following the same three-valued logic as WHERE. Aggregate functions generally skip NULL input values, so `HAVING AVG(col) > 0` excludes groups where all values of col are NULL (because AVG returns NULL for an all-NULL input). - **Interaction with ROLLUP/CUBE/GROUPING SETS**: HAVING applies to all grouping levels produced by ROLLUP, CUBE, or GROUPING SETS. Subtotal and grand total rows are subject to the same HAVING filter as detail-level groups. - **No predicate pushdown**: HAVING predicates that involve aggregate functions cannot be pushed below the GROUP BY operator because the aggregate values do not exist until grouping completes. ## Compatibility HAVING conforms to the SQL:1992 standard. All standard comparison operators, logical connectives, and aggregate functions are supported in the HAVING clause.
-- Filter groups by aggregate result
SELECT region, COUNT(*) AS cnt
FROM orders
GROUP BY region
HAVING COUNT(*) > 100;-- HAVING with multiple aggregate conditions
SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000 AND COUNT(*) >= 5;-- HAVING with SUM and a threshold
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 10000
ORDER BY total_spent DESC;-- HAVING referencing an expression not in SELECT
SELECT product_id, COUNT(*) AS sale_count
FROM order_items
GROUP BY product_id
HAVING MAX(unit_price) > 500;-- HAVING with OR logic
SELECT status, COUNT(*) AS cnt
FROM tickets
GROUP BY status
HAVING COUNT(*) > 1000 OR MIN(created_at) < '2024-01-01';-- HAVING combined with ROLLUP
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (region, product)
HAVING SUM(amount) > 5000;