Saturday, October 31, 2009

Where and Why do we use "for all entries" in select statement


   Joins have the advantage of linking two or more tables based on few of the conditions.However, there are a few disadvantages with same.

1) Duplicates are removed from the resulting set.Hence proper care has to be taken for the unique fields and keys that are used to determine the result.

2)If the resulting table is going to be very large, performace can be well affected.


   It is in this scenario , we use "for all entries".The strategy would be to obtain the results for first table and based on the entries of first table, second table is filled and so on.But if the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.



Not Recommended




Loop at int_cntry.
Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.







Recommended



Select * from zfligh appending table int_fligh


For all entries in int_cntry


Where cntry = int_cntry-cntry

No comments:

Post a Comment