Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

for all enteries

Former Member
0 Kudos

hi to all,

can u tell me the difference between for all entries and Inner join ? which is better ?

Thanks in advance

Vijaya

10 REPLIES 10

Former Member
0 Kudos

Inner join is used to get data(join) from 2 or more

tables.

where as for all entries is used on a table(also <1) and

an internal table. Here Internal table should not be

initial.

Both have their own advantages.

Former Member
0 Kudos

"Using several nested INNER JOIN statements can be inefficient and cause time out if the tables become too big in the future."

Joins here (in ABAP) are not those Native SQL Joins. If you are talking about the Core RDBMS, which mean Oracle or SQL Server, then Undoubtedly Joins are the best.

In ABAP, these joins are first split by the ABAP processor and then sent to the database, with the increase in DATA in production system, these joins tend to give way if your database keeps growing larger and larger.

You should rather used "FOR ALL ENTRIES IN" (Tabular conditions), which is a much effecient way as far as performance is concerned.

For example :

DATA: BEGIN OF LINE,

CARRID TYPE SPFLI-CARRID,

CONNID TYPE SPFLI-CONNID,

CITYFROM TYPE SPFLI-CITYFROM,

CITYTO TYPE SPFLI-CITYTO,

END OF LINE,

ITAB LIKE TABLE OF LINE.

LINE-CITYFROM = 'FRANKFURT'.

LINE-CITYTO = 'BERLIN'.

APPEND LINE TO ITAB.

LINE-CITYFROM = 'NEW YORK'.

LINE-CITYTO = 'SAN FRANCISCO'.

APPEND LINE TO ITAB.

SELECT CARRID CONNID CITYFROM CITYTO

INTO CORRESPONDING FIELDS OF LINE

FROM SPFLI

FOR ALL ENTRIES IN ITAB

WHERE CITYFROM = ITAB-CITYFROM AND CITYTO = ITAB-CITYTO.

WRITE: / LINE-CARRID, LINE-CONNID, LINE-CITYFROM, LINE-CITYTO.

ENDSELECT.

0 Kudos

Hi,

<b>... FROM tabref1 [INNER] JOIN tabref2 ON cond</b>

Effect

Selects data from the transparent database tables or views specified in tabref1 and tabref2. tabref1 and tabref2 either have the same form as in variant 1 or are themseleves joine expressions. The key word INNER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized in the ABAP-Dictionary.

In a relational data structure, it is quite normal for data that belongs together to be split up across several tables to help standardization (see relational database). To regroup this information in a database query, you can link tables using a join command. This formulates conditions for the columns of the tables involved. An inner join contains all combinations of lines from database table tabref1 with lines from database table tabref2 that meet the condition specified in the logical condition ON cond.

Inner join between table 1 and table 2 where column D sets the join condition:

<b>... FOR ALL ENTRIES IN itab WHERE cond</b>

Effect

Only selects the records that meet the logical condition cond when each replacement symbol itab-f is replaced with the value of component f of the internal table itab for at least one line of the table. SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result set. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records.

Got this from F1 help.

Refer F1 help for sample code.

Kindly reward points if u feel the answer helped u, if ur problem got solved reward and close the thread.

Former Member
0 Kudos

Inner join has DB support and is executed at the DB level. 'For all entries' is an SAP extension. This is converted into an SQL statement and then executed at the DB level. Thus the processing is a combination of application server and database.

If the inner join is based on keys of tables, then chances are that the performance of the join will be good. However for non-key fields, it is extremely expensive to do an inner join. Here 'for all entries' is better.

Former Member
0 Kudos

Hi Vijaya,

1. <b>INNER JOIN</b>- join two DATAbase tables based on a condition

In a relational database, you normally need to read data simultaneously from more than one

database table into an application program. You can read from more than one table in a single

SELECT statement, such that the data in the tables all has to meet the same conditions, using INNER JOIN

<b>SELECT ...

...

FROM <tab> [INNER] JOIN <dbtab> [AS <alias>] ON <cond> <options></b>

