mysql having usage analysis

Usage of having

The having clause allows us to filter various data after grouping, and the where clause filters records before aggregation, that is, it acts before the group by and having clauses. The having clause filters the group records after aggregation.

SQL Example:

1. Display the total population and total area of ​​each region.

SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region

First, use region to divide the returned records into multiple groups. This is the literal meaning of GROUP BY. After the groups are formed, aggregate functions are then used to operate on different fields in each group (one or more records).

2. Display the total population and total area of ​​each region. Only those regions with an area greater than 1,000,000 are displayed.

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000

Here, we cannot use where to filter regions with more than 1,000,000, because such a record does not exist in the table.

On the contrary, the having clause allows us to filter the groups of data 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;

Difference between where and having clauses in mysql

The where and having clauses in MySQL can both filter records, but there are some differences in their usage. Let's take a look at an example:

Use the group by and having clauses to find out the unique records. The SQL is as follows:

select uid,email,count(*) as ct from `edm_user081217` GROUP BY email

Then look at this, it is easy to understand

select uid,email,count(*) as ct from `edm_user081217` GROUP BY email HAVING ct > 1

First use group by to group the emails, and then use having to filter those with a value greater than 1, so that only duplicate records are found.

Following are the differences between having and where:

SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

The objects of action are different. The WHERE clause applies to tables and views, and the HAVING clause applies to groups.

WHERE selects input rows before grouping and aggregation (thus, it controls which rows go into the aggregate calculation), whereas HAVING selects grouped rows after grouping and aggregation. Therefore, the WHERE clause cannot contain aggregate functions; it does not make sense to try to use aggregate functions to determine which rows to input into the aggregate operation. In contrast, the HAVING clause always contains aggregate functions. (Strictly speaking, you can write a HAVING clause without using aggregates, but doing so is a waste of effort. The same condition can be used more efficiently in the WHERE phase.)

In the previous example, we can apply the city name restriction in the WHERE because it does not require aggregation. This is more efficient than adding a restriction in HAVING because we avoid grouping and aggregation calculations for rows that fail the WHERE check.

In summary:

Having usually follows group by and works as part of the record group selection.

Where is executed to work with all data.

Furthermore, having can use aggregate functions, such as having sum(qty)>1000

Summarize

The above is the analysis of mysql having usage introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!