«Back to Home

Oracle Jump Start
Topics

How To Use Nested Queries In Oracle

Description

Oracle has an ability to nest queries within one another. A sub query is a select statement that is nested within another select statement and which returns intermediate results. SQL first evaluates the inner query within the where clause. The inner query generates values that are tested in the predict of the outer query, determining when it will be true. The return value of inner query is then substituted in the condition of the outer query.
  • The result of inner query is dynamically substituted in the condition of outer query.
  • There is no practical limitation to the level of nesting of queries in Oracle.
  • When using relational operators, ensure that the subquery returns a single column output.
  • In some cases, the DISTINCT clause can be used to ensure single valued output.
Example
  1. Select Dept_no from Employee  
  2. where Emp_name=’King’;  
1
 
Example
  1. Select Emp_name from Employee  
  2. Where Dept_no=104;  
2

Combine the above two queries
  1. Select emp_name from employee  
  2. where Dept_no = (select Dept_no from Employee where Emp_name=’King’);  
3

List the name of the employee which is the highest salary
  1. select emp_name from employee  
  2. where salary = (select max (salary) from employee);  
4

Using Aggregate Function in Subquery

Aggregate function produces single value for any number of rows. We want to see all employee detail salary is greater than average salary of employees whose hire date is before ‘01-SEP-08’.
For this we need to use aggregate function in inner query.
 
Example 
  1. Select * from Employee  
  2. where salary >   
  3. (select avg(salary) from employee  
  4. where Emp_hiredate< ‘01-SEP-08’);  
5 

Subquery with in having clause

You can also use subquery within the having clause.
these subqueries can use their own aggregate functions as long as they do not produce multiple values or use GROUP BY or HAVING themselves.

Example
  1. select Emp_job, AVG(salary)  
  2. from Employee  
  3. GROUP BY Emp_job  
  4. HAVING AVG(salary) = (select max(AVG(salary)) from employee GROUP BY Emp_job);  
6

Distinct Clause with Subqueries

Distinct clause is used in some cases to force a sub query to generate a single value. Suppose we want to find the details of the employee whose emp_id ‘7005’.

Example
  1. Select * from employee  
  2. where emp_id = (select distinct emp_id from employee where dept_no = ‘105’);  
7

Correlated subquery

A correlated subquery is a nested subquery which is executed once ‘candidate row’ considered by the main query and which on execution uses a value from a column in the outer query.
In a correlated subquery, the column value used in inner subquery refers to the column value present in the outer query forming a correlated subquery.

Example
  1. Select Emp_id, Emp_name, salary, dept_no  
  2. from employee  
  3. where salary > (select AVG(salary) from employee where dept_no = Dept_no);  
8

Summary
Thus, we learnt, Oracle has an ability to nest queries within one another. A sub query is a select statement, which is nested within another select statement, which returns the intermediate results. We learnt, how to use the nested queries in Oracle with the examples.