SaratKumar Puranam

• Apr 10th, 2006

 

Correlated Subquery is the one, which is executed for each and every row processed by the parent statement.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 7th, 2006

 

when a sub query executes for each row its called a corelated sub query, in other words when a sub query takes the input from outer query for executation then execute and passes the result to outer query then outer query executes.

globalanil18

 Profile Answers by globalanil18 Questions by globalanil18

• Mar 31st, 2007

 

Hi Guys,

The Oracle Server performs a correlated subquery when the subquery references a column from a table reffered to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE or DELETE statement.

Nested subqueries Versus Correlated Subqueries

With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to be used by the main query. A correlated subquery, however, executes once for each condidate row considered by the outer query. In other words, the inner query is driven by the outer query.

Nested Subquery Execution

1.   The inner query executes first and finds a value
2.   The outer query executes once, using the value from the inner query.

Correlated Subquery Execution

1.   Get a candidate row (fetching by the outer query).
2.   Executes the inner query using the value of the candidate row.
3.   Use the values resulting from the inner query to qualify or disqualify the 
      candidate.
4.   Repeat until no candidate row remains.

        SELECT column1,column2,...
        FROM table1 [OUTER (Alias)]
        WHERE column1 operator
                        (SELECT column1,column2
                        from table2
                        where expr1=[OUTER].expr2)

A correlated subquery is one way of reading every row in a table and comparing values in each row against related data. It is used whenever a subquery must return a different result or set of results for each candidate row considerd by the main query. In other words, you use a correlated subquery to answer a multipart question whose answer depends on the value in each row processed by the parent statement.

The oracle server performs a correlated subquery references a column from a table in parent  query.

Note : You can use the ANY and ALL operators in a correlated subquery.

I hope ,you will get your answer. If you have any dought then feel free to mail me on

globalanil18@yahoo.co.in

Anil Kumar

Software Developer
Research & Development Section
Tata Steel Jamshedpur
globalanil18@yahoo.co.in