Differences in the WHERE clause and the HAVING clause in Oracle

1.where cannot be placed behind group by
2.HAVING is used in conjunction with group by, placed behind group by, at this time the role is equivalent to where
3.WHERE The following conditions cannot have aggregation functions, such as SUM (), AVG (), etc., and having can

Where and having are a kind of filter for the result of the query, and the written point is the statement that sets the condition.
The following points illustrate their usage and similarities and differences.
Note: The usage field is the EMP table under the default user Scott in the Oracle database, and Sal represents the employee's salary, Deptno represents the department number.
First, aggregate functions
Aggregate functions are sometimes called statistical functions, and they often function as statistics on a set of data, such as the maximum, minimum, total,
Average (Max,min,count, AVG) and so on. The fundamental difference between these functions and other functions is that they generally function on more than one record.
A simple example: SELECT sum (SAL) from EMP, where sum is the sum of the SAL (payroll) fields in the EMP table,
The result is that the query returns only one result, the sum of the wages. By using the GROUP BY clause, you can have the sum and COUNT functions work on data that belongs to a group.
Second, WHERE clause
The WHERE clause is used only for the value returned from the FROM clause, and each row of data returned by the FROM clause is filtered by the criteria in the WHERE clause.
The use of comparison operators (>,<,>=,<=,<>,!=|, etc.) and logical operators (And,or,not) is allowed in the WHERE clause.
Third, HAVING clause
The HAVING clause is usually used with the ORDER BY clause.
The role of having is to further filter the results of grouping by using group by.
For example: Now you need to find department numbers with a department's total payroll greater than 10000?
The first step:
Select Deptno,sum (SAL) from the EMP group by DEPTNO;
The filter results are as follows:
DEPTNO SUM (SAL)
------ ----------
30 9400
20 10875
10 8750
We can see the results we want. But now what if we want the sum of the department's wages to be greater than 10000?
So the thought of filtering the statistical results of the group has to help us complete.
Step Two:
Select Deptno,sum (SAL) from EMP Group by DEPTNO have sum (SAL) >10000;
The filter results are as follows:
DEPTNO SUM (SAL)
------ ----------
20 10875
Of course, the result is exactly what we want.
We further understand them by comparing the WHERE clause to the HAVING clause.
The aggregation statement (Sum,min,max,avg,count) is performed more preferentially in the query process than the HAVING clause, and it is simply understood that I can only perform the filter if I have the statistical results.
The WHERE clause performs precedence over the aggregation statement (Sum,min,max,avg,count) in the query process, because it is filtered by a sentence. Having clauses allows us to filter groups of data after filtering into groups.
The WHERE clause filters the records before aggregating.
For example: Now we want department numbers that are not equal to 10 of departments and the total wage is greater than 8000?
We analyze this by using a WHERE clause to filter out departments with a department number not 10, then counting the department's wages, and then filtering the statistical results with the HAVING clause.
Select Deptno,sum (SAL) from EMP where deptno!= ' GROUP by DEPTNO have sum (SAL) >8000;
The filter results are as follows:
DEPTNO SUM (SAL)
------ ----------
30 9400
20 10875
Not much to explain, this simple little example can be a good illustration of where and having the role.
V. Similarities and differences
They are similar in that they define search conditions, but differ in that the WHERE clause is a single filter and having clauses are related to groups, not to individual rows.
Finally: the best way to understand the HAVING clause and the WHERE clause is the order in which those sentences in the underlying SELECT statement are processed:
The WHERE clause can only receive data that is output from the FROM clause, while the HAVING clause accepts input from the group By,where or FROM clause.

Note: The contents of the essay are from the online data collation, for reference only.

Differences in the WHERE clause and the HAVING clause in Oracle