Querying
The HAVING Clause
The HAVING clause filters groups produced by the GROUP BY clause.
Overview
The HAVING clause filters groups produced by the GROUP BY clause. Unlike the WHERE clause, which filters individual rows before grouping, HAVING is evaluated after grouping and can reference aggregate functions such as COUNT, SUM, AVG, MIN, and MAX. It is an optional part of the SELECT statement and appears after GROUP BY and before ORDER BY. HAVING was introduced in SQL-86 and remains a core feature of standard SQL. Its primary purpose is to restrict the result set to only those groups that satisfy a specified condition. For example, SELECT dept, COUNT(*) FROM employees GROUP BY dept HAVING COUNT(*) > 5 returns only departments with more than five employees. Without GROUP BY, HAVING can still be used, but it then treats the entire result set as a single group. In practice, HAVING is essential for analytical queries that require filtering based on aggregated values, such as finding products with total sales above a threshold or customers with more than a certain number of orders. It complements WHERE by allowing two levels of filtering: row-level and group-level.
Syntax and usage
The HAVING clause is part of the SELECT statement. Its general syntax is: SELECT column_list FROM table_name [WHERE condition] [GROUP BY column_list] [HAVING condition] [ORDER BY column_list];. The HAVING condition typically includes aggregate functions, but it can also reference grouping columns. For instance, SELECT category, AVG(price) FROM products GROUP BY category HAVING AVG(price) > 100; filters categories where the average price exceeds 100. HAVING can be used without GROUP BY, in which case the entire table is treated as one group. Some vendors allow HAVING to reference columns not in the GROUP BY list if they are functionally dependent, but this is not portable. Standard SQL requires that columns in the HAVING condition either be aggregate expressions or appear in the GROUP BY clause. HAVING is evaluated after GROUP BY and before ORDER BY. It cannot be used without a SELECT statement, and it is not allowed in other statements like INSERT, UPDATE, or DELETE. Vendor differences exist: for example, MySQL permits HAVING to reference aliases defined in the SELECT list, while standard SQL does not. PostgreSQL and SQL Server generally follow the standard more strictly.
How it works
The HAVING clause operates on the result of grouping. The query execution order is conceptually: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. First, rows are filtered by WHERE. Then, remaining rows are grouped according to GROUP BY. Each group is then evaluated against the HAVING condition. Only groups for which the condition is true are retained. Finally, the SELECT list is evaluated for each surviving group, and ORDER BY sorts the output. Because HAVING is evaluated after grouping, it can use aggregate functions that summarize each group. In contrast, WHERE cannot use aggregates because it operates before grouping. For example, SELECT product_id, SUM(quantity) FROM sales GROUP BY product_id HAVING SUM(quantity) > 1000 first groups sales by product, then sums quantities per product, and finally keeps only products with total quantity over 1000. If HAVING is used without GROUP BY, the entire result set is considered a single group, and aggregates are computed over all rows. Some optimizers may push conditions from HAVING to WHERE if they do not involve aggregates, but this is an implementation detail and does not change semantics.
Examples
Consider a table orders with columns order_id, customer_id, order_date, and amount. To find customers who have placed more than 10 orders, use: SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 10;. To find customers whose total order amount exceeds 1000, use: SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000;. HAVING can also be combined with WHERE: SELECT customer_id, SUM(amount) FROM orders WHERE order_date >= '2023-01-01' GROUP BY customer_id HAVING SUM(amount) > 500;. This first filters orders from 2023 onward, then groups by customer, and finally keeps customers with total amount over 500. HAVING can reference multiple aggregates: SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 5 AND AVG(amount) > 50;. It can also be used without GROUP BY: SELECT COUNT(*) FROM orders HAVING COUNT(*) > 100; returns a single row if the total count exceeds 100, otherwise no rows. Note that in standard SQL, HAVING cannot reference column aliases; use the aggregate expression directly.
Common pitfalls
A frequent mistake is using HAVING when WHERE would suffice. For example, SELECT * FROM orders GROUP BY customer_id HAVING customer_id = 5; is less efficient than SELECT * FROM orders WHERE customer_id = 5 GROUP BY customer_id; because HAVING filters after grouping. Another pitfall is assuming HAVING can be used without GROUP BY to filter individual rows; without GROUP BY, HAVING treats the entire set as one group, so conditions on non-aggregated columns may yield unexpected results. Also, some developers incorrectly use column aliases in HAVING; while MySQL allows this, standard SQL does not, and other databases like PostgreSQL and SQL Server will raise an error. Another issue is mixing aggregate and non-aggregate columns in HAVING without proper grouping; standard SQL requires that non-aggregate columns in HAVING appear in GROUP BY. Additionally, HAVING cannot be used in UPDATE or DELETE statements; it is exclusive to SELECT. Finally, performance can degrade if HAVING is used on large groups without appropriate indexes, as all rows must be grouped before filtering. Always consider whether a condition can be moved to WHERE to reduce the number of rows processed.
Best practices
Use WHERE for row-level filtering and HAVING for group-level filtering. This improves performance by reducing the number of rows before grouping. When writing HAVING conditions, prefer referencing aggregate expressions directly rather than aliases for portability. If you need to filter on an aggregate, ensure the aggregate is also computed in the SELECT list if you want to display it. Avoid using HAVING without GROUP BY unless you intentionally want to treat the entire result set as a single group. Keep HAVING conditions simple and index-friendly where possible. For complex queries, consider using a subquery or common table expression (CTE) to compute aggregates first, then filter in an outer query; this can sometimes be clearer and more efficient. Always test queries with EXPLAIN to understand execution plans. Remember that HAVING is evaluated after GROUP BY, so it cannot be used to filter rows before aggregation. When combining WHERE and HAVING, place the most restrictive conditions in WHERE to minimize the data that needs to be grouped. Finally, be aware of vendor-specific extensions, such as allowing aliases in HAVING, and avoid them if portability is a concern.
Frequently asked questions
Q: Can I use HAVING without GROUP BY? A: Yes, but then the entire result set is treated as a single group. For example, SELECT COUNT(*) FROM orders HAVING COUNT(*) > 100; returns one row if the total count exceeds 100, otherwise no rows. Q: What is the difference between WHERE and HAVING? A: WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after grouping and typically uses aggregate functions. Q: Can I use column aliases in HAVING? A: Standard SQL does not allow aliases in HAVING; you must repeat the aggregate expression. Some databases like MySQL permit aliases, but this is non-standard. Q: Is HAVING supported in all SQL databases? A: Yes, HAVING is part of standard SQL since SQL-86 and is widely supported. However, minor syntax variations exist, so consult your database documentation for specifics.