Could anyone clearly explain the diference between sub
query and correlated sub query?
Answers were Sorted based on User's Feedback
Correlated subquery runs once for each row selected by the
outer query. It contains a reference to a value from the
row selected by the outer query.
Nested subquery runs only once for the entire nesting
(outer) query. It does not contain any reference to the
outer query row.
For example
Correlated Subquery:
select e1.empname e1.basicsal e1.deptno from emp e1 where
e1.basicsal (select max(basicsal) from emp e2 where
e2.deptno e1.deptno)
Nested Subquery:
select empname basicsal deptno from emp where (deptno
basicsal) in (select deptno max(basicsal) from emp group by
deptno)
Please let me know if I am wrong...............
Is This Answer Correct ? | 2 Yes | 0 No |
Correlated subquery runs once for each row selected by the
outer query. It contains a reference to a value from the
row selected by the outer query.
Nested subquery runs only once for the entire nesting
(outer) query. It does not contain any reference to the
outer query row.
For example
Correlated Subquery:
select e1.empname e1.basicsal e1.deptno from emp e1 where
e1.basicsal (select max(basicsal) from emp e2 where
e2.deptno e1.deptno)
Nested Subquery:
select empname basicsal deptno from emp where (deptno
basicsal) in (select deptno max(basicsal) from emp group by
deptno)
Please let me know if I am wrong...............
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / praveen
if we want to select 100th hig no.
in sub-query we will write 100 conditions.
to avoid this we go for correlated sub-query
Is This Answer Correct ? | 0 Yes | 0 No |
why db2 is called as universal database?what is the storage capacity of db2v9 n what are the advantages of db2
What is the use of commit in db2?
Can you define an Index if the table size less than 10 PAGES?
What is scrollable cursor in db2?
What is the difference between nvl and coalesce?
What are catalog tables in db2?
What is the difference between spufi and qmf?
What is runstats and reorg in db2?
Can DB2 be accessed by TSO users? If yes, which command is used to invoke DB2?
What is DCLGEN ?
Where could you look if you had a question about whether a column has been defined as an index?
Outputs of explain are with matchcols = 0. What does this signify?