Having Clause in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

Having Clause in MySQL with Examples

In this article, I am going to discuss the Having Clause in MySQL with Examples. Please read our previous article where we discussed Group by Clause in MySQL. At the end of this article, you will understand the following pointers in detail.

1. What is a Having Clause in MySQL?
2. Why do we need Having Clause in MySQL?
3. How to use Having Clause in MySQL?
4. Examples to Understand Having Clause in MySQL
5. Using both Having and Where Clause in MySQL
6. Having Clause Using COUNT, SUM, and AVG Aggregate Functions
7. Difference Between Where Clause and Having Clause in MySQL

What is a Having Clause in MySQL?

The Having Clause in MySQL is also used for filtering the data just like the where clause. Having Clause in MySQL filters the rows from the intermediate result set that is built by using the FROM, WHERE, or GROUP BY clauses in a SELECT statement.

The Having Clause in MySQL is typically used with a GROUP BY clause. That means the Having Clause is used in combination with a GROUP BY clause to restrict the number of groups to be returned by satisfying the condition which is specified using the having clause.

Why do we need Having Clause in MySQL?

The WHERE clause could not be used with aggregate data rows, hence the HAVING clause was added to SQL. The purpose of the HAVING clause is to filter the group rows produced by the GROUP BY clause.

The HAVING clause is the same as the WHERE clause. The only difference is WHERE clause FILTERS the intermediate data results, while the HAVING clause operates on group rows. Likewise, WHERE clause, we can use conditions and operators with the HAVING clauses to build complex SQL statements.

Since the HAVING clause acts as a filter on group rows, the only possible column in group rows are columns specified in the GROUP BY clause.

Syntax of MySQL Having Clause:

Parameters or Arguments of Having Clause in MySQL:

1. aggregate_function: It can be any of the aggregate functions such as SUM, COUNT, MIN, MAX, or AVG.
2. expression1, expression2, expression_n: The expressions which are not encapsulated within an aggregate function must be included in the GROUP BY clause.
3. Where Conditions: It is optional. If you want to retrieve selected records based on some conditions then you need to specify the conditions using the Where clause.
4. HAVING having_condition: The Having Clause Condition is used to add a further filter condition that can be applied only to the aggregated results to restrict the number of groups to be returned. 

Examples to Understand Having Clause in MySQL:

We are going to use the following employee table to understand the Having Clause in MySQL with examples.

Please use the following SQL Script to create the company database and employee table with the required records.

CREATE DATABASE company;
USE company;

CREATE TABLE employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'Delhi');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1011, 'Pramod Panda', 'IT', 45000, 'Male', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1012, 'Preety Tiwary', 'HR', 55000, 'Female', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1013, 'Santosh Dube', 'IT', 52000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1014, 'Manoj Tripathy', 'HR', 85000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1015, 'Mitali Rout', 'Finance', 70000, 'Female', 26, 'Mumbai');

Filtering Groups in MySQL:

Example: Find the total salary given to all employees who are working in London Here, we can achieve this using two ways i.e. using the Where and Having clause. The Where clause is used to filter the rows before aggregation, whereas the Having clause is used to filter the groups that mean after aggregations.

Filtering rows using WHERE clause

SELECT City, SUM(Salary) as TotalSalary
FROM Employee
WHERE City = 'London'
GROUP BY City;

Filtering groups using the HAVING clause

SELECT City, SUM(Salary) as TotalSalary
FROM Employee
GROUP BY City
HAVING City = 'London';

In both the case we are getting the same output as shown below.

Note: from the performance point of view, the HAVING is slower than the WHERE clause, and the having clause should be avoided if possible. The point that we should consider is, try to eliminate the records as soon as possible which you don’t want in your result set.

Using both Having and Where Clause in MySQL

