Difference between Nested & Correlated Subqueries

Difference between Nested & Correlated Subqueries

There are two main types of subqueries - nested and correlated. Subqueries are nested, when the subquery is executed first,and its results are inserted into Where clause of the main query. Correlated subqueries are the opposite case, where the main query is executed first and the subquery is executed for every row returned by the main query.[Via: Sql By Leon]

Nested Subqueries
A subquery is nested when you are having a subquery in the where or having clause of another subquery.

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 innermost subquery will be executed first and then based on its result the next subquery will be executed and based on that result the outer query will be executed. The levels to which you can do the nesting is implementation-dependent.

Correlated Subquery

A Correlated Subquery is one that is executed after the outer query is executed. So correlated subqueries take an approach opposite to that of normal subqueries. The correlated subquery execution is as follows:

-The outer query receives a row.
-For each candidate row of the outer query, the subquery (the correlated subquery) is executed once.
-The results of the correlated subquery are used to determine whether the candidate row should be part of the result set.
-The process is repeated for all rows.

Correlated Subqueries differ from the normal subqueries in that the nested SELECT statement referes back to the table in the first SELECT statement.

To find out the names of all the students who appeared in more than three papers of their opted course, the SQL will be

Select name
from student A
Where 3 < (select count (*)
           from result b
           where b.rollno = a.rollno);

In other words, a correlated subquery is one whose value depends upon some variable that receives its value in some outer query. A non-correlated subquery as said before is evaluted in a bottom-to-up manner, i.e. the inner most query is evaluated first. But a correlated subquery is resolved in a top-to-bottom fashion. The top most query is analyzed and based on that result the next query is initiated. Such a subquery has to be evaluated repeatedly, once for each value of the variable in question, instead of once and for all.

Correlated subqueries improve the SQL performance when:

Only a few rows are retrieved through the outer query and especially when outer query's WHERE clause, which limits the number of rows retrieved, can use an index.

The correlated inner queries are performed through an index scan. This is very important if the table or tables against which the inner query is performed is large and the index scan has to retrieve only a small percentage of its rows.

In other cases views and joins are likely to be more efficient. But the only sure way to say which method is efficient is to analyze the execution plan of the query and the resources used by it. [via:Introduction to Database Management Systems By Isrd Group]