HAVINGQuery 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

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 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 ✓ Supported all Standard HAVING. Column aliases from SELECT cannot be referenced in HAVING (unlike MySQL) — repeat the expression or use a subquery/CTE.
SQL Server ✓ Supported all Standard HAVING. Aliases from SELECT cannot be referenced in HAVING.
Oracle ✓ Supported all Standard HAVING. Aliases from SELECT cannot be referenced in HAVING.
SQLite ✓ Supported 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: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

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

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

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

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

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