Differences between the where clause and having clause in Oracle

The fields used in this article are the emp table under scott, the default user in the Oracle database. sal represents the employee's salary, and deptno represents the Department number.

I. Aggregate functions

Aggregate functions are also called statistical functions. They are used to collect statistics on a set of data, such as the maximum value, minimum value, total number, and average value, such as MAX, MIN, COUNT, and AVG. The fundamental difference between these functions and other functions is that they generally work on multiple records. For example, select sum (sal) FROM emp, where SUM is used to count the SUM of sal (salary) fields in the emp table. The result is that only one result is returned for this query, that is, the sum of wages. BY using the group by clause, SUM and COUNT functions can be used for a GROUP of data.

II. where clause

Where self-residence is only used to return values from the from clause. each row of data returned from the from clause is filtered by the conditions in the where clause. The where clause can use comparison operators (>,<,>=, <=, <> ,! = |) And logical operators (and, or, not ).

Iii. having clause

Having is used to further filter the results after group by is used for grouping statistics. Now you need to find the Department number with the total salary of more than 10000.

Step 1: select deptno, sum (sal) from emp group by deptno;

Step 2: select deptno, sum (sal) from emp group by deptno having sum (sal)> 10000;

Iv. Comparison between the where clause and having clause

The aggregate statement (sum, min, max, avg, count) takes precedence over the having clause in the query process. Simply put, we can filter the statement only after the statistical results are obtained. The where clause takes precedence over aggregate statements (sum, min, max, avg, count) in the query process, because it filters by sentence. HAVING clause allows us to filter the data of each group after grouping, while WHERE clause filters records before aggregation. Query the Department numbers of departments whose department number is not equal to 10 and whose total salary is greater than 8000. In the analysis, the where clause is used to filter out departments not numbered 10, and then calculate the Department salary. Then, the having clause is used to filter the statistical results.

Select deptno, sum (sal) from emp where deptno! = '10' group by deptno having sum (sal)> 8000;

V. Summary

The similarity is to define search conditions. The difference is that the where clause is a single filter, and the having clause is related to a group rather than a single row.

① Where cannot be placed behind GROUP

② HAVING is used together with group by and placed behind group by. At this time, the function is equivalent to WHERE

③ There cannot be Aggregate functions such as SUM () and AVG () in the conditions after WHERE, and HAVING can