Subqueries: Nested & Corelated

Nested Subquery: A subquery is a query with in a query. It is nested in the where or having clause of another query. Subquery is executed first and then its results are provided to the main clause of the main query. 

Example:
Get the result of all the students who are enrolled in the same course as the student with RollNo 12.

Select * 
From result
where rollno in (Select rollno
                           From student
                           Where courseid = (Select courseid
                                                          From student
                                                          Where rollno=12));

The inner most query will be run first and then based on its result next subquery will be executed and then based on its result main query will be run.

Corelated Subquery:
"Correlated" means it is linked back to the main query. For example:

SELECT * FROM dept d
WHERE  EXISTS
       ( SELECT 1 FROM emp
         WHERE  deptno = d.deptno );

This means: for each row in DEPT, go and check in EMP for a row with the same
deptno, and if you find one, report success. Logically you are asking for the
EXISTS check to be repeated for every single row in DEPT (although the optimizer

may turn it into a hash join or similar if it decides that will be more efficient).

The fact that the WHERE clause of the subquery refers back to the main query makes it a correlated subquery.



A corelated subquery is  executed after the outer query is run as opposed to nested one. Corelated subquery execution is as follows:
- the outer query retries a row 
- for each candidate row of outer query , the corelated subquery is executed once
- the results of the corelated query are used to determine whether the candidate row should be part of the result set
- the process is repeated for all the rows.

Corelated subqueries improve the SQL performance when only a few rows are retrieved through outer query.