Having vs Where Clause in SQL

Table of Contents
1. Introduction
2. Difference between HAVING and WHERE Clause
3. Syntax of HAVING clause in SQL
4. Examples of HAVING clause in SQL
5. Additional notes and best practices
6. Summary
View more

Introduction

• The HAVING clause places a condition on groups defined by the GROUP BY clause in a SELECT statement.
• The HAVING clause is evaluated after grouping and aggregation; it appears after the GROUP BY clause in the statement.
• We use HAVING because aggregate functions (SUM, COUNT, AVG, MIN, MAX) cannot be used in the WHERE clause. Both WHERE and HAVING are filtering mechanisms in SQL but operate at different stages.

Difference between HAVING and WHERE Clause

The principal distinction between the two clauses is the stage at which they filter data in a query:

• WHERE filters individual rows before any grouping or aggregation is performed. It cannot reference aggregate functions directly.
• HAVING filters groups after aggregation has taken place. It can reference aggregate functions and conditions on aggregated values.
• In typical query processing order, filtering with WHERE reduces the row set that is input to the grouping step; HAVING applies to the result of grouping and aggregation.
• You may use both WHERE and HAVING in the same query: WHERE limits rows before aggregation; HAVING limits groups after aggregation.

Syntax of HAVING clause in SQL

SELECT column_Name1, column_Name2, ..., aggregate_function(column_Name) FROM table_name [WHERE row_condition] GROUP BY column_Name1, column_Name2, ... HAVING group_condition;

Notes:

• The WHERE clause (if present) must appear before GROUP BY.
• group_condition can reference aggregate functions such as SUM(...), COUNT(...), AVG(...), MIN(...), MAX(...).
• It is valid to use HAVING without GROUP BY to filter an aggregate over the whole result (the aggregate is treated as a single group).

Examples of HAVING clause in SQL

Below are worked examples that demonstrate the use of HAVING with different aggregate functions.

Example 1: SUM aggregate with HAVING

Context: Consider an Employee table. The following shows how to find total salary by city and then filter cities whose total salary exceeds a threshold.

Query to compute total salary per city:

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

Result:

To show only those cities whose total salary of employees is more than 5000:

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

Filtered result:

Example 2: COUNT aggregate with HAVING

Context: Consider a Student_details table. We count students by age and filter ages having at least a specified number of students.

Query to count students by age:

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

Result:

To show only ages where the count of students is greater than or equal to 2:

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

Filtered result:

Example 3: MIN and MAX aggregates with HAVING

Context: Using the Employee table again, we demonstrate MIN and MAX with HAVING.

MIN Function with HAVING

Query to show minimum salary by department:

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

Result:

To show only those departments whose minimum salary is greater than 4000:

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

Filtered result:

MAX Function with HAVING

Query to show maximum salary by department:

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

Result:

To show only those departments whose maximum salary is less than 8000:

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

Filtered result:

Example 4: AVG aggregate with HAVING

Context: Consider an Employee_Dept table. We compute average salary per department and filter departments by average salary.

Query to compute average salary by department:

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

Result:

To show only those departments whose average salary is greater than or equal to 6500:

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

Filtered result:

Additional notes and best practices

• You cannot use aggregate expressions (SUM, COUNT, AVG, MIN, MAX) inside a WHERE clause; these appear in HAVING.
• Use WHERE to filter rows before aggregation for better performance; reducing the number of rows before grouping reduces work for the aggregation step.
• Use HAVING to filter on aggregated results (for example, groups whose SUM is above a threshold).
• HAVING without GROUP BY is valid when you want to apply a condition on an aggregate computed over the whole result, for example:

  SELECT SUM(Emp_Salary) AS total_salary FROM Employee HAVING SUM(Emp_Salary) > 100000;

• When using both WHERE and HAVING, place WHERE before GROUP BY and HAVING after GROUP BY.
• Be explicit with column aliases when helpful (for example, AS avg_salary) to make query results clear.

Summary

The WHERE clause filters rows before grouping and cannot use aggregate functions; the HAVING clause filters groups after aggregation and can reference aggregate functions. Use WHERE to reduce rows early for performance and HAVING to apply conditions to aggregated results.