Difference between Where and Having Clause in SQL

WHERE and HAVING clauses play an important role in refining the result set of a query. While both clauses are used for filtering data, they serve distinct purposes and are applied at different stages of query execution. Let’s explore the details of each and their differences with examples.

WHERE Clause

WHERE Clause is used to filter the records from the table or used while joining more than one table. Only those records will be extracted who are satisfying the specified condition in the WHERE clause. It can be used with SELECT, UPDATE, and DELETE statements. It is applied before any grouping or aggregation occurs in a query

Let us consider the below table “Student” 

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

Query: 

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

HAVING Clause is used to filter the records from the groups based on the given condition in the HAVING Clause. Those groups who will satisfy the given condition will appear in the final result. It is applied after the grouping and aggregation of data.

Let us consider Student table mentioned above and apply having clause on it: 

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

Difference Between Where and Having Clause in SQL

SR.NO.	WHERE Clause	HAVING Clause
1.	Filters rows before groups are aggregated.	Filters groups after the aggregation process..
2.	WHERE Clause can be used without GROUP BY Clause	HAVING Clause can be used with GROUP BY Clause
3.	WHERE Clause implements in row operations	HAVING Clause implements in column operation
4.	WHERE Clause cannot contain aggregate function	HAVING Clause can contain aggregate function
5.	WHERE Clause can be used with SELECT, UPDATE, DELETE statement.	HAVING Clause can only be used with SELECT statement.
6.	WHERE Clause is used before GROUP BY Clause	HAVING Clause is used after GROUP BY Clause
7.	WHERE Clause is used with single row function like UPPER, LOWER etc.	HAVING Clause is used with multiple row function like SUM, COUNT etc.

Conclusion

While both WHERE and HAVING clauses are used for filtering data in SQL queries, they serve different purposes and are applied at different stages of query execution. Understanding their distinctions is crucial for writing efficient and effective SQL queries.