1. nested subquery of Oracle

 

A nested subquery is an independent query that is not related to an external query. A subquery is executed only once, then execute the external query. The external query uses the subquery results during execution.
The following is an example of a nested subquery:
SelectEname, Sal
From EMP
Where SAL>
(Select AVG (SAL) from EMP );

Execution sequence:
The meaning is to find out the employees whose salaries are higher than the average in EMP.Name and salary.
Here, Oracle will first execute the select AVG (SAL) from EMP subquery and then execute the external query.
Note the following points for nested queries:
1. subqueries must be included in a pair of parentheses.
2. Place the subquery on the right of the comparison operator.
3. "order by" is not required in subqueries unless you want to use a top-n Analysis
4. When your subquery results are expected to have only one, use the single-line operator. When your subquery results are expected to have more than one, use the multi-line operator.
The so-called row operators are commonly used (>, <, >=, <=, <>, and so on). The so-called multi-row operators use in Oracle subqueries, all, any, some, not in, etc.
For example, the following SQL statement finds out the names and salaries of people whose deptno is 20.
Select ename, Sal
From EMP
Where empno in
(
Select empno from EMP where deptno = 20
);
Of course, here we do not need to use subqueries. Here we use subqueries because the subqueries here return multiple results and we must use the multi-line operator.

The efficiency of subqueries is lower than that of associated queries.