Sub Query in Oracle: Part 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:

 

 

 

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.

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:

 

 

 

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.

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:

 

 

 

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.

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