MySQL HAVING vs WHERE

In this article we will learn about MySQL HAVING vs WHERE

We all know that MySQL is one of the most popular query language. In MySql we use “WHERE” clause a lot. But we also use “HAVING”  we have to work with aggregates data.

We often get confused between these two. Lets this article help you on that.

Difference between “WHERE” and “HAVING”

• WHERE clause: WHERE clause is used when we want to filter from a result data set. Most importantly this filter occurs before any kind of grouping.
• HAVING clause: HAVING clause is used in a grouped data set. It cannot be used in SELECT statement without GROUP BY.

First of all, lets see what is the hierarchy of a SQL statement. See below:

• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING

That means, any SQL query will be run like below order.

MySQL HAVING vs WHERE Example:

Let’s say we have an sample database table named “film”. There are four columns: film_id, title, release_year, rental_rate and rating.

Now check below SQL query where


SELECT rating, AVG(rental_rate)
FROM film
WHERE rating IN ('R', 'G', 'PG')
GROUP BY rating
HAVING AVG(rental_rate) > 3;

Here we see use of both “Where” clause and “Having” clause. “Where” clause is used for filter the result set of “SELECT” query. On the other hand “HAVING” query is used for filter out the result of “GROUP BY” clause.

Please note that, The GROUP BY statement is often used with aggregate functions such as: COUNT(), MAX(), MIN(), SUM(), AVG()to group the result set of one or more columns.