Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about what is the difference between where and having in oracle. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1.where cannot be put behind GROUP BY

2.HAVING is used in conjunction with GROUP BY and placed behind GROUP BY, which is equivalent to WHERE.

There can be no aggregate functions in the conditions after 3.WHERE, such as SUM (), AVG (), etc., while HAVING can

Where and Having are both a filter for query results, and the written point is the statement that sets the condition. The usage and similarities and differences are explained below.

Note: this article uses the emp table under the default user scott in the oracle database, where sal represents employee salary and deptno represents department number.

I. aggregate function

Before we explain, let's take a look at aggregate functions: aggregate functions are sometimes called statistical functions, and their function is usually to count a group of data, such as maximum, minimum, total, and average (

MAX,MIN,COUNT, AVG), etc. The fundamental difference between these functions and other functions is that they generally act on multiple records. A simple example: SELECT SUM (sal) FROM emp, where the SUM function is to count the sum of the sal (salary) fields in the emp table. The result is that the query returns only one result, that is, the sum of wages. By using the GROUP BY clause, you can make functions such as SUM and COUNT work on data that belongs to a group.

II. Where clause

Where is used only for values returned from the from clause, and every row of data returned by the from clause is judged and filtered by the criteria in the where clause. The comparison operator (>, =, 10000) is allowed in the where clause

The screening results are as follows:

DEPTNO SUM (SAL)

--

20 10875

Of course, this result is exactly what we want.

Fourth, let's further understand them through the comparison of where clause and having clause.

The aggregate statement (sum,min,max,avg,count) takes precedence over the having clause in the query process, which is simply understood to mean that I can only perform filtering after I have the statistical results. The where clause takes precedence over the aggregate statement (sum,min,max,avg,count) during the query because it is filtered sentence by sentence. The HAVING clause allows us to filter the data of each group after being filtered into groups. While the WHERE clause filters records before aggregating For example, now we want a department whose department number is not equal to 10 and whose total salary is greater than 8000?

We analyze it this way: we screen out the departments whose department number is not 10 through the where clause, and then count the salaries of the departments, and then use the having clause to screen the statistical results.

Select deptno,sum (sal) from emp where deptnotated trees 10 'group by deptnohaving sum (sal) > 8000

The screening results are as follows:

DEPTNO SUM (SAL)

--

30 9400

20 10875

Without much explanation, this simple example is a good illustration of the role of where and having.

5. Similarities and differences

The similarity is that search conditions are defined, except that the where clause is filtered for a single filter, while the having clause is related to a group, not to a single row.

Finally: the best way to understand the having clause and the where clause is the processing order of those sentences in the basic select statement: the where clause can only receive data output from the from clause, while the having clause can accept input from the group by,where or from clause.

After reading the above, do you have any further understanding of the difference between where and having in oracle? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.