Difference Between Having and Where Clause

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 to perform different tasks which include searching, modifying, updating, etc. and makes the data presentable and provides easy handling. The two most used queries for searching are Where and Having.

Having vs Where Clause

The difference between having and where clause is the function they do. Having clause is used with the group by clause and is used to filter a group whereas a Where clause is used with various clauses and used for filtering the data in a table. The Having clause cannot work without a group by clause. 

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 it is implemented on columns.

The Where clause can be used with several functions like SELECT, UPDATE, 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 of 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 statement.

The 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, 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, 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 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 being grouped or even not being grouped. 
2. The group by statement have different positions values in the queries. In having clause it is compulsory to use whereas in 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 is used to filter the data according to the columns and is a number in front of different filters whereas the Where clause is used to filter 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