What is the difference between the HAVING and WHERE clause? This is one of the most frequently asked questions in interviews, particularly among freshers.
The HAVING and WHERE clauses in SQL Server are very similar purposes, but they operate at different stages of the query and have some key differences.
The main difference between these two clauses is how they are used with the GROUP BY clause. The HAVING clause is used with GROUP BY, while WHERE is not.
In simple words, the WHERE clause is used to filter records before any groupings are made, while the HAVING clause is used to filter values from a group.
Let’s first understand what these clauses are used for in SQL.

Table of Contents
WHERE Clause
The WHERE clause in SQL is used to filter rows before the results are grouped and aggregated. This means that the WHERE clause is applied to each individual row in the table, and only those rows that meet the specified condition/criteria will be included in the final results.
Example: Where Clause
Let us take an example to understand the where clause. Suppose we have a table named customers that contain the following records:
CustomerId | Name | Country |
---|---|---|
1 | Shekh Ali | INDIA |
2 | Sadaf Kamal | USA |
3 | Mark Adam | CANADA |
4 | Bob Johnson | CANADA |
5 | Luke Williams | USA |
-- Find the count of all customers in the 'INDIA'
SELECT COUNT(*) FROM Customers
WHERE Country = 'INDIA';
-- Output: 1
This query selects the count of all rows in the Customers
table where the Country
column is equal to ‘INDIA’. The WHERE clause is used to filter out any rows that do not have ‘INDIA’ in the Country column.
HAVING clause
The HAVING clause is similar to the WHERE clause, but it is used to filter the results of a grouped query. This means that the HAVING clause is applied to the grouped rows, rather than to individual rows.
The HAVING clause in SQL is used in combination with the GROUP BY clause to specify conditions that filter which groups appear in the result. We can use Aggregate functions like SUM, MIN, MAX, AVG, and COUNT only with the SELECT and HAVING clauses.
Example: Having Clause
-- Find the count of customers in each country,
-- but only include countries that have more than 1 customer.
SELECT Country, COUNT(*) FROM Customers
GROUP BY Country
HAVING COUNT(*) > 1;
-- Output:
-- USA | 2
-- CANADA | 2
The output of the query should include both the USA and CANADA, as both countries have more than 1 customer.
HAVING vs WHERE clauses:
The following are the main differences between the HAVING and WHERE clauses in SQL.
- The HAVING clause can only be used in conjunction with a SELECT statement, while the WHERE clause can be used with SELECT, UPDATE, and DELETE statements.
- The HAVING clause cannot be used to filter NULL values, while the WHERE clause can.
- The HAVING clause is typically used to filter the results of a grouped query based on the aggregated values, while the WHERE clause is used to filter the results based on individual rows.
- The WHERE clause filters individual rows, while the HAVING clause filters groups of rows instead of just one row at a time.
- The HAVING clause can only be used with aggregate functions like SUM, MIN, MAX, AVG, COUNT, etc while the WHERE clause can be used with any expression.
- The WHERE clause should be placed before the GROUP BY clause in a query because it filters rows before any aggregate calculations are made. On the other hand, the HAVING clause is placed after the GROUP BY clause and filters rows after the aggregate calculations have been made.
- Both the WHERE and HAVING clauses can be used together in a SELECT query, with the WHERE clause filtering individual rows and the HAVING clause filtering groups after aggregate calculations have been made. The rows are first filtered by the WHERE clause, then grouped and the aggregate calculations are performed, and finally, the groups are filtered by the HAVING clause.
- The WHERE clause is used to filter rows based on a specified condition, while the HAVING clause is used to filter groups after all rows have been selected and aggregate calculations have been made based on a specified condition.
- The WHERE clause acts as a pre-filter, filtering rows before any aggregate calculations are made, while the HAVING clause acts as a post-filter, filtering groups after aggregate calculations have been made.
Using HAVING and WHERE Clauses in the Same Query
Certainly! It is possible to use both the HAVING and WHERE clauses in the same query. The WHERE clause will be applied first, followed by the GROUP BY clause, and finally the HAVING clause.
Here is an example of a query that uses both the HAVING and WHERE clauses:
SELECT Country, COUNT(*) FROM Customers
WHERE Country IN ('USA', 'Canada')
GROUP BY Country
HAVING COUNT(*) > 1;
-- Output:
-- USA | 2
-- Canada | 2
This query will select the count of customers in the USA and Canada, and group the results by country. The WHERE clause is used to filter out any rows that do not have ‘USA’ or ‘Canada’ in the Country column. The HAVING clause is then used to filter out any countries that have more than 1 customer.
Using Aggregate functions with HAVING Clause
Here are a few examples of how you can use the SUM, MIN, MAX, AVG, and COUNT aggregate functions with the HAVING clause in SQL, using the following employees
table.
-- Creating a new employees table
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
department VARCHAR(255),
salary INTEGER
);
-- Inserting records into the table.
INSERT INTO employees (employee_id, department, salary)
VALUES (1, 'Marketing', 75000),
(2, 'Marketing', 60000),
(3, 'Marketing', 45000),
(4, 'Sales', 70000),
(5, 'Sales', 60000),
(6, 'Sales', 80000),
(7, 'Sales', 45000),
(8, 'HR', 65000),
(9, 'HR', 75000),
(10, 'HR', 80000);
employee_id | department | salary |
---|---|---|
1 | Marketing | 75000 |
2 | Marketing | 60000 |
3 | Marketing | 45000 |
4 | Sales | 70000 |
5 | Sales | 60000 |
6 | Sales | 80000 |
7 | Sales | 45000 |
8 | HR | 65000 |
9 | HR | 75000 |
10 | HR | 80000 |
Example: Using Having clause with SUM() function
This query would return the total salary for each department, and only include departments where the total salary is greater than 210000.
SELECT department, SUM(salary)
FROM employees
GROUP BY department
HAVING SUM(salary) > 210000;
The result would be:
department | SUM(Salary) |
---|---|
HR | 220000 |
Sales | 255000 |
Example: Using Having clause with MIN() function
This query would return the minimum salary for each department, and only include departments where the minimum salary is less than 50000.
SELECT department, MIN(salary)
FROM employees
GROUP BY department
HAVING MIN(salary) < 50000;
The result would be:
department | MIN(Salary) |
---|---|
Marketing | 45000 |
Sales | 45000 |
Example: Using Having clause with MAX() function
This query would return the maximum salary for each department, and only include departments where the maximum salary is greater than 70000.
SELECT department, MAX(salary)
FROM employees
GROUP BY department
HAVING MAX(salary) > 70000;
Output:
department | MAX(Salary) |
---|---|
HR | 80000 |
Marketing | 75000 |
Sales | 80000 |
This query would return the average salary for each department, and only include departments where the average salary is greater than 60000.
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
Output:
department | AVG(Salary) |
---|---|
HR | 73333.33 |
Sales | 63750 |
Example: Using HAVING clause with Count() function in SQL
This query would return the number of employees in each department, and only include departments with more than 2 employees.
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;
Output:
department | COUNT(*) |
---|---|
HR | 3 |
Marketing | 3 |
Sales | 4 |
FAQ
The following are some frequently asked questions about the WHERE and HAVING clauses in SQL:
Q: What is the difference between the WHERE and HAVING clauses in SQL?
The WHERE clause is used to filter rows from a table based on a specific condition/Criteria, whereas the HAVING clause is used to filter grouped rows based on a specific condition. The WHERE clause is used before grouping is performed, while the HAVING clause is used after grouping is performed. The WHERE clause cannot be used with aggregate functions, but the HAVING clause can be used with aggregate functions.
Q: Can you use both WHERE and HAVING clauses in the same query?
Yes, you can use both WHERE and HAVING clauses in the same query. The WHERE clause will be applied first to filter rows from the table, and then the HAVING clause will be applied to the grouped rows.
Q: Can you use an aggregate function in the WHERE clause?
No, an aggregate function cannot be used in the WHERE clause. The WHERE clause is used to filter rows from the table before grouping, and aggregate functions are only used after grouping to summarise data. If you want to filter grouped rows using an aggregate function, use the HAVING clause instead.
Articles you might also like:
- Delete vs Truncate vs Drop in SQL Server: What is the Difference between DELETE, TRUNCATE and DROP in SQL?
- Types of Joins in SQL Server
- Function vs Stored procedure in SQL Server
- Stored Procedure in SQL Server With Examples
- Create, Alter, and Drop Database In SQL Server
- SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
- Primary Key Constraint In SQL Server
- View In SQL | Types Of Views In SQL Server
- SQL Server Trigger Update, Insert, Delete Examples
- SQL pivot tables: Understanding pivot tables in SQL Server
- Understanding the SOLID Principle: Single Responsibility Principle in C# - June 7, 2023
- Difference between var and dynamic in C# - May 26, 2023
- Understanding the Chain of Responsibility Design Pattern in C# - May 11, 2023