Aalishan Moosavi

• Jun 30th, 2005

 

A correlated sub-query is a subquery that references the value/s from the main query. 
example :  
SELECT colA 
, colB  
FROM tableA 
WHERE colB < ( SELECT max(colX)  
FROM tableB 
WHERE tableB.colY = tableA.colA) 
Oracle executes correlated subquery for each record from of main query .. hence these types of queries have greater impact on performance and must be avoided

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.

sravan1

 Profile Answers by sravan1 Questions by sravan1

• Dec 13th, 2006

 

In correlated subqueries, the information from the outer select statement participates as a condition in the inner select statement.

Eg :  Select deptno, ename, sal

    from emp a 

where sal> (select avg(sal) from emp b where a.deptno =b.deptno)

order by deptno;