SaratKumar Puranam

• Oct 4th, 2005

 

Correlated subquery is the subquery, where the subquery has been executed for the every row processed by the parent statement.

sravan kumar

• Dec 8th, 2006

 

1.IF THE OUTPUT OF AN OUTER QUERY ACTS AS A INPUT TO THE INNER QUERY THEN THAT QUERY IS KNOW AS CORELATED SUB QUERY.

2.WHEN A QUERY IS ASSOCIATED WITH ALLIAS NAME THEN THAT QUERY IS KNOWN AS CORELATED SUBQUERY

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

G.D.S.Prasad

 Profile Answers by G.D.S.Prasad Questions by G.D.S.Prasad

• Jun 21st, 2008

 

It is a subquery which is evaluated once for each row processed by parent statement.