Having usage
The having clause allows us to filter various data After grouping. The having clause is slower than the aggregate statement (sum, min, max, avg, count) in the query process ). the where clause is faster than the aggregate statement (sum, min, max, avg, count) in the query process ).
SQL instance:
1. display the total population and total area of each region.
Copy codeThe Code is as follows:
SELECT region, SUM (population), SUM (area) FROM bbc group by region
First, return records are divided into multiple groups BY region, which is the literal meaning of group. After grouping, use the aggregate function
For calculation.
2. Show the total population and total area of each region. Only show the regions with an area of more than 1000000 square meters.
Copy codeThe Code is 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 regions, because such a record does not exist in the table.
Differences between the where and having clauses in mysql
The where and having clauses in mysql can both implement the Filter Record function, but their usage is still different. For example:
Use the group by clause and having clause to detect records that are not repeated. The SQL statement is as follows:
Select uid, email, count (*) as ct from 'edm _ user081217 'group by email
Then you can easily understand this.
Select uid, email, count (*) as ct from 'edm _ user081217 'group by email HAVING ct> 1
Use group by to group emails and use having to filter records greater than 1.
The differences between having and where are as follows::
Select city FROM weather WHERE temp_lo = (SELECT max (temp_lo) FROM weather );
Different objects. The WHERE clause acts on tables and views, and the HAVING clause acts on groups.
WHERE selects the input row before grouping and clustering calculation (therefore, it controls which rows enter clustering calculation), while HAVING selects the row of the group after grouping and clustering. Therefore, the WHERE clause cannot contain clustering functions, because it is meaningless to try to use clustering functions to judge the rows input to clustering operations. Conversely, HAVING clauses always contain aggregate functions. (Strictly speaking, you can write a HAVING clause that does not use clustering, but it is only effort-consuming. The same condition can be used more effectively in the WHERE stage .)
In the previous example, we can apply the city name restriction in the WHERE clause because it does not need to be clustered. This is more efficient than adding restrictions to HAVING, because we avoid grouping and clustering computing for rows that do not pass the WHERE check.
To sum up:
Having generally follows group by and executes part of the selected record group to work.
Where is used to execute all data.
In addition, having can be used as an aggregate function, such as having sum (qty)> 1000.