the use of having
The HAVING clause allows us to filter the various data after the group, and the HAVING clause is slower than the aggregation statement (Sum,min,max,avg,count) in the query process. The WHERE clause is faster than the aggregation statement (SUM,MIN,MAX,AVG, Count).
SQL instance:
first, show the total number and area of each area．
Copy Code code as follows:
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, then the aggregate function is used for each group
The different fields (one or more records) are calculated.
Show the total population number and area of each area. Show only those areas with an area of more than 1000000。
Copy Code code as follows:
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.
The difference between where and having clauses in MySQL
The WHERE and having clauses in MySQL can be used to filter records, but there are some differences in their usage, look at one example:
Use a combination of GROUP BY and having clauses to isolate records that are not duplicates, as in sql:
Select Uid,email,count (*) as CT from ' edm_user081217 ' GROUP by email
And look at this, it's easy to understand.
Select Uid,email,count (*) as CT from ' edm_user081217 ' GROUP by email has ct > 1
Use GROUP by to group email, use having to filter more than 1, so find out is duplicate record.
The following is the difference between having and where：
Select city from weather WHERE Temp_lo = (select Max (Temp_lo) from weather);
The object of the action is different. The WHERE clause acts on the table and view, and the HAVING clause acts on the group.
Where the input row is selected before grouping and aggregating calculations (therefore, it controls which rows enter the aggregation calculation), and having the row selected for grouping and aggregating. Therefore, the WHERE clause cannot contain a clustered function, because it makes no sense to attempt to use the aggregate function to determine which row inputs are to the aggregation operation. Instead, the HAVING clause always contains a clustered function. (Strictly speaking, you can write a HAVING clause that does not use aggregation, but this is a waste of effort.) The same conditions can be used more effectively in the WHERE phase. ）
In the previous example, we can apply the city name restriction in the where, because it does not need to be aggregated. This is more efficient than adding restrictions in the having, because we avoid grouping and aggregating the rows that do not pass the where check
To sum up：
Having generally follows group by, performs a portion of the selection of the record group to work.
Where is the execution of all data to work.
Moreover having can use aggregate functions, such as having sum (qty) >1000
