Where and Having Clause in SQL

WHERE and HAVING clause are mainly used in the statement of SQL queries, these allow us to restrict the combination in the result relation through using a specific predicate. The major difference between WHERE and HAVING is that WHERE clause specifies the conditions for selecting the tuples (rows) from the relations, including join conditions if needed. On the other hand, HAVING clause specifies a condition on the groups being selected rather than on individual tuples.

SQL stands for Structured Query Language; it is comprehensive or declarative database language used for accessing the data from the databases.

Comparison Chart

Basis for comparison	WHERE	HAVING
Basic	Implemented in row operations.	Implemented in column operations.
Applied to	Single row	Summarized row or groups.
Data fetching	It only fetches the particular data from particular rows according to the condition.	At first, complete data is fetched then separated according to the condition.
Aggregate Functions	Cannot appear in WHERE clause.	Can appear in HAVING clause.
Used with	SELECT and other statements such as UPDATE, DELETE or either one of them.	Can't be used without a SELECT statement.
Act as	Pre-filter	Post-filter
GROUP BY	Comes after WHERE.	Comes before HAVING.

Definition of Where Clause

The SQL WHERE clause is used to describe a condition at the time of retrieving the data from a single table or by joining with multiple tables.  It only returns the particular value from the table if the given condition is fulfilled. WHERE clause is used to permeate the records and retrieve only the necessary records.

SQL also implements the logical connectives and, or and not in the WHERE clause which are also known as the boolean condition; the condition must be true to retrieve the tuples. The operands of the logical connectives expressions involve the comparison operators such as <, <=, >, >=, =, and <>. These comparison operators compare strings and arithmetic expressions. It can be used in the SELECT statement, as well as in UPDATE, DELETE statements.

Let’s take an example. The table shown below named as ‘Sales‘ table consists of ‘Product‘ and ‘Sales_amount‘ attributes.

The following query is to be written to calculate the Total_sales of phone and speakers.

1. SELECT Product, sum(Sales_amount) AS Total_sales
2. FROM Sales
3. WHERE Product in ( 'Phone', 'Speakers')
4. GROUP BY Product;

Following output is the resulting output where the rows are filtered first, phone and speaker rows are retrieved then the aggregate function is performed.

Definition of Having Clause

SQL provides HAVING clause which can be used in conjunction along with GROUP BY clause. This HAVING clause helps in retrieving the values for the groups that fulfils certain conditions. WHERE clause can also use in conjunction with HAVING clause during selection, WHERE clause filters the individual row. The rows are then grouped, and aggregate calculations are performed, at last HAVING clause filter the groups.

It behaves in the same as WHERE when GROUP BY keyword is not used. The Group functions such as min, max, avg, sum, and count can appear in only two clauses: SELECT and HAVING clause. It provides a condition on the tuples corresponding to each value in the group of attributes. The only set of records that satisfies the condition will be shown as the result.

Here also we are taking the same example as WHERE clause and considering the same ‘Sales‘ table. When we want to calculate the Total_sales of phone and speakers using HAVING clause, we will write the following query.

1. SELECT Product, sum(Sales_amount) AS Total_sales
2. FROM Sales
3. GROUP BY Product
4. HAVING Product in ('phone', 'Speakers');

The query produces the following output where the products are retrieved first, then aggregate function (sum) is performed and at last groups are filtered unlike WHERE clause.

When we want to find only those products where the Total_sales is greater than 1000. The query can be written as:

1. SELECT Product, sum(Sales_amount) AS Total_sales
2. FROM Sales
3. GROUP BY Product
4. HAVING sum(Sales_amount)>1000;

The produced output is:

This can not be performed using WHERE clause in spite of HAVING and it generates an error message because WHERE clause cannot be used with aggregate functions.

Key Differences Between Where and Having Clause

1. WHERE clause is employed in row operations and applied on a single row whereas HAVING clause is used in column operations and can be applied to summarized rows or groups.
2. In WHERE clause the desired data is fetched according to the applied condition. In contrast, HAVING clause fetch whole data then separation is done according to the condition.
3. Aggregate functions like min, sum, max, avg can never appear along with WHERE clause. As against, these functions can appear in HAVING clause.
4. HAVING clause can not use without a SELECT statement. Conversely, WHERE can be used with SELECT, UPDATE, DELETE, etc.
5. WHERE clause behaves as a pre-filter while HAVING clause acts as a post-filter.
6. WHERE clause when used with GROUP BY, comes before GROUP BY. This signifies that WHERE filter rows before aggregate calculations are performed. On the other hand, HAVING comes after GROUP BY, which means it filters after aggregate calculations are performed.

Conclusion

WHERE and HAVING clause works in the same way except the additional feature that HAVING clause is popular for. HAVING clause can efficiently work with aggregate functions while WHERE cannot be operated with aggregate functions.