The difference between where and having in SQL

A "where" is a constraint declaration that uses where to constrain the data of the database, where it works before the result is returned, and where the aggregate function cannot be used.

"Having" is a filter declaration that filters the results of a query after it returns a result set, and can use aggregate functions in the having.

Before we tell the difference, we have to introduce the GROUP BY clause, and before we say the group clause, we have to talk about a special function in the--sql language of the "aggregate function". such as Sum, COUNT, MAX, AVG, and so on. The fundamental difference between these functions and other functions is that they generally function on more than one record.

Such as:
SELECT SUM (population) from VV_T_BBC;

The SUM function here is on the population field of all returned records, and the result is that the query returns only one result, that is, the total population of all countries.

By using the GROUP BY clause, you can have the sum and COUNT functions work on the data that belongs to a group. When you specify group by regions, only one set of data that belongs to the same region will return a row of values, which means that all fields except region (regions) in the table are returned with a value only after the SUM, count, and other aggregate function operations.

The following say "having" and "WHERE":
The HAVING clause allows us to filter groups of data, where clauses filter records before aggregation. That is, it acts before the GROUP BY clause and the HAVING clause, whereas the HAVING clause filters the group records after aggregation.

Let's take a concrete example to understand the group BY and HAVING clauses:

SQL instance:

First, show the total population and area of each region:

SELECT region, sum (population), sum (area)
From BBC
GROUP by region

The return record is divided into groups with region first, which is the literal meaning of group by. After the group is finished, the different fields (one or more records) in each group are calculated with an aggregate function.

Second, show the total population and area of each area. Only those regions with a population of more than 1000000 are shown.

SELECT region, sum (population), sum (area)
From BBC
GROUP by region
Having SUM (population) >1000000

[note] Here, we cannot use where to filter more than 1000000 of the area, because there is no such record in the table.

Instead, the HAVING clause allows us to filter groups of data after the group.

PS: If you want to sort by the field after sum, you can add the following: Order by sum (population) desc/asc

This article is from the "Essays" blog, make sure to keep this source http://lishouxiang.blog.51cto.com/8123559/1854700

The difference between where and having in SQL