SQL tutorial

SQL Having

Learn more about SQL, a standard language for interacting with databases and storing, manipulating, and retrieving data from databases.

Go hands-on with SQL in our free interactive SQL tutorial.

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:

Code Example
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.

The SQL 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.

As such, HAVING follows the GROUP BY clause in an aggregate query. In the simplest case, it would appear in the general form as below:

Code Example
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 LIMIT and ORDER BY if they are included:

Code Example
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 WHERE:

Code Example
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