For ALL ENTRIES IN

[Former Member]

How can i justify to use FOR ALL ENTRIES IN insteded of INNERJOIN ?

when i can go for FOR ALL ENTRIES?

[Former Member]

Hi,

In a join, the tables (base tables) are combined to form one results table. The join conditions are applied to this results table. The resulting composite for an inner join logic contains only those records for which matching records exist in each base table.

Join conditions are not limited to key fields.

If columns from two tables have the same name, then you have to ensure that the field labels are unique by prefixing the table name or a table alias.

A table join is generally the most efficient way to read from the database. The database is responsible for deciding which table is read first and which index is used (DB Optimizer).

At LEFT OUTER JOIN, results tables can also contain entries from the designated left hand table without the presence of corresponding data records (join conditions) from the table on the right. These table fields are filled by the database with null values and are then initialized according to ABAP type.

It makes sense to use a LEFT OUTER JOIN when data from the table on the left is needed for which there are no corresponding entries in the table on the right.

The following limitations apply for the Left Outer Join:

you can only have a table or a view to the right of the JOIN operator, you cannot have another join statement

Only AND can be used as a logical operator in an ON condition.

every comparison in the ON condition must contain a field from the table on the right.

if the FROM clause contains an Outer Join, then all ON conditions must contain at least one 'true' JOIN condition (a condition that contains a field from tab1 and a field from tab2).

-

---

FOR ALL ENTRIES works with a database in a quantity-oriented manner. Initially all data is collected in an internal table. Make sure that this table contains at least one entry (query sy-subrc or DESCRIBE), otherwise the subsequent transaction will be carried out without any restrictions).

SELECT...FOR ALL ENTRIES IN is treated like a SELECT statement with an external OR condition. The system only selects those table entries that meet the logical condition .

Using FOR ALL ENTRIES is recommended when data is not being read from the database, that is, it is already available in the program, for example, if the user has input the data. Otherwise a join is recommended.

The easiest technical option for reading from multiple (dependent) tables is to use nested SELECT statements. The biggest disadvantage of this method is that for every data record contained in the external loop a SELECT statement is run using the database. This leads to a considerably worse performance in client/server systems.

I think you got some clear idea on this .

Regards,

Gaurav

[Former Member]

Hi Gaurav

Given useful answer to me. can u explain it in programatically

what is happing in this program what is happing in workarea

internal table(ie., which record coming to internaltable & wa).

tables : ekko.

types : begin of ty_ekko,

ebeln type ekko-ebeln,

ebelp type ekko-ebelp,

end of ty_ekko,

begin of ty_ekpo,

ebeln type ekpo-ebeln,

end of ty_ekpo.

data : it_ekko type table of ty_ekko,

it_ekpo type table of ty_ekpo.

Parameters: so_ebeln for ekko-ebeln.

select ebeln ebelp from ekko

into table it_ekko

where ebeln in so_ebeln.

select ebeln from ekpo

into table it_ekpo

for all entries in it_ekko.

(and where ....)

thanks

kiran

[Former Member]

Hi,

if you have values in an internal table, and you want to fetch data from a database table based on internal table you will generally use FOR ALL ENTRIES.

Join joins two tables in a select statement which is time consuming, instead use 2 select statement. get the details into an internal table from the select on the first table. Then using this internal table in for all entries in the second select stmt. this method will be time efficient.

Reward if useful

rgds,

harikrishna.

[Former Member]

hi,

The effect of FOR ALL ENTRIES need to be observed first by running a test program and analyzng SQL trace. Certain options set by BASIS can cause FOR ALL ENTRIES to execute as an 'OR' condition.

This means if the table being used FOR ALL ENTRIES has 3 records, SQL Trace will show 3 SQL's getting executed. In such a case using FOR ALL ENTRIES is useless. However of the SQL Trace shows 1 SQL statement it's beneficial since in this case FOR ALL ENTRIES is actually getting executed as an IN List.

JOINS are recommended to be used till 5 joins. If the JOIN is being made on fields which are key fields in both the tables, it reduced program overhead and increases performance. So, if the JOIN is between two tables where the JOINING KEYS are key fields JOIN is recommended over FOR ALL ENTRIES.

-siva

[Former Member]

for all entries puts less load database when comapared to I Join.

If u need to getch fields from 2 tables, which are related with some common fields in both tables, we can for For all entries.

Performance wise , is is better.

1st It fetches data from 1st table.

if any entries r there, then it fetches data from 2nd table. so load on database will be reduced

Reward if useful

Narendra

[Former Member]

Hi kumar kiran,

Using "For all entries" is always better than inner join because the performance is very good.The program is much faster using "for all entries" than "inner joins".

Consider the example:

tables : ekko.

types : begin of ty_ekko,

ebeln type ekko-ebeln,

ebelp type ekko-ebelp,

end of ty_ekko,

begin of ty_ekpo,

ebeln type ekpo-ebeln,

end of ty_ekpo.

data : it_ekko type table of ty_ekko,

it_ekpo type table of ty_ekpo.

select-options: so_ebeln for ekko-ebeln.

select ebeln ebelp from ekko

into table it_ekko

where ebeln in so_ebeln.

select ebeln from ekpo

into table it_ekpo

for all entries in it_ekko.

(and where ....)

Hence, the second select query helps in selecting all the ebeln s present in first select query.

Reward if useful.

Regards,

Thasneem

[Former Member]

Hi,

If you join two or three tables, then you use inner joins.

But if the tables are more than three, then there is performance issue. For that use for all entries to get better performance.

regards,

kamala.