Sub Query in Oracle: Part 2

2. Multiple Row Sub Query
 
A Multiple Row Sub Query returns a result of multiple rows to the outer/main/parent query. It includes the following operators:

1. IN
2. ANY
3. ALL or EXISTS

Example

SELECT e.first_name, e.salary
FROM employees e
WHERE salary IN ( SELECT MIN(e.salary) 
FROM employees e
GROUP BY e.department_id);

Execute the Query, then the result will be as in the following:

3. Multiple Column Sub Query
 
Multiple Column Sub Queries are queries that return multiple columns to the outer SQL query. It uses the IN operator for the WHERE and HAVING clause.
 
Example

SELECT e.department_id,  e.job_id,e.salary 

FROM employees e 

WHERE (e.job_id,  e.salary) IN ( SELECT  e.job_id, e.salary

FROM employees e

WHERE e.department_id = 50) ;

Execute the Query, then the result will be as in the following:

Note: We can use a Sub Query using a FROM clause in the main query.
 

SELECT e.first_name, e.salary, e.department_id, b.salary_avg 

FROM employees e,

(SELECT e1.department_id, AVg(e1.salary) salary_avg

FROM employees e1

GROUP BY e1.department_id) b 

WHERE  e.department_id = b.department_id  AND e.salary > b.salary_avg;

Execute the Query, then the result will be as in the following:

 
4. Nested Sub Query
 
When we write a Sub Query in a WHERE and HAVING clause of another Sub Query then it is called a nested Sub Query.

Example
 

SELECT e.first_name,e.salary

FROM employees e

WHERE e.manager_id in

( SELECT e.manager_id

FROM employees e

WHERE department_id in (select d.department_id

FROM departments d

WHERE d.department_name='Purchasing' ));

Execute the Query, then the result will be as in the following:

 
5. Correlated Sub Query

A Correlated Sub Query contains a reference to a table that appears in the outer query. It is used for row by row processing, in other words the Sub Query will execute row by row for the parent query.

Example
 

SELECT a.first_name||' '||a.last_name, a.department_id,

(SELECT b.first_name||' '||b.last_name

FROM employees b

WHERE b.employee_id in

(SELECT d.manager_id

FROM departments d

WHERE d.department_name='IT' ) ) as MANAGER

FROM employees a ;

Execute the Query, then the result will be as in the following:

 Previous article: Sub Query in Oracle: Part 1