What is correlated sub-query?




Answers were Sorted based on User's Feedback



Answer / bindhu

Correlated subquery are used for row-by-row prcessing.Each
subquery is executed once for every row of the outer
query.It is one way of reading every row in a table and
camparing the values in each row againt the realted data.

Eg: select ename,sal,deptno from emp outer where sal >
(select avg(sal) from emp where deptno=outer.deptno);

Each time a row from the outer query is processed,the inner
query is evaluated.

Is This Answer Correct ?    103 Yes 17 No

Answer / guest

select empno,ename from emp where deptno in(
select deptno from dept where dept.deptno=emp.deptno)

when inner subquery has an reference to outer query then
this is know as Correlated sub-query.

Is This Answer Correct ?    107 Yes 23 No

Answer / his highness abdullah!!

Thats right , a co-related subquery is evaluated once for
every row processed by the parent statement.......

Is This Answer Correct ?    32 Yes 8 No




Answer / asnani_satish@yahoo.com

Difference between sub-query, correlated query and query as
table
1. Sub-query : the inner query is executed in entirety
before the outer query is executed
eg select * from emp where deptno in (select deptno from dept);

2. Correlated Query: For each record fetched in outer query
corresponding matching records are fetched in sub-query
because of join condition within inner sub-query. Answers
1,2,3 are correct

3. Query as Table: A query can behave as a table

select a.empno,a.sal,a.sal/b.avgsal*100 as percent_avg_sal
from emp a,(select deptno,sum(sal) avgsal from emp group by
deptno) b
where a.deptno=b.deptno;

Here the entire "(select deptno,avg(sal).....)" behaves as
table named "b". The query is behaving as a temporary table.

Is This Answer Correct ?    15 Yes 3 No

Answer / bis

A query which uses values from the outer query is called as a correlated sub query. The subquery is executed once and uses the results for all the evaluations in the outer query.

Here, the sub query references the employee_id in outer query. The value of the employee_id changes by row of the outer query, so the database must rerun the subquery for each row comparison. The outer query knows nothing about the inner query except its results.

select employee_id, appraisal_id, appraisal_amount From employee
where
appraisal_amount < (select max(appraisal_amount)
from employee e
where employee_id = e. employee_id);

Is This Answer Correct ?    2 Yes 0 No

Answer / raj

Correlated sub query is evaluated once per row processed by the parent statement.

Example:which employee earn salary greater than avg salary of their department.

Query: select emp_no,emp_name,job,sal,dept_no from emp a where sal>(select avg(sal) from emp where dept_no=a.dept_no)

Is This Answer Correct ?    0 Yes 0 No

Answer / asnani_satish@yahoo.com

Minor correction in above answer
Difference between sub-query, correlated query and query as
table
1. Sub-query : the inner query is executed in entirety
before the outer query is executed
eg select * from emp where deptno in (select deptno from dept);

2. Correlated Query: For each record fetched in outer query
corresponding matching records are fetched in sub-query
because of join condition within inner sub-query. Answers
1,2,3 are correct

3. Query as Table: A query can behave as a table

select a.empno,a.sal,a.sal/b.avgsal*100 as percent_avg_sal
from emp a,(select deptno,avg(sal) avgsal from emp group by
deptno) b
where a.deptno=b.deptno;

Here the entire "(select deptno,avg(sal).....)" behaves as
table named "b" containing dept wise average sal. The query
is behaving as a temporary table.

Is This Answer Correct ?    6 Yes 9 No

Post New Answer





More SQL PLSQL Interview Questions

In a PL/SQL block,which loop type should be used in a performance point of view & Why (as both loops can do the same task) 1) open - Fetch loop 2) for loop

4 Answers   JDA,


What are user defined stored procedures ?

3 Answers   BirlaSoft,


ename empno deptno amar 1 10 akbar 2 20 anthonny 3 30 jonathan 4 40 write a procedure to dispaly the column values ina row separated by a deleimiter eg - input - select ename from emp '|' output - amar|akbar|anthony|jonathan input - select empno from emp '@' o/p - 1@2@3@4 input - select deptno from emp '/' o/p - 10/20/30/40 Pls answer this questn.

2 Answers  


what is self join and how it works??

2 Answers   Infosys,


How many rows will return from dual table?

4 Answers   Fujitsu,






i don't want the message as 14 rows updated just it should update what it will do

4 Answers  


Maximum how many triggers can we use in trigger and How to find out how many records it fetched.

1 Answers   TCS,


Hello All, Could any well write a query for the following scenario. Account(table name) No Name Amount 1 ABCD 2000.00 2 DEFG -2000.00 3 GHIJ 3000.50 4 JKLM 4000.00 5 MNOP 6000.00 O/p Should be in this format No Name Credit Debit 1 ABCD 2000.00 0 2 DEFG 0 -2000.00 3 GHIJ 3000.50 4 JKLM 0 -4000.00 5 MNOP 6000.00 o could any one give appropriate query for this Thnks in Advance Suneel Reddy

6 Answers   Target,


What is the difference between SQL table and the PLSQL table?

3 Answers   Mastek,


counting the no.of characters occurs in a string by using pl/sql function

1 Answers   TCS,


How will we see framework of a table?

2 Answers   Accenture,


What is the differnce between view and materialized view

4 Answers   TCS,






Categories