Precautions during Oracle subquery

We all know that we can use Oracle subqueries in HAVING, and the results of the two query statements can be used for set operations. If you want to know how we use Oracle subqueries in HAVING? And how can the results of the two query statements perform the actual operations on the set? The following are related answers.

1. the query statement can be nested.

Example: SELECT ...... FROM (SELECT ...... FROM table name 1, [Table name 2,...] WHERE condition) WHERE condition 2;

2. Where can I use Oracle subqueries?

When the query conditions are uncertain

Subqueries can also be used in DML (insert, update, delete) statements.

Subqueries are also available in HAVING.

3. The results of the two query statements can be set.

For example:

UNION (remove duplicate records)

Union all (do not remove duplicate records)

10

J2EE @ zxw

Difference set MINUS,

Intersection INTERSECT

4. subquery considerations

First, execute the SQL statement in the brackets, and layer by layer to the outside.

Internal query only once

If multiple result sets are returned IN the layer, you cannot use a comparison character such as <>== to use IN.

5. Example of Oracle subquery (1)

 
 

  
  
SQL> select title,avg(salary) from s_emp   
  
  
group by title Having avg(salary) =   
  
  
(select min(avg(salary)) from s_emp   
  
  
group by title);  
 
 

Find the job name and salary for the lowest average wage

Example of subquery (2)

The table name in the parent query can be used for Oracle subqueries.

This SQL statement is correct:

 
 

  
  
SQL>select cty_name from city where st_code in   
  
  
(select st_code from state where st_name='TENNESSEE' and   
  
  
city.cnt_code=state.cnt_code);  
 
 

Note: The subquery called by the parent query is executed only once.

6. Obtain the SQL statements from 80 to 100 of the result set.

Each result set processed by Oracle has only one ROWNUM field indicating its logical location,

You can only use ROWNUM <100, not ROWNUM> 80.

The following are two SQL statements with better results obtained from Oracle after analysis: 80 to 100

(ID is the field name with the unique keyword)

Statement Syntax:

 
 

  
  
SQL> select * from (
  
  
(Select rownum as numrow, c. * from (
  
  
Select [field_name,...]
From table_name where Condition 1 order by condition 2) c)
  
  
Where numrow> 80 and numrow <= 100)
 
 

Order by Condition 3;

The above content is an introduction to Oracle subqueries. I hope you will get some benefits.