Having vs Where Clause in SQL | Database Management System (DBMS) - Computer Science Engineering (CSE) PDF Download

Introduction

• 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

SELECT column_Name1, column_Name2, ....., column_NameN aggregate_function_name(column_Name) FROM table_name GROUP BY column_Name1 HAVING condition; 

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:

SELECT SUM(Emp_Salary), Emp_City FROM Employee GROUP BY Emp_City;

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:

SELECT SUM(Emp_Salary), Emp_City FROM Employee GROUP BY Emp_City HAVING SUM(Emp_Salary)>5000;

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:

SELECT COUNT(Roll_No), Age FROM Student_details GROUP BY Age ; 

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:

SELECT COUNT(Roll_No), Age FROM Student_details GROUP BY Age HAVING COUNT(Roll_No) >= 2 ;  

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

SELECT MIN(Emp_Salary), Emp_Dept FROM Employee GROUP BY Emp_Dept;

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:

SELECT MIN(Emp_Salary), Emp_Dept FROM Employee GROUP BY Emp_Dept HAVING MIN(Emp_Salary) > 4000 ; 

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:

SELECT MAX(Emp_Salary), Emp_Dept FROM Employee GROUP BY Emp_Dept;  

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:

SELECT MAX(Emp_Salary), Emp_Dept FROM Employee GROUP BY Emp_Dept HAVING MAX(Emp_Salary) < 8000 ; 

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:

SELECT AVG(Emp_Salary), Emp_Dept FROM Employee_Dept GROUP BY Emp_Dept;  

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:

SELECT AVG(Emp_Salary), Emp_Dept FROM Employee_Dept GROUP BY Emp_Dept HAVING AVG(Emp_Salary) > 6500 ;  

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