What is the Difference Between WHERE and HAVING Clause?

What is the difference between WHERE and HAVING Clause? Is it one of the most popular questions asked in interviews, especially to the freshers? these two clauses are very similar to each other. They’re also used to filter records in SQL queries and they primarily restrict the tuples returned by the SELECT statement, Furthermore, the main difference arises when used with the GROUP BY clause. Before making the comparison, first of all, know these SQL clauses. So, let’s first learn about The WHERE and HAVING clauses and then their differences in depth in this article.
Difference Between WHERE and HAVING Clause

WHERE Clause

The WHERE clause is used for the purpose to fetch the data which specifies the given condition. It’s used to filter records and select only required records. The WHERE clause is used with SELECT, UPDATE, DELETE queries to filter records from the table or relation.

It can also implement the logical connectives AND, OR, and NOT. They are called the Boolean condition that must be true to retrieve the records. The logical connectives expressions use the comparison operators as their operands such as =, =, and . The comparison operators are generally used to compare strings and arithmetic expressions.

The WHERE Clause describes a particular condition when retrieving data from a single table or multiple tables using the JOIN clause. If the particular condition is satisfied, it returns the specific value from the table. The WHERE clause places conditions on the chosen or selected columns.

The following syntax demonstrate the use of the WHERE clause:

SELECT column_lists,     
FROM table_name  
WHERE conditions  
GROUP BY column_lists;

SELECT column_lists,

FROM table_name

WHERE conditions

GROUP BY column_lists;

Let us take an example to understand the WHERE Clause. Consider we have a table named Employee that contain the following data:

Table: Employee

EmpID	FirstName	LastName	Email	Mobile	Salary	DeptID
1	Peg	Legge	peg04@gmail.com	1245638975	35000	11
2	Isabelle	Ringing	Ringing12@gmail.com	458569656	45000	12
3	Rhoda	Report				13
4	Anita	Letterback	anita04@gmail.com	458796561	22000	11
5	Amanda	Hug	hug@gmail.com	Otto	54500	13
6	Willie	Findit	Findit780@gmail.com	789663452	34000	12
7	Don	Messwidme				11
8	Faye	Clether	Clether1104@gmail.com	124589637	65000	13

Example #1: If we want to get the employee detail whose Department ID are greater than 11, then we can use the statement as follows:

SELECT * FROM Employee  
WHERE DeptID > 11;

1 2	SELECT * FROM Employee WHERE DeptID > 11;

As per result, We will get the below output where we can see employee detail whose Department ID are greater than 11:

WHERE Clause

HAVING Clause

HAVING clause in SQL used in conjunction with GROUP BY keyword enables us to specify conditions that filter which group results seem in the result. It returns only those values from the groups in the final result that fulfills specific conditions. Thus, we can also use the WHERE and HAVING clause together during selection. during this case, the WHERE clause first filters the individual rows, then rows are grouped, performs aggregate calculations, & at last HAVING clause filters the groups.

This clause places conditions on groups created by the SQL GROUP BY clause. It behaves just like the WHERE clause when the SQL statement doesn’t use the GROUP BY Clause. We can use the aggregate functions such as SUM, COUNT, MIN, MAX, and AVG only with two clauses: SELECT and HAVING.

The following syntax demonstrate the use of the HAVING clause:

SELECT column_lists,     
aggregate_function (expression)    
FROM table_name  
WHERE conditions  
GROUP BY column_lists    
HAVING condition;

SELECT column_lists,

aggregate_function (expression)

FROM table_name

WHERE conditions

GROUP BY column_lists

HAVING condition;

Demo Table: Magazine

Let us take an example to know the HAVING clause. Here we are considering another table Magazine for demonstration.

ID	MagazineName	Cost	NumPage	City	CatID	Language
1	Playpen	250	30	Lucknow	2	English
2	Little Women	150	35	Delhi	1	Hindi
3	Runway	220	40	Noida	3	English
4	Spy Magazine	180	25	Ghaziabad	1	Hindi
5	Philadelphia Story	240	21	Jhasi	2	English
6	Woollett	120	11	Delhi	1	Hindi

Syntax:

SELECT Column FROM Table WHERE condition GROUP BY Column HAVING condition [ORDER BY Column];

1	SELECT Column FROM Table WHERE condition GROUP BY Column HAVING condition [ORDER BY Column];

We can also utilize the SQL HAVING clause with logical operators like OR and AND.
Let us consider the SQL statement given below to learn the clause:

SELECT COUNT (CatID), Language From Magazine GROUP BY Language HAVING COUNT(CATID) >1;

1	SELECT COUNT (CatID), Language From Magazine GROUP BY Language HAVING COUNT(CATID) >1;

WHERE Vs. HAVING Keyword Comparison Chart

Comparison Basis	WHERE Clause	HAVING Clause
Definition	It can be used to perform filtration on individual rows.	It can be used to perform filtration on groups.
Basic	It is implemented in row operations.	It is implemented in column operations.
Data fetching	The WHERE clause fetches the specific records from particular rows based on the specified condition.	The HAVING clause first fetches the complete records or data. after that, It separates them according to the given condition.
Aggregate Functions	The WHERE keyword doesn’t permit to work with aggregate functions.	The HAVING keyword can work with aggregate functions.
Act as	The WHERE clause acts as a pre-filter.	The HAVING clause acts as a post-filter.
Used with	We can use the WHERE keyword with the SELECT, UPDATE, & DELETE statements.	The HAVING keyword can only use with the SELECT statement.
GROUP BY	The GROUP BY keyword comes after the WHERE clause.	The GROUP BY keyword comes before the HAVING clause.

Key Differences between WHERE and HAVING Clause

There are the following points justify the main differences between database and schema:

SQL WHERE clause filters individual rows, whereas the HAVING clause filters groups rather than one row at a time.
We can’t use the WHERE clause with aggregate functions because as you know it works for filtering individual rows. Whereas, HAVING can works with aggregate functions because it is used to filter groups.
Row operations are handled by the WHERE clause, whereas the HAVING clause handles column operations to summarized rows or groups.
WHERE clause comes before the GROUP BY, it implies that the WHERE clause filters rows before performing aggregate calculations. HAVING clause comes after GROUP BY, which means that the HAVING clause filters rows after performing aggregate calculations. Therefore, HAVING is slower than WHERE in terms of efficiency and should be avoided wherever possible.
We can combine the WHERE and HAVING keywords together in a SELECT query. during this case, the WHERE clause is used first to filter individual rows. The rows are then grouped, perform aggregate calculations, & finally, the HAVING is used to filter the groups.
The WHERE keyword retrieves the required data based on the specified condition. Whereas the HAVING clause first fetches whole data, and then separation is done based on the required condition.
Without a SELECT statement, we can’t use the HAVING keyword. Conversely, we can use a WHERE with SELECT, UPDATE, & DELETE statements.
WHERE clause is a pre-filter, On the other hand, HAVING clause is a post-filter.

Conclusion

In this article, we have made a difference or comparision between the WHERE and HAVING clauses. Here, we conclude that both clauses work in an identical way in filtering the data, except some further feature makes the HAVING clause more popular. We can efficiently work with aggregate functions in the HAVING keyword while the WHERE keyword doesn’t allow for aggregate functions.