Difference between 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.
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:
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:
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:
Here is the output:
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:
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:
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 vs. HAVING Comparison Chart
The following comparison chart explains their main differences in a quick manner:
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.