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

Last updated on July 8th, 2023 at 03:18 pm

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 main difference between the WHERE and HAVING clauses in SQL is that the WHERE clause filters individual rows before grouping, while the HAVING clause filters groups of rows after grouping based on aggregate conditions such as MIN, MAX, SUM, AVG, COUNT, and so on.

In simple terms, WHERE is for filtering rows, and HAVING is for filtering groups.
Additionally, it is important to note that aggregate functions can be used with the HAVING clause but not with the WHERE clause.

Let’s first understand what these clauses are used for 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:

-- 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);

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:

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:

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:

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:

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:

Comparison Chart: WHERE vs. HAVING Clause in SQL

Differences between the HAVING and WHERE clauses in SQL:

This table provides a simplified overview of the main differences between SQL’s WHERE and HAVING clauses.

The WHERE clause is used to filter individual rows before grouping, while the HAVING clause is used to filter groups of rows after grouping. The WHERE clause cannot use aggregate functions, whereas the HAVING clause can.

FAQ

The following are some frequently asked questions about the WHERE and HAVING clauses in SQL:

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
• Mastering Database Normalization: Best Practices and Techniques
• SQL EXISTS – Exploring EXISTS Operator in SQL Server
• Function vs Stored procedure in SQL Server
• Mastering SQL Inner Join: A Comprehensive Guide With Practical Examples
• 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

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024