3
In SQL Server, the WHERE and HAVING clauses are both used to filter data, but they are used in different contexts and operate at different stages of the query processing.
WHERE: It is used to filter rows before any grouping or aggregation is performed. It is applied to individual rows in the table and can be used in SELECT, UPDATE, DELETE etc. It filters data at an earlier stage, right after the data is fetched from the table. We can't use aggregate functions directly.
HAVING: It is used to filter groups of rows after grouping and aggregation have been performed. It is applied to the result of the GROUP BY clause and only used with the GROUP BY clause or with aggregate functions. It filters data at a later stage, after the GROUP BY clause has formed groups and aggregate functions have been applied. We can use aggregate functions to filter.
Consider a table Sales with columns SaleID, ProductID, Quantity, and SaleDate.
Using WHERE:
SELECT ProductID, Quantity
FROM Sales
WHERE Quantity > 10;
This query selects rows where the Quantity is greater than 10 before any aggregation or grouping.
Using HAVING:
SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductID
HAVING SUM(Quantity) > 100;
This query groups the rows by ProductID, calculates the total quantity for each product, and then filters those groups to include only products with a total quantity greater than 100.
1 The WHERE clause filters individual records before any grouping or aggregation occurs and operates at the record level meaning it checks conditions for each row in the table.
The HAVING clause filters results after grouping or aggregation and It operates at the group of records level, applying conditions to aggregated data.
1 the WHERE clause is used to filter records before any groupings are made, whereas the HAVING clause is used to filter records after the groupings have been made. Essentially, WHERE is used with individual records, and HAVING is used with aggregated data.
Where:
SELECT *
FROM Employees
WHERE Department = 'Sales';
Having:
SELECT Department, COUNT(*) as EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;
1 Hello,
Consider the following table -
|
Roll_no
|
S_Name
|
Age
|
|
1
|
a
|
17
|
|
2
|
b
|
20
|
|
3
|
c
|
21
|
|
4
|
d
|
18
|
|
5
|
e
|
20
|
|
6
|
f
|
17
|
|
7
|
g
|
21
|
|
8
|
h
|
17
|
WHERE CLAUSE -
SELECT S_Name, Age FROM Student
WHERE Age >=18
Output -
|
S_Name
|
Age
|
|
b
|
20
|
|
c
|
21
|
|
d
|
18
|
|
e
|
20
|
|
g
|
21
|
HAVING CLAUSE -
SELECT Age, COUNT(Roll_No) AS No_of_Students
FROM Student GROUP BY Age
HAVING COUNT(Roll_No) > 1
Output -
|
Age
|
No_of_Students
|
|
17
|
3
|
|
20
|
2
|
|
21
|
2
|
Hope this helps.