Shulou(Shulou.com)05/31 Report--

This article shows you what is the difference between where and having clauses in mysql. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The usage of having

Having sentences allow us to filter all kinds of data after grouping. Where sentences filter records before aggregation, that is, they act in front of group by and having sentences. The having clause filters the group records after aggregation.

SQL instance:

First, display the total population and total area of each area.

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

First, the returned records are divided into groups with region, which is the literal meaning of GROUP BY. After the group is divided into groups, then use the aggregate function to compare each group

Perform operations on different fields (one or more records) of

Second, display the total population and total area of each region. Only those areas with an area over 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 regions, because such a record does not exist in the table.

On the contrary, the having clause allows us to filter groups of data

Mysql determines the length of a field:

Select home_page from aaa table where char_length (trim (home_page)) 1

The difference between where and having clauses in mysql

Both the where and having clauses in http://hi.baidu.com/ww999/blog/item/f330c8d3073107d0a8ec9a72.htmlwwhttp://hi.baidu.com/ww999/blog/item/f330c8d3073107d0a8ec9a72.htmlmysql can filter records, but there are some differences in their usage. Take a look at an example:

Use the combination of group by and having clauses to find out the records that are not duplicated. The sql is as follows:

Select uid,email,count (*) as ct from `edm_ user081217` GROUP BY email

And then look at this, it's easy to understand.

Select uid,email,count (*) as ct from `edm_ user081217` GROUP BY email HAVING ct > 1

First use group by to group email, and then use having to filter those greater than 1, so that you can find duplicate records.

Here are the differences between having and where:

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

The object of action is different. The WHERE clause acts on tables and views, and the HAVING clause acts on groups.

WHERE selects input rows before grouping and aggregation calculation (therefore, it controls which rows enter the aggregation calculation), while HAVING selects grouped rows after grouping and aggregation. Therefore, the WHERE clause cannot contain an aggregate function; there is no point in trying to use an aggregation function to determine which rows are input to the aggregation operation. In contrast, the HAVING clause always contains aggregate functions. (strictly speaking, you can write HAVING clauses that do not use aggregation, but this is in vain. The same conditions can be used more effectively in the WHERE phase. )

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

To sum up:

Having generally follows group by and executes part of the record group selection to work.

Where executes all the data to work.

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

What is the difference between the where clause and the having clause in mysql? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.