Having vs Where Clause: Difference and Comparison

MySQL is one of the most used languages for storing data in databases. The language has many commands known as queries which help the user perform different tasks, including searching, modifying, updating, etc., making the data presentable and providing easy handling. The two most used queries for searching are Where and Having.

Key Takeaways

1. The HAVING clause filters records based on aggregate functions and groups, while the WHERE clause filters individual records before grouping.
2. The HAVING clause is used with the GROUP BY statement, whereas the WHERE clause can be used independently or in conjunction with other clauses.
3. The WHERE clause cannot be used with aggregate functions, but the HAVING clause can be used with these functions like COUNT, SUM, and AVG.

Having vs Where Clause

The HAVING clause in SQL is used to filter records in combination with the GROUP BY clause when aggregate functions are used. The WHERE clause is used to filter records before any groupings or aggregations are made.

Having clause is used for the filtration of the columns that are present in a table and are grouped by a specific condition given by group by clause. The advantage of using the Having clause is that it can contain aggregate functions as well as is implemented on columns.

The Where clause can be used with several functions like SELECT, UPDATE, and DELETE and is used for filtering the records present in a given table and can be used for giving a filter while joining tables. The clause works on rows and does not require a group-by-clause. The query of where clause can perform any logical operation like not, AND, etc.

Comparison Table

Parameters of Comparison	Having Clause	Where Clause
Function	The clause is used for filtering the data from a group.	The where clause is used for the filtration of the data from the records of a table.
Group  by clause	It cannot work without a group by clause and is used in a query after this.	It can work without a group by clause and even if is given the where clause comes first.
Aggregate operations	The aggregate operations can be performed on the data because it filters groups.	It cannot perform these operations because it filters individual rows.
Application	The Having clause is used with the group by clause and then having clause works on the columns of the group	The where clause is used for the filtration of rows of a given table.
Selection	The Having clause first retrieves the whole data then groups them and after that, the filtering is done according to the specified condition.	The Where clause only retrieves the specified amount of data according to the condition provided.

What is Having Clause?

Having clause is used for filtering the data and is used in a query in conjugation with the group by command. The group by clause first groups the data according to the query, and then the having clause is used for operating on the columns of the grouped data. Data filtration is quite a long process.

The advantage of using this clause is that it can work with a variety of different aggregate functions like SUM, COUNT, etc. The output of the calculations performed is generally a number. The aggregate functions of multi-line functions can be used because it is performed on a group, and another advantage is that it can also work with where statements.

Working with the Where clause is a bit different. The steps that follow are:-

• Several rows are retrieved based on the where statement.
• The rows are grouped by the use of group by clause.
• This is followed by performing different functions like sum and count.
• The data is displayed according to the condition specified by the having clause.

The Having clause has its disadvantages. The clause cannot be used along with several clauses like the Update, Modify, Delete, etc. and has been a problem. It also sorts the data by the groups; therefore, several times, it is difficult to sort the data according to some preferences.

What is Where Clause?

The where clause is used in a query for filtering different kinds of data from the records of a table. The select statement has its limitations like it cannot perform aggregate functions in the query it is used with and also cannot help in cases where a specific amount is needed. 

The Where Clause can be used at various places and with many different clauses like Update, Modify, and Delete and can be used for performing special tasks through this. The segregation by this clause is done before the group by clause, i.e. the data is filtered before, and it can or cannot have a group by clause. The steps of the internal query performance are as follows:-

Example:- SELECT state from table name where number=1 group by state;

• The query above will first be the column state from the records of the table.
• The records will then be selected based on the condition given.
• After the selection, the data will be grouped by the state they belong to, and a number will be given according to the people in each state.

The Where clause can perform some tasks like the logical operations and join two or more conditions or negate a condition using operations like AND, OR and NOT. 

Main Differences Between Having and Where Clause

1. The Having clause is used to sort the data after it has been grouped by the group by clause, whereas the Where clause is used to filter the records of a table before or after being grouped. 
2. The group by statement have different position values in the queries. In the having clause, it is compulsory to use, whereas, in the Where clause, it is used after the query is run and data is filtered.
3. The Having clause can perform the aggregate operations. After all, it can run multi-line functions and groups the data first, whereas the Where clause cannot perform because it can only run single-line functions.
4. The Having clause filters the data according to the columns and is a number in front of different filters, whereas the Where clause filters the records or the rows of a table.
5. The Having clause first retrieves the whole data in a table and then filters them after being grouped, whereas the Where clause first filters the data or the records of the table.

References

1. http://www.vldb.org/conf/2001/P491.pdf