mainframegurukul.com

Posted: **Sat Sep 12, 2009 9:38 am**

Hi,

Would you please tell me how a sub-query looks like and a correlated sub-query? Do they always have fixed form of writing it?

Thanks.

Posted: **Sat Sep 12, 2009 4:02 pm**

Hi,

You can refer correlated subquery in
http://www.mainframegurukul.com/Mainfra ... php?t=4628

----------
Example
----------

Subquery -

Code: Select all

SELECT * FROM  TABLE  WHERE
FIELD1 = &#40; SELECT FIELDA FROM TABLEB &#41;

Correlated suquery -

Code: Select all

SELECT * FROM  TABLE  A  WHERE
FIELD1 = &#40; SELECT FIELDA FROM TABLEB  B  WHERE A.FIELD1 = B.FIELDB &#41;

Here we are using instance A from outer query in the innerquery, hence we are calling it as correlated subquery.

In normal subquery, we dont have any reference from outer query, being used in the inner query.

Posted: **Tue Sep 22, 2009 2:23 pm**

Hi, Thanks for the reply.

Does inner query execute first or outer query in sub-query / correlated sub-query?

Please explain.

Thanks.

Posted: **Tue Sep 22, 2009 3:08 pm**

subquery -
inner query executes first then outer query.

correlated subquery -
outer query starts executing first , inner query executes once for every record fetched by outer query.

mainframegurukul.com

a Sub-Query and a Correlated Sub-Query

a Sub-Query and a Correlated Sub-Query

sub query vs correlated sub query

inner query?