The difference between where and having in a query

Aggregate statements (Sum,min,max,avg,count) are executed more than the HAVING clause in the query process. The WHERE clause takes precedence over the aggregate statement (SUM,MIN,MAX,AVG,COUNT) in the query process.

The HAVING clause is similar to where it is, but there is also a difference, which is the statement that sets the condition.

Aggregate statements (Sum,min,max,avg,count) are executed more than the HAVING clause in the query process. The WHERE clause takes precedence over the aggregate statement (SUM,MIN,MAX,AVG,COUNT) in the query process.

In short,

WHERE clause:

Select SUM (num) as RMB from the order where id>10

You can aggregate statements only if you first query for records with IDs greater than 10

HAVING clause:

Select ReportsTo as manager, COUNT (*) as reports from employees

GROUP BY ReportsTo has count (*) > 4

Take the Northwind library as an example. The having condition expression is an aggregation statement. Definitely say HAVING clause query process execution priority is lower than aggregate statement.

In other words, it would be an error to replace the previous having with a where. Aggregate statements are used to count grouped data.

The having is used to judge the grouped data again. Without these relationships there is no use of having. Just use the Where.

Having is to make up for where the problem is when the grouped data is judged. Because the where execution priority is faster than the aggregate statement.

Aggregate function, which is a special function that must be spoken first: such as Sum, COUNT, MAX, AVG, and so on. The fundamental difference between these functions and other functions is that they generally work on more than one record.

SELECT SUM (population) from TableName

The sum action here is on all population fields that return records, and the result is that the query returns only one result, that is, all

The total population of the country. By using the GROUP BY clause, you can have the sum and COUNT functions work on data that belongs to a group.

When you specify group by region, a set of data belonging to the same region (region) will only return one row of values.

That is, all fields except the region (region) in the table can only return a value after the aggregate function operation SUM, count, and so on.

The HAVING clause allows us to filter the groups of data after the group

The HAVING clause filters the group records after aggregation

The WHERE clause filters the records before aggregation. That is, before the GROUP BY clause and the HAVING clause

Look at the following examples:

First, show the total number and area of each area

SELECT region, sum (population), sum (area)

From BBC

GROUP by region

First, you divide the return records into groups by region, which is the literal meaning of group by. After the group is finished, the different fields (one or more records) in each group are calculated with aggregate functions.

Show the total population and area of each area. Only those areas with an area of more than 1000000 are shown.

SELECT region, sum (population), sum (area)

From BBC

GROUP by region

Having SUM (area) >1000000

Here, we cannot use where to filter more than 1000000 of the region because no such record exists in the table.

Instead, the HAVING clause allows us to filter the groups of data after the group.