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.
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:
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:
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
As per result, We will get the below output where we can see employee detail whose Department ID are greater than 11:
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:
GROUP BY column_lists
Demo Table: Magazine
Let us take an example to know the HAVING clause. Here we are considering another table Magazine for demonstration.
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;
WHERE Vs. HAVING Keyword Comparison Chart
It can be used to perform filtration on individual rows.
It can be used to perform filtration on groups.
It is implemented in row operations.
It is implemented in column operations.
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.
The WHERE keyword doesn’t permit to work with aggregate functions.
The HAVING keyword can work with aggregate functions.
The WHERE clause acts as a pre-filter.
The HAVING clause acts as a post-filter.
We can use the WHERE keyword with the SELECT, UPDATE, & DELETE statements.
The HAVING keyword can only use with the SELECT statement.
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.
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.