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