Difference between Nested & Correlated Subqueries

参考网址：http://sql-plsql.blogspot.com/2011/09/difference-nested-correlated-subquery.html

                  http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/WritingNestedSubqueries.htm

                  http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/WritingCorrelatedSubqueries.htm

需要使用代理方可打开。

一、嵌套子查询和关联子查询的区别：

1、A correlated subquery references one or more columns in the outer query. The subquery is known as a correlated subquery because the subquery is related to the outer query. A correlated subquery is used for a query depending on a value in each row contained  in the outer query.

2、Nest subqueries inside other subqueries can be 255-level depth.

1、关联子查询引用了外部查询的一列或者多列，一个子查询之所以被叫做关联子查询是因为该子查询和外部的查询是关联的，一个关联子查询依赖了在其外部查询中的一列的一个值。

2、嵌套子查询的层级最多可以有255级。

二、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]

1、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.

2、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.

3、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]

二、嵌套子查询和关联子查询的区别

      有两种主要的子查询：嵌套子查询和关联子查询：

      对于嵌套子查询：他首先会被执行，然后他的执行结果插入到主查询的where语句中。

      对于关联子查询：它与嵌套子查询恰恰相反，主查询首先被执行，然后每个子查询在主查询的每一行中进行执行。

 1、嵌套子查询

      当在where语句有一个子查询或者having子句中有另一个子查询，那么这时候的子查询为嵌套子查询。下面的子查询是一个嵌套子查询：

SELECT *
  FROM RESULT
 WHERE rollno IN
       (SELECT rollno
          FROM student
         WHERE courseid = (SELECT courseid FROM student WHERE rollno = 12));

    最里面的子查询将首先被执行，随后另一个子查询在刚刚执行的子查询的基础上在执行，随后，最外层的查询在刚刚执行的子查询的结果集的基础上再次执行。

2、关联子查询

关联子查询是在主查询执行完在进行执行的子查询，所以关联子查询执行的方式和其他一般的子查询执行的方式是相反的。下面是关联子查询执行的过程：

    1、外部查询查询到一行数据

    2、对于每一行在外部执行中查询到的数据，关联子查询都将执行一次。

    3、关联子查询查询的结果将决定所关联的外部查询是否满足条件作为最终结果集的一部分。

    4、对于外部查询的每一行数据重复上述的操作。

和正常的子查询有待区别的是，关联子查询需要引用的是之前的SQL语句。如下的SQL语句就是一个关联子查询：

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

换句话说，关联子查询决定于某些变量值，而这些变量值需要从其他查询中去接收，非关联子查询它是采取从底而上的验证方式，也就是最里层的首先进行验证，而关联子查询是从上而下的验证方式，最上层的查询分析的时候，他的结果集被下一个子查询初始化使用，这个子查询将会被重复的执行。

3、在以下情况下，关联子查询可以提高SQL语句的性能。

     1、当外部查询返回少量的行，尤其是在外部查询的where条件，他限制了少量的结果集可以返回，那么我们可以使用索引。

    2、内关联子查询通过索引扫描，如果内关联执行的表都非常的大，那么索引扫描是非常重要的，通过索引扫描可以获取表中很小比例的行。

    3、在其他情况下，使用视图和连接可能更加的高效，但是，总归来说，执行计划所采用的方式是最高效的。