Mysql having and mysqlhaving

Mysql having and mysqlhaving
Having usage

Having clauses allow us to filter various data After grouping. where clauses filter records before aggregation, that is, before group by and having clauses. The having clause filters group records after aggregation.

SQL instance:

1. shows the total population and total area of each region.
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. The total population and total area of each region are displayed. Only those regions with an area exceeding 1000000 square meters are displayed.

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.
On the contrary, the having clause allows us to filter the data of each group after grouping.

Mysql determines the length of a field:

Select home_page from aaa table where char_length (trim (home_page) <10 and char_length (trim (home_page)> 1;

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.