Having VS Where Clause in SQL | Explore the main Differences between Where and Having Clause in SQL

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.

having-vs-where-clause-in-sql
having-vs-where-clause-in-sql

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:

CustomerIdNameCountry
1Shekh AliINDIA
2Sadaf KamalUSA
3Mark AdamCANADA
4Bob JohnsonCANADA
5Luke WilliamsUSA
-- 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_iddepartmentsalary
1Marketing75000
2Marketing60000
3Marketing45000
4Sales70000
5Sales60000
6Sales80000
7Sales45000
8HR65000
9HR75000
10HR80000
employees table

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:

departmentSUM(Salary)
HR220000
Sales255000

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:

departmentMIN(Salary)
Marketing45000
Sales45000

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:

departmentMAX(Salary)
HR80000
Marketing75000
Sales80000

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:

departmentAVG(Salary)
HR73333.33
Sales63750

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:

departmentCOUNT(*)
HR3
Marketing3
Sales4

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:

5 2 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments