Subquery In Oracle

A Subquery or Inner query or Nested query is the query within query is referred as subquery.

To study subquery we use “employee” and “department” table as example.

Selecting employee table



Selecting department table



Subquery are of two types
1. Co-related Subquery
2. Non Co-related Subquery

1. Co-related Subquery
Correlated Subquery is a subquery that uses values from the outer query. In this case the inner query has to be executed for every row of outer query.

Use of Co-related Subquery

Now if we want to display the Employee ID,Employee Name,Salary of all employees whose salary is above average for their departments.



Note : In Co-related subquery inner will never executed without outer query.

Use of Co-related Subquery

Now if we want to display the Employee ID,Employee Name,Salary of all employees whose salary is above minimum for their departments.



Use of Co-related Subquery

Now if we want to display the Employee ID,Employee Name,Salary of all employees whose salary is below maximum for their departments.



2. Non Co-related Subquery

Non Co-related Subquery is the subquery in which first inner query executed and based upon it's result outer query return it's result.

Syntax for Non Co-related Subquery
SELECT columnname
FROM tablename
WHERE columnname1 IN
(SELECT columnname1
FROM tablename
WHERE columnname2 = “value”);

Use of NON Co-related Subquery

Now if we want to display the Employee Name and Salary of all employees work in “HR” department.



Use of NON Co-related Subquery

Now if we want to display the Employee Name and Salary of all employees work in “Accounting” department.



Use of NON Co-related Subquery

Now if we want to display the Employee Name and Salary of all employees work in “Accounting” department or “HR” department.



I hope this article will help you