Which Clause Is Used To Filter The Results Of Aggregate Functions?

next → ← prev

Difference between WHERE and HAVING

The WHERE and HAVING clauses are discussed in depth in this article. They're besides used to filter records in SQL queries. The departure between the WHERE and HAVING clause is the nigh mutual question posed during an interview time. The main difference betwixt them is that the WHERE clause is used to specify a condition for filtering records before any groupings are fabricated, while the HAVING clause is used to specify a condition for filtering values from a grouping. 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 status when retrieving records from a single table or multiple tables using the Join clause. If the specified condition is satisfied, information technology returns the item value from the table. The WHERE clause places weather condition on the selected columns.

The WHERE clause in MySQL can too 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 utilize the comparison operators as their operands like <, <=, >, >=, =, and <>. The comparing operators are ordinarily used to compare strings and arithmetics expressions.

The following syntax illustrates the use of the WHERE clause:

Allow us take an example to empathise this clause. Suppose we have a table named employees that incorporate the following data:

If we want to get the employee particular whose working hours are greater than 9, so we tin use the statement as follows:

We will get the below output where we tin see employee item whose working hours are greater than 9:

If we employ the in a higher place query with the Grouping Past clause, we volition get the different upshot:

Here is the output:

HAVING Clause

HAVING clause in MySQL used in conjunction with GROUP Past clause enables us to specify conditions that filter which group results appear in the result. Information technology returns only those values from the groups in the last issue that fulfills certain atmospheric condition. We can also utilise the WHERE and HAVING clause together during selection. In this case, WHERE clause commencement filters the individual rows, so rows are grouped, performs amass calculations, and at last HAVING clause filter the groups.

This clause places conditions on groups created by the GROUP Past clause. It behaves like the WHERE clause when the SQL statement does not use the GROUP BY keyword. Nosotros can employ the aggregate (grouping) functions such as SUM, MIN, MAX, AVG, and COUNT merely with two clauses: SELECT and HAVING.

The following syntax illustrates the use of the HAVING clause:

Let us have an example to sympathise this clause. Here we are because the aforementioned 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 utilise 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 explicate the main differences between database and schema:

WHERE clause filters individual rows, whereas the HAVING clause filters groups instead of 1 row at a time.
We cannot utilise the WHERE clause with aggregate functions because information technology works for filtering individual rows. In contrast, HAVING tin works with aggregate functions because information technology 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 Past, which means the HAVING clause filters rows after performing aggregate calculations. Consequently, HAVING is slower than WHERE in terms of efficiency and should exist avoided wherever possible.
We tin can combine the WHERE and HAVING clause together in a SELECT query. In this instance, the WHERE clause is used first to filter individual rows. The rows are then grouped, perform amass 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 start fetches whole data, and and so separation is done based on the specified condition.
Without a SELECT statement, we cannot use the HAVING clause. Conversely, nosotros tin 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 Nautical chart

The following comparing chart explains their main differences in a quick style:

Comparison Ground	WHERE Clause	HAVING Clause
Definition	Information technology is used to perform filtration on private rows.	Information technology 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 information from item rows based on the specified condition	The HAVING clause offset fetches the complete information. Information technology then separates them according to the given condition.
Aggregate Functions	The WHERE clause does not permit to work with aggregate functions.	The HAVING clause can work with amass functions.
Act every bit	The WHERE clause acts as a pre-filter.	The HAVING clause acts equally a postal service-filter.
Used with	Nosotros tin utilize the WHERE clause with the SELECT, UPDATE, and DELETE statements.	The HAVING clause can merely use with the SELECT argument.
GROUP Past	The GROUP BY clause comes afterward the WHERE clause.	The GROUP Past clause comes before the HAVING clause.

Decision

In this article, we have made a comparing 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 than popular. We tin can efficiently piece of work with aggregate functions in the HAVING clause while WHERE does not allow for aggregate functions.

Next Topic Where condition in SQL

← prev next →