Difference Between HAVING And WHERE Clause In SQL Server

Introduction

In this tutorial, I am going to explain the concept of HAVING and WHERE Clause in SQL Server. This detailed article will cover the following topics as follows,

1. Introduction
2. SQL Order of Execution
3. HAVING Clause
4. WHERE Clause
5. Difference Between HAVING And WHERE Clauses
6. Conclusion

First, let's create a database with some tables containing some dummy data. Here, I am providing you with the database along with the tables containing the records, on which I am showing you the various examples. Let's see.

CREATE DATABASE OnkarSharma_OnlineFoodStore
PRINT 'New Database ''OnkarSharma_OnlineFoodStore'' Created'
GO

USE [OnkarSharma_OnlineFoodStore]
GO

CREATE TABLE [dbo].[Employee] (
	EmployeeID INT IDENTITY (31100,1),
	EmployerID BIGINT NOT NULL DEFAULT 228866,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	Email VARCHAR(255) NOT NULL UNIQUE,
	DepartmentID VARCHAR(100) NOT NULL,
	Age INT  NOT NULL,
	GrossSalary BIGINT NOT NULL,
	PerformanceBonus BIGINT,
	ContactNo VARCHAR(25),
	PRIMARY KEY (EmployeeID)
);

CREATE TABLE [dbo].[tbl_Orders] (
	OrderId INT IDENTITY (108, 1) PRIMARY KEY,
	FoodieID INT,
	OrderStatus TINYINT NOT NULL, -- OrderStatus: 4: Cancelled; 3: Pending; 2: Processing; 1: Completed
	OrderDate DATE NOT NULL,
	ShippedDate DATE,
	RestaurantId INT NOT NULL,
);

CREATE TABLE [dbo].[tbl_OrderItems](
	OrderId INT NOT NULL,
	ItemId INT,
	MenuId INT NOT NULL,
	Quantity INT NOT NULL,
	Price DECIMAL(6, 2) NOT NULL,
	Discount DECIMAL(5, 2) NOT NULL DEFAULT 0,
	PRIMARY KEY (ItemId)
);

CREATE TABLE [dbo].[tbl_Menu] (
	MenuId INT IDENTITY (81, 1) PRIMARY KEY,
	FoodCategoryID INT NOT NULL,
	FoodName VARCHAR (255) NOT NULL,
	TypeofFood VARCHAR (100) NOT NULL,
	Price DECIMAL(6, 2) NOT NULL
);

Let's check our following tables by using the following queries.

1) To get the data from the "Employee" table, use the following query.

SELECT * FROM OnkarSharma_OnlineFoodStore..Employee

2) To get the data from the "tbl_Orders" table, use the following query.

SELECT * FROM OnkarSharma_OnlineFoodStore..tbl_Orders

3) To get the data from the "tbl_OrderItems" table, use the following query.

SELECT * FROM OnkarSharma_OnlineFoodStore..tbl_OrderItems

4) To get the data from the "tbl_Menu" table, use the following query.

SELECT * FROM OnkarSharma_OnlineFoodStore..tbl_Menu

Order of Execution In SQL Server

Before moving on to the main topic, we need to know the execution order of the query in SQL Server. The SQL Server order of execution defines the order in which the clauses of the query are evaluated.

FROM: The logical execution of a SQL Server query begins with the "FROM" statement, which collects data from the tables mentioned in the query.

WHERE: The next step is the "WHERE" clause, which filters the data according to the user-defined condition(s).

GROUP BY: The "GROUP BY" clause performs the grouping of the records (data) obtained from the WHERE condition(s).

HAVING: It's time to filter the groups based on the specified conditions created by the "GROUP BY" clause. And, this will be done by the HAVING clause.

SELECT: Now, the processing comes down to the SELECT command. And, "SELECT" evaluates which columns will be sent in the result. It also evaluates any keywords such as UNIQUE, DISTINCT, and TOP if it is included.

ORDER BY: Finally, the "ORDER BY" clause is used to sort the data by the column name specified in it. By default, it sorts the data in ascending order.

HAVING Clause

The HAVING clause is used together with the GROUP BY clause to filter data from groups based on the conditions specified in the HAVING clause. A HAVING clause applies only to groups as a whole.

Key Points

• HAVING Clause can only be used with a SELECT Statement.
• HAVING Clause is used to filter records from the groups. This means it is used to filter groups.
• HAVING Clause implements in column operations.
• HAVING Clause is used after GROUP BY Clause.
• HAVING Clause can have aggregate functions.
• The HAVING clause is slower than the WHERE clause and should be avoided whenever possible.

