We have seen previously that we can filter data using the
WHERE clause and aggregate data using the
GROUP BY clause.
While the former can be combined in a
SELECT statement with the latter, in order to filter data before it is aggregated, one might initially think that it could also be used to do so afterward.
For example, if we wanted to find an employee last name that occurs in the employees table exactly 150 times, we would assume that the query below would be valid:
SELECT last_name, COUNT(*) FROM employees GROUP BY last_name WHERE COUNT(*)=150;
However, here the
WHERE clause cannot be applied on the result of
COUNT, as it filters data before the aggregation occurs and so can only be applied with predicates using columns in the source table.
HAVING clause serves to be used in these instances instead, and functions as a
WHERE clause for aggregate calculations, which are evaluated after the initial application of
WHERE during a query’s execution.
HAVING follows the
GROUP BY clause in an aggregate query. In the simplest case, it would appear in the general form as below:
SELECT <expression> FROM <table> GROUP BY <columns> HAVING <predicate>;
Where the predicate here uses expressions including aggregate functions.
In more complex queries, the clause should come after
WHERE (as it is before
GROUP BY) and precede the usage of
ORDER BY if they are included:
SELECT <expression> FROM <table> WHERE <predicate> GROUP BY <columns> HAVING <predicate> ORDER BY <column> LIMIT <row count>;
Let’s return to our example where we are looking for the last name shared by exactly 200 employees in the
employees table. In this case, using
HAVING is straightforward, as it is simply used instead of
SELECT last_name, COUNT(*) FROM employees HAVING COUNT(*)=150;
Learn SQL Today
Get hands-on experience writing code with interactive tutorials in our free online learning platform.
- Free and fun
- Designed for beginners
- No downloads or setup required