SQL Nested subqueries

Nested subqueries

A subquery can be nested inside other subqueries. SQL has an ability to nest queries within one another. A subquery is a SELECT statement that is nested within another SELECT statement and which return intermediate results. SQL executes innermost subquery first, then next level. See the following examples :

Example -1 : Nested subqueries

If we want to retrieve that unique job_id and there average salary from the employees table which unique job_id have a salary is smaller than (the maximum of averages of min_salary of each unique job_id from the jobs table which job_id are in the list, picking from (the job_history table which is within the department_id 50 and 100)) the following SQL statement can be used :

Sample table: employees

Sample table: jobs

SQL Code:

SELECT job_id,AVG(salary) 
FROM employees   
GROUP BY job_id   
HAVING AVG(salary)<           
(SELECT MAX(AVG(min_salary))            
FROM jobs             
WHERE job_id IN                 
(SELECT job_id FROM job_history                  
WHERE department_id                   
BETWEEN 50 AND 100)             
GROUP BY job_id);

The above code is executed in Oracle 11g Express Edition.

or

SELECT job_id,AVG(salary) 
SELECT job_id,AVG(salary) 
FROM employees   
GROUP BY job_id   
HAVING AVG(salary)<
(SELECT MAX(myavg) from (select job_id,AVG(min_salary) as myavg
FROM jobs             
WHERE job_id IN                 
(SELECT job_id FROM job_history                  
WHERE department_id                   
BETWEEN 50 AND 100)            
GROUP BY job_id) ss);

The above code is executed in PostgreSQL 9.3

Output

JOB_ID     AVG(SALARY)
---------- -----------
IT_PROG           5760
AC_ACCOUNT        8300
ST_MAN            7280
AD_ASST           4400
SH_CLERK          3215
FI_ACCOUNT        7920
PU_CLERK          2780
SA_REP            8350
MK_REP            6000
ST_CLERK          2785
HR_REP            6500

Explanation:

This example contains three queries: a nested subquery, a subquery, and the outer query. These parts of queries are runs in that order.

Let's break the example down into three parts and observes the results returned.

Atfirst the nested subquery as follows:

SQL Code:

SELECT job_id FROM job_history  
WHERE department_id      
BETWEEN 50 AND 100;

This nested subquery retrieves the job_id(s) from job_history table which is within the department_id 50 and 100.

Here is the output.

Output:

JOB_ID
----------
ST_CLERK
ST_CLERK
IT_PROG
SA_REP
SA_MAN
AD_ASST
AC_ACCOUNT

Here is the pictorial representation of how the above output comes.

Now the subquery that receives output from the nested subquery stated above.

SELECT MAX(AVG(min_salary))
FROM jobs WHERE job_id 
IN(.....output from the nested subquery......)
GROUP BY job_id

The subquery internally works as follows:

SQL Code:

SELECT MAX(AVG(min_salary))
FROM jobs 
WHERE job_id 
IN(
'ST_CLERK','ST_CLERK','IT_PROG',	
'SA_REP','SA_MAN','AD_ASST',	'
AC_ACCOUNT')
GROUP BY job_id;

The subquery returns the maximum of averages of min_salary for each unique job_id return ( i.e. 'ST_CLERK','ST_CLERK','IT_PROG', 'SA_REP','SA_MAN','AD_ASST', 'AC_ACCOUNT' ) by the previous subquery.

Here is the output:

Output:

MAX(AVG(MIN_SALARY))
--------------------
               10000

Here is the pictorial representation of how the above output returns.

Now the outer query that receives output from the subquery and which also receives the output from the nested subquery stated above.

SELECT job_id,AVG(salary) 
FROM employees   
GROUP BY job_id   
HAVING AVG(salary)<   
(.....output from the subquery(         
output from the nested subquery)......)

The outer query internally works as follows:

SQL Code:

SELECT job_id,AVG(salary) 
FROM employees   
GROUP BY job_id   
HAVING AVG(salary)<10000;

The outer query returns the job_id, average salary of employees that are less than maximum of average of min_salary returned by the previous query

Output:

JOB_ID     AVG(SALARY)
---------- -----------
IT_PROG           5760
AC_ACCOUNT        8300
ST_MAN            7280
AD_ASST           4400
SH_CLERK          3215
FI_ACCOUNT        7920
PU_CLERK          2780
SA_REP            8350
MK_REP            6000
ST_CLERK          2785
HR_REP            6500

Example -2 : Nested subqueries

Here is an another nested subquery example.

Sample table : orders

Sample table : customer

Sample table : agents

SQL Code:

SELECT ord_num,ord_date,ord_amount,advance_amount
FROM orders 
WHERE ord_amount>2000 
AND ord_date<'01-SEP-08' 
AND ADVANCE_AMOUNT <         
ANY(SELECT OUTSTANDING_AMT		
FROM CUSTOMER	
WHERE GRADE=3 
AND CUST_COUNTRY<>'India'	
AND opening_amt<7000 
AND EXISTS		        
(SELECT * 				 
FROM agents				  
WHERE commission<.12));

Output:

   ORD_NUM ORD_DATE  ORD_AMOUNT ADVANCE_AMOUNT
---------- --------- ---------- --------------
    200130 30-JUL-08       2500            400
    200127 20-JUL-08       2500            400
    200110 15-APR-08       3000            500
    200105 18-JUL-08       2500            500
    200129 20-JUL-08       2500            500
    200108 15-FEB-08       4000            600
    200113 10-JUN-08       4000            600
    200106 20-APR-08       2500            700
    200109 30-JUL-08       3500            800
    200107 30-AUG-08       4500            900
    200101 15-JUL-08       3000           1000
    200128 20-JUL-08       3500           1500
    200114 15-AUG-08       3500           2000

Explanation:

The last Inner query will fetched the rows from agents table who have commission is less than .12%.

The 2nd last inner query returns the outstanding amount for those customers who are in grade 3 and not belongs to the country India and their deposited opening amount is less than 7000 and their agents should have earned a commission is less than .12%.

The outer query returns ord_num, ord_date, ord_amount, advance_amount for those orders from orders table which ord_amount is more than 2000 and ord_date before the '01-sep-08' and the advance amount may be the outstanding amount for those customers who are in grade 3 and not belongs to the country India and there deposited opening amount is less than 7000 and their agents should have earned a commission is less than .12%.

Let's break the code and analyze what's going on in inner query. Here is the first code of inner query with output :

SQL Code:

SELECT *
FROM agents
WHERE commission<.12;

Output:

AGENT_CODE AGENT_NAME      WORKING_AREA      COMMISSION PHONE_NO        COUNTRY
---------- --------------- ----------------- ---------- --------------- ---------
A009       Benjamin        Hampshair                .11 008-22536178
A002       Mukesh          Mumbai                   .11 029-12358964

Here is the second code of inner query (including first one) with output :

SQL Code:

SELECT OUTSTANDING_AMT
FROM CUSTOMER
WHERE GRADE=3 
AND CUST_COUNTRY<>'India' 
AND opening_amt<7000 
AND EXISTS(
SELECT * 
FROM agents
WHERE commission<.12);

Output:

OUTSTANDING_AMT
---------------
           6000
           3000
           5000

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Correlated subqueries using aliases
Next: Union