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
  1. SELECT e.first_name, e.salary  
  2. FROM employees e   
  3. WHERE salary IN ( SELECT MIN(e.salary)    
  4. FROM employees e   
  5. GROUP BY e.department_id); 
Execute the Query, then the result will be as in the following:
 
Multiple Row Subquery 
 
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.
  1. SELECT e.department_id,  e.job_id,e.salary   
  2. FROM employees e   
  3. WHERE (e.job_id,  e.salary) IN ( SELECT  e.job_id, e.salary  
  4. FROM employees e  
  5. WHERE e.department_id = 50) ; 
Execute the Query, then the result will be as in the following:
 
Multiple Column Subquery 
 
Note: We can use a Sub Query using a FROM clause in the main query.
  1. SELECT e.first_name, e.salary, e.department_id, b.salary_avg   
  2. FROM employees e,  
  3. (SELECT e1.department_id, AVg(e1.salary) salary_avg  
  4. FROM employees e1  
  5. GROUP BY e1.department_id) b   
  6. 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:
 
subquery using FROM clause 
 
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.
  1. SELECT e.first_name,e.salary  
  2. FROM employees e  
  3. WHERE e.manager_id in  
  4. SELECT e.manager_id  
  5. FROM employees e  
  6. WHERE department_id in (select d.department_id  
  7. FROM departments d  
  8. WHERE d.department_name='Purchasing' )); 
Execute the Query, then the result will be as in the following:
 
Nested Subquery 
 
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.
  1. SELECT a.first_name||' '||a.last_name, a.department_id,  
  2. (SELECT b.first_name||' '||b.last_name  
  3. FROM employees b  
  4. WHERE b.employee_id in  
  5. (SELECT d.manager_id  
  6. FROM departments d  
  7. WHERE d.department_name='IT' ) ) as MANAGER  
  8. FROM employees a ;
Execute the Query, then the result will be as in the following:
 
Correlated SubQuery 
 
Previous article: Sub Query in Oracle: Part 1