Nested subquery: A nested subquery has a main query. Inside the main query there’s a subquery and inside the subquery there’s a sub subquery.
SELECT COMPANY FROM CLIENTS WHERE EMPL_CLIENT IN (SELECT EMPL_N RETRIEVING DATA FROM EMPLOYEES WHERE EMPL_OFFICE IN (SELECT OFFICE FROM OFFICES WHERE REGION = ‘Western’))
The above query finds clients whose employees are assigned to offices in the Western region.
Correlated subquery: For certain subqueries same results are produced for every row.
SELECT CITY FROM OFFICES WHERE TOTAL_SALES < (SELECT AVG(SALES) FROM OFFICES)
The above query finds offices whose total sales are below the average sales. If this query is performed for each office, the result will be the same as average sales doesn’t change for any office. SQL detects this and changes the query to:
SELECT CITY FROM OFFICES WHERE TOTAL_SALES < 1000000.00
However, this can’t be done if the query has an outer reference.
SELECT CITY FROM OFFICES WHERE EXPECTED_SALES > (SELECT SUM(AVG_SALES) FROM EMPLOYEES WHERE EMPL_OFFICE = OFFICE)
The above query finds the offices whose expected sales exceed the sum of average sales of employees.
This query has an outer reference of OFFICE column and hence the query has to be performed for each row of OFFICES table. This type of subquery is known as correlated subquery.