Skip to Content
author's profile photo Former Member
Former Member

SELECT query

hello..

please tell me when shud i use FOR ALL ENTRIES n when shud i use INNER JOINS in select query.. whats the difference betwn them..

Thank u..

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

7 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 11:48 AM

    Hi,

    Inner Join: When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.

    Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.

    Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.

    Select aairln alnnam bfligh bcntry into table int_airdet

    From zairln as a inner join zflight as b on aairln = bairln.

    In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.

    For all entries: Outer join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

    Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

    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.

    If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

    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.

    <b>Reward Points if this helps,</b>

    Satish

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 20, 2007 at 11:48 AM

    Hi,

    Every time u should use for all entries, it is performance wise good dont use joins .

    EX.

    &----


    *& Form SUB_READ_VBRK

    &----


    • text

    ----


    FORM sub_read_vbrk.

    SELECT vbeln

    rplnr

    bukrs

    FROM vbrk

    INTO TABLE it_vbrk

    WHERE vbeln IN s_vbeln

    AND rplnr NE ' '.

    IF sy-subrc EQ 0.

    SORT it_vbrk BY rplnr.

    ENDIF.

    ENDFORM. " SUB_READ_VBRK

    &----


    *& Form SUB_READ_FPLTC

    &----


    • text

    ----


    FORM sub_read_fpltc.

    IF NOT it_vbrk[] IS INITIAL.

    SELECT fplnr

    fpltr

    ccnum

    FROM fpltc

    INTO TABLE it_fpltc

    FOR ALL ENTRIES IN it_vbrk

    WHERE fplnr EQ it_vbrk-rplnr

    AND ccins EQ 'GIFC'.

    IF sy-subrc EQ 0.

    SORT it_fpltc BY fplnr.

    ENDIF.

    ENDIF.

    ENDFORM. " SUB_READ_FPLTC

    Reward Points

    Reagrds,

    Prashant

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 11:51 AM

    Hi,

    if you use for all entries

    means you have an internal table with some records

    and you will hit another table and you fetch the records for those records in first internal table...

    if we have 10 in the first table and for these 10 records there may n number of records in the 2nd table and you will get these

    here you need two internal tables

    but if you use joins

    then it will give some records ( here if we have 10 records in first table and 20 in 2nd table then we will get 20 in our target table....)

    here you need only one internal table

    i hope you understood... with this

    regards,

    Venkatesh

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 12:54 PM

    hi,

    Using FOR ALL ENTRIES in your will give better performance.

    Dont go for JOINS with out knowing the key fields in the table.

    If you know the correct key fields and which conditions should be used as where condition in the select query, then you can go ahead with JOIN.

    Otherwise FOR ALL ENTRIES is the best way to use.

    Thanks

    Vijay

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 12:58 PM

    I do not agree to statement "FOR ALL ENTRIES ARE BETTER THAN JOINS",

    if you design the join properly it will fetch data faster,

    if u have more than 3 tables involved better go for for all entries

    if the tables are related through key fields , join also will perform well

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 01:36 PM

    Hi Friends,

    I was also confused about this part. Which one is recommended and which one is not. Recently i was breaking this select statment which has implemented "INNER JOIN" and I was trying to use "FOR ALL ENTRIES" to make the code Performance intensive.

    select BKPFBELNR BKPFBUKRS BKPFCPUDT VBRKBUKRS VBRKFKART VBRKKUNAG VBRKVBELN VBRKVBTYP VBRKWAERK VBRPARKTX VBRPFKIMG VBRPMATNR VBRPNETWR VBRPPOSNR VBRPPSTYV VBRPVBELN VBRPVRKME VBRPWERKS MARALABOR MARAMATNR MARAMTART MBEWBWKEY MBEWMATNR MBEWSTPRS T001KBUKRS T001KBWKEY T001BUKRS T001WAERS

    into (BKPF-BELNR , BKPF-BUKRS , BKPF-CPUDT , VBRK-BUKRS , VBRK-FKART , VBRK-KUNAG , VBRK-VBELN , VBRK-VBTYP , VBRK-WAERK

    , VBRP-ARKTX , VBRP-FKIMG , VBRP-MATNR , VBRP-NETWR , VBRP-POSNR , VBRP-PSTYV , VBRP-VBELN , VBRP-VRKME , VBRP-WERKS

    , MARA-LABOR , MARA-MATNR , MARA-MTART , MBEW-BWKEY , MBEW-MATNR , MBEW-STPRS , T001K-BUKRS , T001K-BWKEY , T001-BUKRS

    , T001-WAERS )

    from ( BKPF

    inner join VBRK

    on VBRKBUKRS = BKPFBUKRS

    and VBRKVBELN = BKPFBELNR

    inner join VBRP

    on VBRPVBELN = VBRKVBELN

    inner join MARA

    on MARAMATNR = VBRPMATNR

    inner join MBEW

    on MBEWMATNR = VBRPMATNR

    and MBEWBWKEY = VBRPWERKS

    inner join T001K

    on T001KBWKEY = MBEWBWKEY

    inner join T001

    on T001BUKRS = T001KBUKRS )

    where BKPF~BUKRS in BUKRS

    and BKPF~CPUDT in CPUDT

    and VBRK~VBTYP in VBTYP

    and VBRK~KUNAG in KUNAG

    and VBRP~WERKS in WERKS

    and VBRP~PSTYV in PSTYV

    and MARA~LABOR in LABOR

    and MARA~MTART in MTART.

    Endselect.

    Join is implemented on these many tables.

    1> BKPF

    2> VBRK

    3> VBRP

    4> MARA

    5> MBEW

    6> T001K

    7> T001

    When i compared the execution time of both the programmes then above query was taking much much less time to display the output as compared the code i had written.

    So I think we can say that when we are selecting Data from multiple tables then it is better to implement Joins rather than going in for "FOR ALL Entries".

    I would like to straight out this concept.

    Inputs are welcome.

    Regards,

    Mayank

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 03:34 PM

    Just check this:

    <a href="/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better">JOINS vs. FOR ALL ENTRIES - Which Performs Better?</a>

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.