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
    explained before we first understand the aggregate function: Aggregate functions are sometimes called statistical functions, they are often 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. 
    
      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. 
   three, 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? 
  first step:     

Select Deptno, bydeptno;

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 having sum (SAL) >< Span style= "color: #800000; Font-weight:bold; " >10000;          

filter results are as follows:
deptno   SUM (SAL)
Span style= "font-size:15px;" >----------------
    20      10875
Four, below we further understand them by comparing the WHERE clause to the HAVING clause.
    aggregate statements during query (Sum,min,max,avg,count) is preferable to the HAVING clause, it is simply understood that I can only perform a filter if I have 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 filtering out departments with a department number not 10 through the WHERE clause, then counting the department's payroll, and then using the HAVING clause to filter the statistical results.

 select deptno,sum (SAL) from emp 
 where Deptno != 10 ' 

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.

Transfer from http://www.cnblogs.com/fuchongjundream/

Differences in the WHERE clause and the HAVING clause in Oracle