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
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id ----------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- -------------- ---------- ------------- 100 Steven King SKING 515.123.4567 6/17/1987 AD_PRES 24000 90 101 Neena Kochhar NKOCHHAR 515.123.4568 6/18/1987 AD_VP 17000 100 90 102 Lex De Haan LDEHAAN 515.123.4569 6/19/1987 AD_VP 17000 100 90 103 Alexander Hunold AHUNOLD 590.423.4567 6/20/1987 IT_PROG 9000 102 60 104 Bruce Ernst BERNST 590.423.4568 6/21/1987 IT_PROG 6000 103 60 105 David Austin DAUSTIN 590.423.4569 6/22/1987 IT_PROG 4800 103 60 106 Valli Pataballa VPATABAL 590.423.4560 6/23/1987 IT_PROG 4800 103 60 107 Diana Lorentz DLORENTZ 590.423.5567 6/24/1987 IT_PROG 4200 103 60 108 Nancy Greenberg NGREENBE 515.124.4569 6/25/1987 FI_MGR 12000 101 100 .......... 206 William Gietz WGIETZ 515.123.8181 10/1/1987 AC_ACCOUNT 8300 205 110
Sample table: jobs
| JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY |
|---|---|---|---|
| AD_PRES | President | 20000 | 40000 |
| AD_VP | Administration Vice President | 15000 | 30000 |
| AD_ASST | Administration Assistant | 3000 | 6000 |
| FI_MGR | Finance Manager | 8200 | 16000 |
| FI_ACCOUNT | Accountant | 4200 | 9000 |
| AC_MGR | Accounting Manager | 8200 | 16000 |
| AC_ACCOUNT | Public Accountant | 4200 | 9000 |
| SA_MAN | Sales Manager | 10000 | 20000 |
| PR_REP | Public Relations Representative | 4500 | 10500 |
SQL Code:
-- Selecting job_id and the average salary from the employees table
SELECT job_id, AVG(salary)
-- Grouping the results by job_id
FROM employees
GROUP BY job_id
-- Filtering the grouped results based on a condition
HAVING AVG(salary) <
(
-- Selecting the maximum of the average of min_salary from the jobs table
SELECT MAX(AVG(min_salary))
-- From the jobs table
FROM jobs
-- Filtering jobs where the job_id is in the subquery result
WHERE job_id IN
(
-- Selecting job_id from job_history table
SELECT job_id
-- Filtering job_history where department_id is between 50 and 100
FROM job_history
WHERE department_id BETWEEN 50 AND 100
)
-- Grouping the results by job_id from the subquery
GROUP BY job_id
);
The above code is executed in Oracle 11g Express Edition.
Explanation:
- This SQL query calculates the average salary for each job_id from the "employees" table.
- It groups the results by job_id.
- The HAVING clause filters the grouped results based on a condition.
- The condition compares the average salary for each job_id with the maximum average of the minimum salary for jobs within specific department_ids.
- The subquery selects the maximum of the average of min_salary from the "jobs" table, filtering it based on the job_ids present in the subquery result.
- The subquery within the WHERE clause selects job_ids from the "job_history" table where the department_id falls between 50 and 100.
- The main query then selects job_ids and their corresponding average salaries where the average salary is less than the maximum average of the minimum salary for jobs within specific department_ids.
or
-- Selecting job_id and the average salary from the employees table
SELECT job_id, AVG(salary)
-- Grouping the results by job_id
FROM employees
GROUP BY job_id
-- Filtering the grouped results based on a condition
HAVING AVG(salary) <
(
-- Selecting the maximum of the myavg column from the subquery
SELECT MAX(myavg)
-- Selecting job_id and average minimum salary as myavg from the jobs table
FROM (SELECT job_id, AVG(min_salary) as myavg
-- From the jobs table
FROM jobs
-- Filtering jobs where the job_id is in the subquery result
WHERE job_id IN
(
-- Selecting job_id from job_history table
SELECT job_id
-- Filtering job_history where department_id is between 50 and 100
FROM job_history
WHERE department_id BETWEEN 50 AND 100
)
-- Grouping the results by job_id
GROUP BY job_id) ss
);
The above code is executed in PostgreSQL 9.3
Explanation:
- This SQL query calculates the average salary for each job_id from the "employees" table.
- It groups the results by job_id.
- The HAVING clause filters the grouped results based on a condition.
- The condition compares the average salary for each job_id with the maximum average of the minimum salary for jobs within specific department_ids.
- The subquery selects the maximum value from the "myavg" column.
- This subquery selects job_ids and their corresponding average minimum salaries, filtering it based on the job_ids present in the subquery result.
- The subquery within the WHERE clause selects job_ids from the "job_history" table where the department_id falls between 50 and 100.
- The main query then selects job_ids and their corresponding average salaries where the average salary is less than the maximum average of the minimum salary for jobs within specific department_ids.
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
Let's break the example down into three parts and observes the results returned.
Atfirst the nested subquery as follows:
SQL Code:
-- Selecting job_id from the job_history table
SELECT job_id
-- Filtering job_history records based on department_id
WHERE department_id
-- Specifying the range for department_id filtering
BETWEEN 50 AND 100;
Explanation:
- This SQL query retrieves job_ids from the "job_history" table.
- It filters the records based on the department_id.
- The BETWEEN operator is used to specify a range for department_id filtering, including values between 50 and 100.
- The query returns job_ids associated with department_ids falling within the specified range.
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 visual 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:
-- Selecting the maximum of the average of min_salary from the jobs table
SELECT MAX(AVG(min_salary))
-- From the jobs table
FROM jobs
-- Filtering jobs based on job_id
WHERE job_id
-- Specifying the list of job_ids for filtering
IN (
'ST_CLERK', 'ST_CLERK', 'IT_PROG', 'SA_REP', 'SA_MAN', 'AD_ASST', 'AC_ACCOUNT'
)
-- Grouping the results by job_id
GROUP BY job_id;
Explanation:
- This SQL query calculates the average minimum salary for each job_id from the "jobs" table.
- It first filters the jobs based on specific job_ids listed.
- The MAX() function is then applied to get the maximum value of the average minimum salary across all the job_ids.
- The GROUP BY clause groups the results by job_id, allowing the calculation of the average minimum salary for each job_id separately.
- The query returns the maximum value among these averages.
Here is the output:
Output:
MAX(AVG(MIN_SALARY))
--------------------
10000
Here is the Visual 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:
-- Selecting job_id and the average salary from the employees table
SELECT job_id, AVG(salary)
-- Grouping the results by job_id
FROM employees
GROUP BY job_id
-- Filtering the grouped results based on a condition
HAVING AVG(salary) < 10000;
Explanation:
- This SQL query calculates the average salary for each job_id from the "employees" table.
- It groups the results by job_id.
- The HAVING clause filters the grouped results based on a condition.
- The condition specifies that only those records should be selected where the average salary for each job_id is less than 10000.
- The query returns job_ids and their corresponding average salaries where the average salary is less than 10000.
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
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
........
200102 2000 300 25-MAY-08 C00012 A012
Sample table : customer
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | ........ | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
Sample table : agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | ....... | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
SQL Code:
-- Selecting specific columns from the orders table
SELECT ord_num, ord_date, ord_amount, advance_amount
-- Filtering orders based on multiple conditions
FROM orders
-- Specifying conditions for filtering
WHERE ord_amount > 2000
-- Filtering based on order amount greater than 2000
AND ord_date < '01-SEP-08'
-- Filtering based on order date before 01-SEP-08
AND ADVANCE_AMOUNT <
-- Comparing advance_amount with values from a subquery result
ANY(
SELECT OUTSTANDING_AMT
-- Selecting outstanding amounts from the CUSTOMER table
FROM CUSTOMER
-- Specifying conditions for filtering customers
WHERE GRADE = 3
-- Filtering based on customer grade being 3
AND CUST_COUNTRY <> 'India'
-- Filtering based on customer country not being India
AND opening_amt < 7000
-- Filtering based on opening amount less than 7000
AND EXISTS
-- Checking for existence of records in a subquery
(
SELECT *
-- Selecting all columns from the agents table
FROM agents
-- Specifying conditions for filtering agents
WHERE commission < .12
-- Filtering based on commission less than 0.12
)
);
Explanation:
- This SQL query retrieves specific columns from the "orders" table.
- It filters the orders based on multiple conditions:
- Orders with ord_amount greater than 2000.
- Orders with ord_date before 01-SEP-08.
- Orders with advance_amount less than any of the values retrieved from a subquery.
- The subquery retrieves outstanding amounts from the CUSTOMER table based on several conditions.
- The conditions include GRADE being 3, CUST_COUNTRY not being 'India', and opening_amt less than 7000.
- The subquery also includes an EXISTS clause to check for the existence of records in another subquery.
- This inner subquery selects all columns from the agents table and filters them based on the commission being less than 0.12.
- Overall, the query selects orders that meet the specified conditions on ord_amount, ord_date, advance_amount, and the results of the subquery.
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
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:
-- Selecting outstanding amounts from the CUSTOMER table
SELECT OUTSTANDING_AMT
-- Filtering customers based on multiple conditions
FROM CUSTOMER
-- Specifying conditions for filtering customers
WHERE GRADE = 3
-- Filtering based on customer grade being 3
AND CUST_COUNTRY <> 'India'
-- Filtering based on customer country not being India
AND opening_amt < 7000
-- Filtering based on opening amount less than 7000
AND EXISTS(
-- Checking for existence of records in a subquery
SELECT *
-- Selecting all columns from the agents table
FROM agents
-- Specifying conditions for filtering agents
WHERE commission < .12
-- Filtering based on commission less than 0.12
);
Explanation:
- This SQL query retrieves outstanding amounts from the CUSTOMER table.
- It filters the customers based on multiple conditions:
- Customers with GRADE equal to 3.
- Customers with CUST_COUNTRY not equal to 'India'.
- Customers with opening_amt less than 7000.
- The query also includes an EXISTS clause to check for the existence of records in a subquery.
- The subquery selects all columns from the agents table and filters them based on the commission being less than 0.12.
- Overall, the query selects outstanding amounts from customers who meet the specified conditions on GRADE, CUST_COUNTRY, opening_amt, and the existence of agents with a commission less than 0.12.
Output:
OUTSTANDING_AMT
---------------
6000
3000
5000
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
PREV : Correlated subqueries using aliases
NEXT :Union