Syntax

SELECT <column_list>
FROM <table_name>
WHERE <search_condition(s)>
GROUP BY <expression>
HAVING <condition>;

Examples

The examples in this section demonstrate the functionality of the HAVING Clause. Let's see.

1) HAVING Clause with GROUP BY Clause

 The following example returns the list of departments having an Average Gross Salary is more than 25L.

SELECT DepartmentID, AVG(GrossSalary) AS 'AvgGrossSalary'
FROM OnkarSharma_OnlineFoodStore..Employee
GROUP BY DepartmentID
HAVING AVG(GrossSalary) > 2500000

2) HAVING Clause with WHERE Clause 

The following example returns the list of total employees for the respective departments having a Gross Salary is more than 23L.

SELECT COUNT(EmployeeID) AS TotalEmployees, DepartmentID, GrossSalary
FROM OnkarSharma_OnlineFoodStore..Employee
WHERE GrossSalary > 2300000
GROUP BY DepartmentID, GrossSalary
HAVING COUNT(EmployeeID) < 7

3) HAVING Clause with ORDER BY Clause

The following example returns the list of departments having an Average Gross Salary is more than 25L in descending order.

SELECT DepartmentID, AVG(GrossSalary) AS 'AvgGrossSalary'
FROM Employee
GROUP BY DepartmentID
HAVING AVG(GrossSalary) > 2500000
ORDER BY AvgGrossSalary DESC

4) HAVING Clause with Aggregate Functions

A) SUM()

The following example looks for sales orders that have a total order value of more than 1000.

SELECT OrderId, SUM((Quantity * Price) - Discount) AS 'TotalOrderValue'
FROM OnkarSharma_OnlineFoodStore..tbl_OrderItems
GROUP BY OrderId
HAVING SUM((Quantity * Price) - Discount) > 1000
ORDER BY TotalOrderValue

B) MAX() & MIN()

The following example finds the maximum & minimum prices for each type of food category.

SELECT FoodCategoryID, TypeofFood, MIN(Price) MinPrice, MAX(Price) MaxPrice
FROM OnkarSharma_OnlineFoodStore..tbl_Menu
GROUP BY FoodCategoryID, TypeofFood
HAVING MIN(Price) > 20 OR MAX(Price) < 700

C) AVG()

The following example finds food categories for an average list price between 100 and 500.

SELECT FoodCategoryID, TypeofFood, AVG(Price) 'AvgPrice'
FROM OnkarSharma_OnlineFoodStore..tbl_Menu
GROUP BY FoodCategoryID, TypeofFood
HAVING AVG(Price) BETWEEN 100 AND 500

D) COUNT()

The following example finds the eater (foodie) who has placed less than ten orders per year.

SELECT FoodieID, YEAR(OrderDate) AS [Order Year], 
       DATENAME(MONTH ,(OrderDate)) Month, COUNT(OrderId) AS 'TotalOrders'
FROM OnkarSharma_OnlineFoodStore..tbl_Orders
GROUP BY FoodieID, YEAR (OrderDate), DATENAME(MONTH ,(OrderDate))
HAVING COUNT(OrderId) < 10

WHERE Clause

The WHERE clause is used to filter the table's records. It is generally used with DML (Data Manipulation Language) commands, viz, SELECT, UPDATE, and DELETE. The WHERE clause applies to individual rows (records). As per Wikipedia, "A WHERE Clause specifies that a Data Manipulation Language (DML) statement should only affect rows that meet specified criteria".

Key Points

• WHERE Clause is generally used with DML commands.
• WHERE Clause is used to filter rows from the table.
• WHERE Clause implements in row operations.
• WHERE Clause comes before GROUP BY Clause.
• WHERE Clause cannot contain conditions with aggregate functions.
• This is an optional clause but it can be used to limit the number of rows affected by a DML statement.
• WHERE Clause is faster than HAVING Clause.

Syntax

SELECT <column_list>
FROM <table_name>
WHERE <search_condition(s)>

Examples

The examples in this section demonstrate the functionality of the WHERE Clause. Let's see.

1) SQL Server WHERE Clause

The following example returns the list of employees of the department "V_IT" who are less than 24 years old.

SELECT EmployeeID, CONCAT(FirstName , ' ' , LastName) AS [Full Name], 
	   Email, DepartmentID, GrossSalary