2.<b>FOR ALL ENTRIES</b>

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on

<b>internal tables</b>..

like

select....

INTO CORRESPONDING FIELDS OF LINE

<b>FROM SPFLI

FOR ALL ENTRIES IN ITAB</b>WHERE CITYFROM = ITAB-CITYFROM AND CITYTO = ITAB-CITYTO.

former_member188685
Active Contributor
0 Kudos

Hi,

you can use for all entries.

sample code for inner join

DATA: DATE   LIKE SFLIGHT-FLDATE, 
      CARRID LIKE SFLIGHT-CARRID, 
      CONNID LIKE SFLIGHT-CONNID. 

SELECT F~CARRID F~CONNID F~FLDATE 
    INTO (CARRID, CONNID, DATE) 
    FROM SFLIGHT AS F INNER JOIN SPFLI AS P 
           ON F~CARRID = P~CARRID AND 
              F~CONNID = P~CONNID 
    WHERE P~CITYFROM = 'FRANKFURT' 
      AND P~CITYTO   = 'NEW YORK' 
      AND F~FLDATE BETWEEN '20010910' AND '20010920' 
      AND F~SEATSOCC < F~SEATSMAX. 
  WRITE: / DATE, CARRID, CONNID. 
ENDSELECT.

sample code for for all entries..

select vbeln
       from vbak
       into table it_vbak
      where vbeln in s_vbeln.
if sy-subrc = 0.
 "before using for all entries either you should 
 "check sy-subrc or it_vbak table initial condition
select vbeln 
       posnr
       from vbap
       into table it_vbap
       for all entries in it_vbak
      where vbeln = it_vbak-vbeln.

endif.

endif.

you can use joins and for all entries combination also.

select vbak~vbeln
       vbap~posnr
       from vbak join vbap
       on vbak~vbeln = vbap~vbeln
       into table itab
       for all entries in it_vbeln
       where vbak~vbeln = it_vbeln-vbeln
             vbap~uepos = '0'.

regards

vijay

Former Member
0 Kudos

Hi vijaya

Inner JOin:

Inner join is used to join two tables to retrieve the matching data in both the table.

For all Entries.

For all entries is used to filter the database table which you want to retrive based on the internal table. i.e it fetch the data in the database table for which threre is a data in the internal table.

Both has different advantages. But usually fi you use multiple inner joins it will decrease the performance.

regards

kishore

Former Member
0 Kudos

Hi,

1. Joins are used when comparing 2 or more table.

2. But FOR ALL ENTRIES is used between a standard table

and internal table.

3. According to my knowledge using FOR ALL ENTRIES

improves performance when compared to using joins.

This is because based on some condition you are

already having the data in the internal table, so

that when you use FOR ALL ENTRIES unnecessary

retrieval to database can be avoided.

4. Before using FOR ALL ENTRIES, the internal table

should be checked for NOT INITIAL. That is the

internal table should contain entries.

Regs,

Venkat Ramanan

Former Member
0 Kudos

Hi,

For ALL Entries

- U can take advantage of 'For all entries' in the case when u want to reduce no. of trips to database.

- if u have some values in one internal table and based on that value u want some data from other table at particular time u can use for all entries.

- Instead of writing select statement in LOOP... ENDLOOP.

For all entries is good choice.

Inner Join

- When u have proper links between table available at that time u can use inner Join Statement.

According to ur requirement and flow of logic, U have to decide which one is suitable.

Regards,

Digesh Panchal

abdul_hakim
Active Contributor
0 Kudos

hi

FOR ALL ENTRIES will be based on the entries available in an itab.If the itab is empty then the select will fetch all the records from the corresponding table.so while using FOR ALL ENTRIES you need to check say for eg,

IF NOT ITAB IS INITIAL.

SELECT...FOR ENTRIES IN ITAB...

ENDIF.

Join will be based on the entries available in the specified table.If you ask which one is better then i can say by performancewise FOR ALL ENTRIES will be better.

Regards,

Abdul