HAVING

Query Techniques
Buy Me A Coffee

Filter the results of a GROUP BY aggregation. WHERE filters individual rows before grouping; HAVING filters groups after aggregation.

Filters groups created by GROUP BY, analogous to how WHERE filters individual rows. Aggregate conditions (COUNT > 5, SUM(amount) < 100) must use HAVING since WHERE cannot reference aggregates. Evaluated after GROUP BY but before SELECT expressions are finalized.

Compatibility

Filter by Database
SQL HAVING Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native all Standard HAVING. MySQL extension: HAVING can reference column aliases defined in SELECT (e.g. HAVING total > 100 where total is a SELECT alias). This is non-standard behavior.
PostgreSQL Native all Standard HAVING. Column aliases from SELECT cannot be referenced in HAVING (unlike MySQL) — repeat the expression or use a subquery/CTE.
SQL Server Native all Standard HAVING. Aliases from SELECT cannot be referenced in HAVING.
Oracle Native all Standard HAVING. Aliases from SELECT cannot be referenced in HAVING.
SQLite Native all Standard HAVING supported. SQLite also allows referencing SELECT aliases in HAVING, similar to MySQL.

Details

The most common mistake: using HAVING instead of WHERE for row-level filters (HAVING runs after grouping and is slower). The alias-in-HAVING shortcut works in MySQL and SQLite but will fail in PostgreSQL, SQL Server, and Oracle — repeat the aggregate expression for portability.

Standard Syntax

SELECT dept_id, COUNT(*) AS employee_count FROM employees GROUP BY dept_id HAVING COUNT(*) > 10;

Version Support

MySQL: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

Standard HAVING. MySQL extension: HAVING can reference column aliases defined in SELECT (e.g. HAVING total > 100 where total is a SELECT alias). This is non-standard behavior.

SELECT dept_id, COUNT(*) AS cnt FROM employees GROUP BY dept_id HAVING cnt > 10; -- MySQL allows alias reference (non-standard)

PostgreSQL Native syntax

Standard HAVING. Column aliases from SELECT cannot be referenced in HAVING (unlike MySQL) — repeat the expression or use a subquery/CTE.

SELECT dept_id, COUNT(*) AS cnt FROM employees GROUP BY dept_id HAVING COUNT(*) > 10; -- must repeat COUNT(*), not alias 'cnt'

SQL Server Native syntax

Standard HAVING. Aliases from SELECT cannot be referenced in HAVING.

SELECT dept_id, COUNT(*) AS cnt FROM employees GROUP BY dept_id HAVING COUNT(*) > 10;

Oracle Native syntax

Standard HAVING. Aliases from SELECT cannot be referenced in HAVING.

SELECT dept_id, COUNT(*) AS cnt FROM employees GROUP BY dept_id HAVING COUNT(*) > 10;

SQLite Native syntax

Standard HAVING supported. SQLite also allows referencing SELECT aliases in HAVING, similar to MySQL.

SELECT dept_id, COUNT(*) AS cnt FROM employees GROUP BY dept_id HAVING cnt > 10; -- alias reference works in SQLite