HAVING Clause in SQL

The HAVING clause places the condition in the groups defined by the GROUP BY clause in the SELECT statement.

This SQL clause is implemented after the 'GROUP BY' clause in the 'SELECT' statement.

This clause is used in SQL because we cannot use the WHERE clause with the SQL aggregate functions. Both WHERE and HAVING clauses are used for filtering the records in SQL queries.

Difference between HAVING and WHERE Clause

The difference between the WHERE and HAVING clauses in the database is the most important question asked during an IT interview.

The following table shows the comparisons between these two clauses, but the main difference is that the WHERE clause uses condition for filtering records before any groupings are made, while HAVING clause uses condition for filtering values from a group.

Syntax of HAVING clause in SQL

Examples of HAVING clause in SQL

In this article, we have taken the following four different examples which will help you how to use the HAVING clause with different SQL aggregate functions:

Example 1: Let's take the following Employee table, which helps you to analyze the HAVING clause with SUM aggregate function:

If you want to add the salary of employees for each city, you have to write the following query:

The output of the above query shows the following output:

Now, suppose that you want to show those cities whose total salary of employees is more than 5000. For this case, you have to type the following query with the HAVING clause in SQL:

The output of the above SQL query shows the following table in the output:

Example 2: Let's take the following Student_details table, which helps you to analyze the HAVING clause with the COUNT aggregate function:

Suppose, you want to count the number of students from the above table according to their age. For this, you have to write the following query:

The above query will show the following output:

Now, suppose that you want to show the age of those students whose roll number is more than and equals 2. For this case, you have to type the following query with the HAVING clause in SQL:

The output of the above SQL query shows the following table in the output:

Example 3: Let's take the following Employee table, which helps you to analyze the HAVING clause with MIN and MAX aggregate function:

MIN Function with HAVING Clause:

If you want to show each department and the minimum salary in each department, you have to write the following query:

The output of the above query shows the following output:

Now, suppose that you want to show only those departments whose minimum salary of employees is greater than 4000. For this case, you have to type the following query with the HAVING clause in SQL:

The above SQL query shows the following table in the output:

MAX Function with HAVING Clause:

In the above employee table, if you want to list each department and the maximum salary in each department. For this, you have to write the following query:

The above query will show the following output:

Now, suppose that you want to show only those departments whose maximum salary of employees is less than 8000. For this case, you have to type the following query with the HAVING clause in SQL:

The output of the above SQL query shows the following table in the output:

Example 4: Let's take the following Employee_Dept table, which helps you to analyze the HAVING clause with AVG aggregate function:

If you want to find the average salary of employees in each department, you have to write the following query:

The above query will show the following output:

Now, suppose that you want to show those departments whose average salary is more than and equals 6500. For this case, you have to type the following query with the HAVING clause in SQL:

The above SQL query will show the following table in the output: