MySQL HAVING

Summary: in this tutorial, you will learn how to use MySQL HAVING clause to specify a filter condition for groups of rows or aggregates.

Introducing MySQL HAVING clause

The MySQL HAVING clause is used in the SELECT statement to specify filter conditions for group of rows or aggregates.

The MySQL HAVING clause is often used with the GROUP BY clause. When using with the GROUP BY clause, you can apply a filter condition to the columns that appear in the GROUP BY clause. If the GROUP BY clause is omitted, the MySQL HAVING clause behaves like the WHERE clause. Notice that the MySQL HAVING clause applies the condition to each group of rows, while the WHERE clause applies the condition to each individual row.

Examples of using MySQL HAVING clause

Let’s take a look at an example of using MySQL HAVING clause.

We will use the orderdetails table in the sample database for the sake of demonstration.

orderdetails table

We can use the MySQL GROUP BY clause to get order number, the number of items sold per order and total sales for each:

MySQL HAVING example

Now, we can find which order has total sales greater than $1000. We use the MySQL HAVING clause on the aggregate as follows:

MySQL HAVING with GROUP BY

We can construct a complex condition in the MySQL HAVING clause using logical operators such as OR and AND. Suppose we want to find which order has total sales greater than $1000 and contains more than 600 items, we can use the following query:

MySQL HAVING with complex condition

Suppose we want to find all orders that has shipped and has total sales greater than $1500, we can join the orderdetails table with the orders table by using the INNER JOIN clause, and apply condition on the status column and the total aggregate as the following query:

MySQL HAVING with complex condition example

The MySQL HAVING clause is only useful when we use it with the GROUP BY clause to generate the output of the high-level reports. For example, we can use the MySQL HAVING clause to answer some kinds of queries like give me all the orders in this month, this quarter and this year that have total sales greater than 10K.

In this tutorial, you have learned how to use the MySQL HAVING clause together with the GROUP BY to specify filter condition on groups of records or aggregates.