It is also possible in MySQL to use both Where and Having in a single SQL Select Statement. Let us understand this with an example. We want to retrieve the total Salary of All the Male employees who are working in London. The following SQL Script exactly does the same. In the below example, first, it filters the data based on the Where clause i.e. filter the employees by Gender. Then Group by clause applied to the filter result set based on the City column. Finally, the Having clause applied to the groups and return only the London group.

SELECT City, SUM(Salary) as TotalSalary
FROM Employee
WHERE Gender = 'Male'
GROUP BY City
HAVING City = 'London';

When you execute the above query, you will get the following output.

Having Clause Using SUM Function in MySQL:

Let us see an example of using Having Clause in MySQL with SUM Aggregate function. Our requirement is the find all the departments whose total salary greater than 170000. So, we need to group the employees by department and The Having Clause will filter the results so that only departments with a total Salary greater than 140000 will be returned. The following SQL Query exactly does the same.

SELECT Department, SUM(Salary) AS Total_Salary
FROM Employee
GROUP BY Department
HAVING SUM(Salary) > 170000;

When you execute the above query, it will give us the below output. As you can in the below image, the Finance department is not included in the result set as the total Salary to given to all employees in the Finance department is less than 170000.

Having Clause Using COUNT Function in MySQL:

Let us see an example of using Having Clause in MySQL with COUNT Aggregate function. Our requirement is the find all the departments whose total number of employees working in that department is greater than 5.

The following example uses the Group by Clause and COUNT Aggregate function to return the Department and the number of employees in that department. The Having Clause then applied to the groups and filter the results so that only the department with more than 5 employees will be returned as a result.

SELECT Department, COUNT(*) AS 'Number of employees'
FROM Employee
GROUP BY Department
HAVING COUNT(*) > 5;

When you execute the above SQL Query, you will get the below output. As you can in the below image, the Finance department is not included in the result set as there are only 3 employees working in the Finance department.

Having Clause using AVG Function in MySQL:

Let us see an example of using Having Clause in MySQL with AVG Aggregate function. Our requirement is the find all the departments whose average Salary is more than 50000.

The following example uses the Group by clause and AVG Aggregate function to return the name of the department and the Average salary of each department. The Having clause then applied and filtered the group and will return only those departments where the average salary is greater than 50000.

SELECT Department, AVG(Salary) AS 'Average salary'
FROM Employee
GROUP BY Department
HAVING AVG(Salary) > 50000;

When you execute the above query, you will get the following output. As you can in the below image, the IT department is not included in the result set as the Average Salary of the IT department is less than 50000.

Difference Between Where Clause and Having Clause in MySQL

1. WHERE clause in MySQL cannot be used with aggregate functions whereas HAVING clause can be used with aggregate functions. That means the WHERE clause in MySQL is used for filtering individual rows on a table whereas the HAVING clause in MySQL is used to filtering the groups which are created by the Group by Clause.
2. The WHERE comes before the GROUP BY clause. That means the WHERE clause filters rows before aggregate calculations are performed. On the other hand, the HAVING clause comes after GROUP by Clause. That means the HAVING clause filters rows after aggregate calculations are performed. So, from a performance standpoint, the HAVING Clause is slower than the WHERE Clause and should be avoided if possible.
3. WHERE and HAVING clauses can be used together in a single SELECT statement. In that case, the WHERE clause is applied first to filter individual rows. The rows are then grouped and aggregate calculations are performed, and then only the HAVING clause filters the groups in MySQL. 
4. The MySQL WHERE clause can be used with Select, Insert, and Update statements whereas the HAVING clause can only be used with the Select statement.
5. We can use the Where clause without using the Group by Clause but we can not use the Having Clause without using the Group by Clause in MySQL.

In the next article, I am going to discuss Order by Clause in MySQL with Examples. Here, in this article, I try to explain the Having Clause in MySQL with Examples. I hope you enjoy this Having Clause in the MySQL article. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Dot Net Tutorials

About the Author: Pranaya Rout

Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.