IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

correleted subqueries


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
elayarajack

New User


Joined: 15 Feb 2004
Posts: 8

PostPosted: Fri Feb 20, 2004 12:25 pm
Reply with quote

hi,

can any one can give me a clear concept of correleted subqueries and sibqueries with suitable examples.
Back to top
View user's profile Send private message
mcmillan

Site Admin


Joined: 18 May 2003
Posts: 1210
Location: India

PostPosted: Fri Feb 20, 2004 2:38 pm
Reply with quote

I hope this info will guide you:

Quote:
Definition: A correlated subquery refers to at least one column of the outer query.

Any predicate that contains a correlated subquery is a stage 2 predicate.

Example: In the following query, the correlation name, X, illustrates the subquery's reference to the outer query block.

SELECT * FROM DSN8610.EMP X
WHERE JOB = 'DESIGNER'
AND EXISTS (SELECT 1
FROM DSN8610.PROJ
WHERE DEPTNO = X.WORKDEPT
AND MAJPROJ = 'MA2100');

What DB2 does: A correlated subquery is evaluated for each qualified row of the outer query that is referred to. In executing the example, DB2:

1. Reads a row from table EMP where JOB='DESIGNER'.

2. Searches for the value of WORKDEPT from that row, in a table stored in memory.

The in-memory table saves executions of the subquery. If the subquery has already been executed with the value of WORKDEPT, the result of
the subquery is in the table and DB2 does not execute it again for the current row. Instead, DB2 can skip to step 5.

3. Executes the subquery, if the value of WORKDEPT is not in memory. That requires searching the PROJ table to check whether there is any
project, where MAJPROJ is 'MA2100', for which the current WORKDEPT is responsible.

4. Stores the value of WORKDEPT and the result of the subquery in memory.

5. Returns the values of the current row of EMP to the application.


DB2 repeats this whole process for each qualified row of the EMP table.

Notes on the in-memory table: The in-memory table is applicable if the operator of the predicate that contains the subquery is one of the
following operators:

<, <=, >, >=, =, <>, EXISTS, NOT EXISTS

The table is not used, however, if:

 There are more than 16 correlated columns in the subquery

 The sum of the lengths of the correlated columns is more than 256 bytes

 There is a unique index on a subset of the correlated columns of a table from the outer query
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Which type is more efficient Nested S... DB2 2
No new posts What is EXIST, ANY, ALL in subqueries DB2 1
Search our Forums:

Back to Top