DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
Independent subquery
Code: |
UPDATE DB2.TABLE1 T1
SET COL1 = 1213
WHERE T1.COL2 IN
(SELECT T2.COL3
FROM DB2.TABLE2 T2
WHERE T2.COL4 < 4000
AND
AND
)
|
In an independent subquery, the innermost subquery is executed only once and the result table is unchanged for the duration of the SQL, and is completely independent of and values in TABLE1. PROs to this: the Subquery in ony executed once. CONs: the result table could be very large and must be searched for every row of TABLE1.
Correlated subquery
Code: |
UPDATE DB2.TABLE1 T1
SET COL1 = 1213
WHERE T1.COL2 IN
(SELECT T2.COL3
FROM DB2.TABLE2 T2
WHERE T2.INDX1 = T1.INDX1
AND T2.COL4 < T1.COL1
AND
)
|
In a Correlated subquery, the innermost subquery is executed for each row of TABLE1 because ?T2.INDX1 and T2.COL4?in TABLE2 correlates to ?T1.INDX1 and T1.COL1? of TABLE1. PROs to this: The subquery can be fast and produces only values needed to satisfy the ?IN?. CONs: The subquery must be executed for each row. Make sure your subquery in coded as narrowly as possible and use index keys. If the subquery does not use index keys, it will have to scan the entire TABLE2 for each row of TABLE1. |
|