What is the difference between Having and Where clause

Let us understand the difference between HAVING and WHERE clause with an example. Consider the Employees table below.

Employee Table


Use the script below to create and populate the table, so you can follow along with the examples.

CREATE TABLE [dbo].[Employees1]
(
  [Id] [int] NOT NULL,
  [Name] [nvarchar](50) NULL,
  [Dept] [nvarchar](50) NULL,
  [Gender] [nvarchar](50) NULL,
  [Country] [nvarchar](50) NULL,
  [Salary] [float] NULL,
)


Data Inserttion Script:
Insert into Employees Values(1,'John','IT','Male','UK',5000)
Insert into Employees Values(2,'Mary','HR','Female','India',3000)
Insert into Employees Values(3,'Todd','IT','Male','UK',3500)
Insert into Employees Values(4,'Pam','HR','Female','India',4000)
Insert into Employees Values(5,'Tatcher','Payroll','Male','USA',2000)
Insert into Employees Values(6,'Sunil','IT','Male','USA',1400)
Insert into Employees Values(7,'Hari','Payroll','Male','UK',2500)
Insert into Employees Values(8,'Sunitha','HR','Female','India',4000)
Insert into Employees Values(9,'Sneha','IT','Female','India',3000)
Insert into Employees Values(10,'Ruby','Payroll','Male','UK',4600)

You can use HAVING clause only when you use Group By clause. The following query will give an error stating "Column 'Employees.Dept' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause."

Select * from Employees Having Dept='IT'

So to filter the rows as they are selected from the table we use WHERE clause as shown below 

Select * from Employees Where Dept='IT'

If I want to select, the toal number of employees in IT department I can write the query in 2 different ways as shown below.

1. Select Dept, COUNT(*) as Total from Employees Where Dept='IT' Group By Dept
2. Select Dept, COUNT(*) as Total from Employees Group By Dept Having Dept='IT'

The first query runs faster than the second query. This is because, in the first query we only select IT department records and then perform the count operation where as in the second query we perform the count on all the Department records and then select only the IT department and its count using the HAVING clause. As the second query has more records to process than the first query, it tends to be relatively slower.

So, a WHERE clause is used in the select statement to filter the rows as they are retrieved from the database table. HAVING clause is used in the select statement in conjunction with the Group By clause, to filter the query results after they have been grouped.

If you have spotted any errors or if you can improve this answer further, please feel free to do so by submitting the form below.