WHERE and HAVING
The WHERE and HAVING clauses are discussed in depth in this article. They're also used to filter records in SQL queries. The difference between the WHERE and HAVING clause is the most common question posed during an interview time. The main difference between them is that the WHERE clause is used to specify a condition for filtering records before any groupings are made, while the HAVING clause is used to specify a condition for filtering values from a group. Before making the comparison, we will first know these SQL clauses.
WHERE Clause
The WHERE clause in MySQL is used with SELECT, INSERT, UPDATE, and DELETE queries to filter data from the table or relation. It describes a specific condition when retrieving records from a single table or multiple tables using the JOIN clause. If the specified condition is satisfied, it returns the particular value from the table. The WHERE clause places conditions on the selected columns.
The WHERE clause in MySQL can also implement the logical connectives AND, OR, and NOT. They are known as the Boolean condition that must be true to retrieve the data. The logical connectives expressions use the comparison operators as their operands like <, <=, >, >=, =, and <>. The comparison operators are usually used to compare strings and arithmetic expressions.
The following syntax illustrates the use of the WHERE clause:
FROM table_name
WHERE conditions
GROUP BY column_lists;
Let us take an example to understand this clause. Suppose we have a table named employees that contain the following data:
If we want to get the employee detail whose working hours are greater than 9, then we can use the statement as follows:
WHERE working_hour > 9;
We will get the below output where we can see employee detail whose working hours are greater than 9:
If we use the above query with the GROUP BY clause, we will get the different result:
WHERE working_hour > 9
GROUP BY name;
Here is the output:
HAVING Clause
HAVING clause in MySQL used in conjunction with GROUP BY clause enables us to specify conditions that filter which group results appear in the result. It returns only those values from the groups in the final result that fulfills certain conditions. We can also use the WHERE and HAVING clause together during selection. In this case, WHERE clause first filters the individual rows, then rows are grouped, performs aggregate calculations, and at last HAVING clause filter the groups.
This clause places conditions on groups created by the GROUP BY clause. It behaves like the WHERE clause when the SQL statement does not use the GROUP BY keyword. We can use the aggregate (group) functions such as SUM, MIN, MAX, AVG, and COUNT only with two clauses: SELECT and HAVING.
The following syntax illustrates the use of the HAVING clause:
aggregate_function (expression)
FROM table_name
WHERE conditions
GROUP BY column_lists
HAVING condition;
Let us take an example to understand this clause. Here we are considering the same table employees for demonstration.
If we want to get the total working hours for each employee whose working time is greater than 6 hour, then we can use the statement as follows:
FROM employees
GROUP BY name
HAVING SUM(working_hour) > 6;
We will get the below output where we can see each employee total working hours:
Key Differences between WHERE and HAVING Clause
The following points explain the main differences between database and schema:
- WHERE clause filters individual rows, whereas the HAVING clause filters groups instead of one row at a time.
- We cannot use the WHERE clause with aggregate functions because it works for filtering individual rows. In contrast, HAVING can works with aggregate functions because it is used to filter groups.
- Row operations are handled by the WHERE clause, while the HAVING clause handles column operations to summarized rows or groups.
- WHERE comes before GROUP BY, which means WHERE clause filter rows before performing aggregate calculations. HAVING comes after GROUP BY, which means the HAVING clause filters rows after performing aggregate calculations. Consequently, HAVING is slower than WHERE in terms of efficiency and should be avoided wherever possible.
- We can combine the WHERE and HAVING clause together in a SELECT query. In this case, the WHERE clause is used first to filter individual rows. The rows are then grouped, perform aggregate calculations, and finally, the HAVING clause is used to filter the groups.
- The WHERE clause retrieves the desired data based on the specified condition. On the other hand, the HAVING clause first fetches whole data, and then separation is done based on the specified condition.
- Without a SELECT statement, we cannot use the HAVING clause. Conversely, we can use a WHERE with SELECT, UPDATE, and DELETE statements.
- WHERE clause is a pre-filter, whereas HAVING clause is a post-filter.
WHERE vs. HAVING Comparison Chart
The following comparison chart explains their main differences in a quick manner:
| Comparison Basis | WHERE Clause | HAVING Clause |
|---|---|---|
| Definition | It is used to perform filtration on individual rows. | It is 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 data from particular rows based on the specified condition | The HAVING clause first fetches the complete data. It then separates them according to the given condition. |
| Aggregate Functions | The WHERE clause does not allow to work with aggregate functions. | The HAVING clause 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 clause with the SELECT, UPDATE, and DELETE statements. | The HAVING clause can only use with the SELECT statement. |
| GROUP BY | The GROUP BY clause comes after the WHERE clause. | The GROUP BY clause comes before the HAVING clause. |
Conclusion
In this article, we have made a comparison between the WHERE and HAVING clause. Here, we conclude that both clauses work in the same way in filtering the data, except some additional feature makes the HAVING clause more popular. We can efficiently work with aggregate functions in the HAVING clause while WHERE does not allow for aggregate functions.