FROM OnkarSharma_OnlineFoodStore..Employee
WHERE Age < 24 AND DepartmentID = 'V_IT'

2) WHERE Clause with GROUP BY Clause

The following example gives the total gross salary for the departments "V_IT" and "V_HR".

SELECT DepartmentID, SUM(GrossSalary) AS TotalGrossSalary
FROM OnkarSharma_OnlineFoodStore..Employee
WHERE DepartmentID IN ('V_IT', 'V_HR')
GROUP BY DepartmentID

Points To Remember

As per Wikipedia, " WHERE is taken into account at an earlier stage of query execution, filtering the rows read from the tables. If a query contains GROUP BY, rows from the tables are grouped and aggregated. After the aggregating operation, HAVING is applied, filtering out the rows that don't match the specified conditions. Therefore, WHERE applies to data read from tables and HAVING should only apply to aggregated data, which isn't known in the initial stage of a query".

The HAVING clause cannot be used without a SELECT statement or with UPDATE and DELETE Statements. Conversely, we can use the WHERE Clause with SELECT, UPDATE, and DELETE (DML) Statements.

-- Wrong Queries
SELECT * FROM Employee
HAVING Salary > 3000000

UPDATE OnkarSharma_OnlineFoodStore..Employee
SET DepartmentID = 'V_Admin'
HAVING EmployeeID = 31100

DELETE FROM Employee
HAVING EmployeeId > 31105

--Right Queries
SELECT * FROM Employee
WHERE Salary > 3000000

UPDATE OnkarSharma_OnlineFoodStore..Employee
SET DepartmentID = 'V_Admin'
WHERE EmployeeID = 31100

DELETE FROM Employee
WHERE EmployeeId > 31105

HAVING Clause is slower than WHERE Clause and should be avoided when possible. Because the HAVING clause filters the data after performing the aggregate calculation while the WHERE clause filters the rows before performing the aggregate calculation.

SELECT DepartmentID, AVG(GrossSalary) AvgGrossSalary
FROM Employee
GROUP BY DepartmentID
HAVING DepartmentID IN ('V_Accounts', 'V_HR', 'V_MGR')

SELECT DepartmentID, AVG(GrossSalary) AvgGrossSalary
FROM Employee
WHERE DepartmentID IN ('V_Accounts', 'V_HR', 'V_MGR')
GROUP BY DepartmentID

A (SELECT) query can have both a WHERE clause and a HAVING Clause. In that case, the WHERE clause is applied first to filter individual rows in the table. The HAVING clause is then applied to the result set. Only groups that satisfy HAVING conditions appear in the output. You can apply the HAVING clause to only those columns that appear in the GROUP BY clause or also in the aggregate function.

SELECT DepartmentID, SUM(GrossSalary) AvgGrossSalary
FROM Employee
WHERE DepartmentID IN ('V_Accounts', 'V_HR', 'V_MGR')
GROUP BY DepartmentID
HAVING SUM(GrossSalary) > 5000000

Difference Between HAVING And WHERE Clause In SQL Server

The HAVING and WHERE clauses are often confusing for beginners and experienced alike, but they serve different purposes. So, let's look at the quick difference between HAVING and WHERE Clause In SQL Server.

S.No.	Key Points	HAVING Clause	WHERE Clause
1	Definition	HAVING Clause is used to filter records from the groups.	WHERE Clause is used to filter rows from the table.
2	Syntax	HAVING comes after the GROUP BY Clause.	WHERE Clause comes before GROUP BY Clause.
3	Conditions	HAVING Clause can have aggregate functions.	It cannot contain conditions with aggregate functions.
4	Type of Operation	HAVING Clause implements in column operations to summarized groups.	WHERE Clause implements in row operations.
5	Works as	HAVING Clause works as a post-filter.	WHERE clause works as a pre-filter.
6	Works with	HAVING clause can only be used with a SELECT statement.	WHERE Clause is generally used with DML commands.
7	Speed	HAVING Clause is slower than WHERE Clause.	WHERE Clause is faster than HAVING Clause.

See you in the next article, till then take care and be happy learning.

You may also visit my other articles,

• Difference Between Delete, Truncate, And Drop Statements In SQL Server
• Different Ways To Handle NULL In SQL Server

Conclusion

In this article, we have discussed the concept of HAVING and WHERE Clauses in SQL Server with various examples.

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about SQL Server.

Thanks for reading.