Differences in the WHERE clause and the HAVING clause in Oracle

Differences in the WHERE clause and the HAVING clause in Oracle

1.where cannot be placed behind group by 2. Having is used together with group by, placed behind group by, at this time the function is equivalent to the where3.where behind the condition can not have aggregation function, such as SUM (), AVG (), and have 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: This article uses the EMP table below the default user Scott in the Oracle database, which represents the employee's salary and Deptno represents the department number.
First, aggregate functions
Before we understand the aggregate function: Aggregate functions are sometimes called statistical functions, and they are usually used to calculate 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. For example: SELECT sum (SAL) from EMP, where sum is counted as 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
Where the claim is used only for the value returned from the FROM clause, 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. Because everyone is familiar with the WHERE clause, we are not repeating it here.
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. , and 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.

Where deptno!= ' GROUP by Deptno

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, and the HAVING clause accepts a group BY, The input to the where or FROM clause.

Differences in the WHERE clause and the HAVING clause in Oracle