Difference between having and where in Database SQL, sqlhaving

Difference between having and where in Database SQL, sqlhaving

"Where" is a constraint Declaration, Where is used to constrain the data in the database. Where is used before the result is returned, and Where cannot use aggregate functions.

"Having" is a filter statement that filters query results after the returned result set is queried. Aggregate functions can be used in Having.

Aggregate functions: Basic SQL functions. Aggregate functions calculate a group of values and return a single value. Except COUNT, All Aggregate functions ignore null values. Aggregate functions are often used with the group by clause of SELECT statements.

The Transact-SQL programming language provides the following Aggregate functions:

1. AVG returns the average value in the specified group. The null value is ignored.

Example: select prd_no, avg (qty) from sales group by prd_no

2. COUNT returns the number of items in the specified group.

Example: select count (prd_no) from sales

3. MAX returns the maximum value of the specified data.

Example: select prd_no, max (qty) from sales group by prd_no

4. MIN returns the minimum value of the specified data.

Example: select prd_no, min (qty) from sales group by prd_no

Before talking about the difference, we must first introduce the group by clause. before talking about the GROUP clause, we must first talk about the "aggregate function"-a special function in SQL language. For example, SUM, COUNT, MAX, and AVG. The fundamental difference between these functions and other functions is that they generally work on multiple records.
For example:

SELECT SUM(population) FROM vv_t_bbc ;

SUM is used in the population field of all returned records. The result is that only one result is returned for this query, that is, the total population of all countries.

BY using the group by clause, the SUM and COUNT functions can be used for a GROUP of data. When you specify group by region, only a GROUP of data belonging to the same region (region) will be returned, that is, all fields except region (region) in the table, only one value can be returned after SUM, COUNT, and other aggregate function operations.

Let's talk about "HAVING" and "WHERE ":

HAVING clause allows us to filter the data of each group after grouping. The WHERE clause filters records before aggregation. that is to say, the function is prior to the group by clause and HAVING clause, while the HAVING clause filters GROUP records after aggregation.

Let's still understand the group by and HAVING clauses through specific instances:

SQL instance:

　　1. display the total population and total area of each region:

SELECT region, SUM(population), SUM(area)FROM bbcGROUP BY region

First, return records are divided into multiple groups BY region, which is the literal meaning of group. After grouping, Aggregate functions are used to calculate different fields (one or more records) in each group.

　　2. The total population and total area of each region are displayed. Only those regions with over 1000000 population are displayed.

SELECT region, SUM(population), SUM(area)FROM bbcGROUP BY regionHAVING SUM(population)>1000000

[Note] 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 group data.

Ps: To sort by the sum field, add order by sum (population) desc/asc.

The preceding section describes the differences between having and where in Database SQL. I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in time!