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
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Correlated subqueries using aliases
Next: Union
SQL: Tips of the Day
How to avoid the "divide by zero" error in SQL?
Select Case when divisor=0 then null Else dividend / divisor End ,,,
OR:
Select dividend / NULLIF(divisor, 0) ...
Ref: https://bit.ly/3dLj7gS
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook