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

hi guru's what is the diff between for all entries & joins

hi guru's what is the diff between for all entries & joins

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 03:31 AM

    Hi,

    INNER JOIN - This is used to join two different DATABASE tables.

    FOR ALL ENTRIES - This is used to avoid the SELECT inside a loop. Usually if we want the child details for all the header records of a internal table then you loop at the header table and fire the SELECT. We can avoid the SELECT inside the looop using the FOR ALL ENTRIES clause

    Regards

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 31, 2008 at 03:39 AM

    hi Sri

    What is the use of 'FOR ALL ENTRIES'?

    Ans. To avoid nested select statements we use SELECT FOR ALL ENTRIES statement.

    If there r more than 10000 records SELECT FOR ALL ENTRIES is used.

    Performance wise SELECT FOR ALL ENTRIES is better to use.

    -


    1. duplicate rows are automatically removed

    2. if the itab used in the clause is empty , all the rows in the source table will be selected .

    3. performance degradation when using the clause on big tables.

    -


    My advice is NOT to use INNER JOIN and FOR ALL ENTRIES together. It kills the performance,especially when key fields are not passed to WHERE clause.

    First use FOR ALL ENTRIES on table AFKO with SELECT...ENDSELECT statement. And within this SELECT...ENDSELECT write SELECT query to hit AFPO.

    -


    check this link:

    http://blogs.ittoolbox.com/sap/db2/archives/for-all-entries-vs-db2-join-8912

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 03:38 AM

    Sri,

    For all entries

    The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

    The plus

    Large amount of data

    Mixing processing and reading of data

    Fast internal reprocessing of data

    Fast

    The Minus

    Difficult to program/understand

    Memory could be critical (use FREE or PACKAGE size)

    Some steps that might make FOR ALL ENTRIES more efficient:

    Removing duplicates from the driver table

    Sorting the driver table

    If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement: FOR ALL ENTRIES IN i_tab WHERE mykey >= i_tab-low and mykey <= i_tab-high.

    Select using JOINS

    The plus

    Very large amount of data

    Similar to Nested selects - when the accesses are planned by the programmer

    In some cases the fastest

    Not so memory critical

    The minus

    Mixing processing and reading of data not possible

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 31, 2008 at 03:42 AM

    Hi Vasu,

    Joins are used to fetch data fast from Database tables:

    Tables are joined with the proper key fields to fetch the data properly.

    If there are no proper key fields between tables don't use Joins;

    Important thing is that don't USE JOINS FOR CLUSTER tableslike BSEG and KONV.

    Only use for Transparenmt tables.

    You can also use joins for the database VIews to fetch the data.

    JOINS

    ... FROM tabref1 [INNER] JOIN tabref2 ON cond

    Effect

    The data is to be selected from transparent database tables and/or views determined by tabref1 and tabref2. tabref1 and tabref2 each have the same form as in variant 1 or are themselves Join expressions. The keyword INNER does not have to be specified. The database tables or views determined by tabref1 and tabref2 must be recognized by 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 the process of standardization (see relational databases). To regroup this information into a database query, you can link tables using the join command. This formulates conditions for the columns in the tables involved. The inner join contains all combinations of lines from the database table determined by tabref1 with lines from the table determined by tabref2, whose values together meet the logical condition (join condition) specified using ON>cond.

    Inner join between table 1 and table 2, where column D in both tables in the join condition is set the same:

    Table 1 Table 2










    A

    B

    C

    D

    D

    E

    F

    G

    H










    a1

    b1

    c1

    1

    1

    e1

    f1

    g1

    h1

    a2

    b2

    c2

    1

    3

    e2

    f2

    g2

    h2

    a3

    b3

    c3

    2

    4

    e3

    f3

    g3

    h3

    a4

    b4

    c4

    3






    |-- | | | --|

    \ /

    \ /

    \ /

    \ /

    \/

    Inner Join










    A

    B

    C

    D

    D

    E

    F

    G

    H










    a1

    b1

    c1

    1

    1

    e1

    f1

    g1

    h1

    a2

    b2

    c2

    1

    1

    e1

    f1

    g1

    h1

    a4

    b4

    c4

    3

    3

    e2

    f2

    g2

    h2

    |-- | | | | | | | | --|

    Example

    Output a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out:

    DATA: DATE LIKE SFLIGHT-FLDATE,

    CARRID LIKE SFLIGHT-CARRID,

    CONNID LIKE SFLIGHT-CONNID.

    SELECT FCARRID FCONNID F~FLDATE

    INTO (CARRID, CONNID, DATE)

    FROM SFLIGHT AS F INNER JOIN SPFLI AS P

    ON FCARRID = PCARRID AND

    FCONNID = PCONNID

    WHERE P~CITYFROM = 'FRANKFURT'

    AND P~CITYTO = 'NEW YORK'

    AND F~FLDATE BETWEEN '20010910' AND '20010920'

    AND FSEATSOCC < FSEATSMAX.

    WRITE: / DATE, CARRID, CONNID.

    ENDSELECT.

    If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or a table alias.

    Note

    In order to determine the result of a SELECT command where the FROM clause contains a join, the database system first creates a temporary table containing the lines that meet the ON condition. The WHERE condition is then applied to the temporary table. It does not matter in an inner join whether the condition is in the ON or WHEREclause. The following example returns the same solution as the previous one.

    Example

    Output of a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that are not sold out:

    DATA: DATE LIKE SFLIGHT-FLDATE,

    CARRID LIKE SFLIGHT-CARRID,

    CONNID LIKE SFLIGHT-CONNID.

    SELECT FCARRID FCONNID F~FLDATE

    INTO (CARRID, CONNID, DATE)

    FROM SFLIGHT AS F INNER JOIN SPFLI AS P

    ON FCARRID = PCARRID

    WHERE FCONNID = PCONNID

    AND P~CITYFROM = 'FRANKFURT'

    AND P~CITYTO = 'NEW YORK'

    AND F~FLDATE BETWEEN '20010910' AND '20010920'

    AND FSEATSOCC < FSEATSMAX.

    WRITE: / DATE, CARRID, CONNID.

    ENDSELECT.

    Note

    Since not all of the database systems supported by SAP use the standard syntax for ON conditions, the syntax has been restricted. It only allows those joins that produce the same results on all of the supported database systems:

    Only a table or view may appear to the right of the JOIN operator, not another join expression.

    Only AND is possible in the ON condition as a logical operator.

    Each comparison in the ON condition must contain a field from the right-hand table.

    If an outer join occurs in the FROM clause, all the ON conditions must contain at least one "real" JOIN condition (a condition that contains a field from tabref1 amd a field from tabref2.

    Note

    In some cases, '*' may be specified in the SELECT clause, and an internal table or work area is entered into the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the FROM clause, according to the structure of each table work area. There can then be gaps between table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, not simply by counting the total number of fields. For an example, see below:

    Variant 3

    ... FROM tabref1 LEFT [OUTER] JOIN tabref2 ON cond

    Effect

    Selects the data from the transparent database tables and/or views specified in tabref1 and tabref2. tabref1 und tabref2 both have either the same form as in variant 1 or are themselves join expressions. The keyword OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized by the ABAP-Dictionary.

    In order to determine the result of a SELECT command where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left-hand table (tabref1) are then added to this table, and their corresponding fields from the right-hand table are filled with ZERO values. The system then applies the WHERE condition to the table.

    Left outer join between table 1 and table 2 where column D in both tables set the join condition:

    Table 1 Table 2










    A

    B

    C

    D

    D

    E

    F

    G

    H










    a1

    b1

    c1

    1

    1

    e1

    f1

    g1

    h1

    a2

    b2

    c2

    1

    3

    e2

    f2

    g2

    h2

    a3

    b3

    c3

    2

    4

    e3

    f3

    g3

    h3

    a4

    b4

    c4

    3






    |-- | | | --|

    \ /

    \ /

    \ /

    \ /

    \/

    Left Outer Join










    A

    B

    C

    D

    D

    E

    F

    G

    H










    a1

    b1

    c1

    1

    1

    e1

    f1

    g1

    h1

    a2

    b2

    c2

    1

    1

    e1

    f1

    g1

    h1

    a3

    b3

    c3

    2

    NULL

    NULL

    NULL

    NULL

    NULL

    a4

    b4

    c4

    3

    3

    e2

    f2

    g2

    h2

    |-- | | | | | | | | --|

    Example

    Output a list of all custimers with their bookings for October 15th, 2001:

    DATA: CUSTOMER TYPE SCUSTOM,

    BOOKING TYPE SBOOK.

    SELECT SCUSTOMNAME SCUSTOMPOSTCODE SCUSTOM~CITY

    SBOOKFLDATE SBOOKCARRID SBOOKCONNID SBOOKBOOKID

    INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

    BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

    BOOKING-BOOKID)

    FROM SCUSTOM LEFT OUTER JOIN SBOOK

    ON SCUSTOMID = SBOOKCUSTOMID AND

    SBOOK~FLDATE = '20011015'

    ORDER BY SCUSTOMNAME SBOOKFLDATE.

    WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

    BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

    BOOKING-BOOKID.

    ENDSELECT.

    If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or using an alias.

    Note

    For the resulting set of a SELECT command with a left outer join in the FROM clause, it is generally of crucial importance whether a logical condition is in the ON or WHERE condition. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems:

    Only a table or view may come after the JOIN operator, not another join statement.

    The only logical operator allowed in the ON condition is AND.

    Each comparison in the ON condition must contain a field from the right-hand table.

    Comparisons in the WHERE condition must not contain a field from the right-hand table.

    The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs).

    Note

    In some cases, '*' may be specivied as the field list in the SELECT clause, and an internal table or work area is entered in the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the llen in der FROM clause, according to the structure of each table work area. There can be gaps between the table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, as in the following example (not simply by counting the total number of fields).

    Example

    Example of a JOIN with more than two tables: Select all flights from Frankfurt to New York between September 10th and 20th, 2001 where there are available places, and display the name of the airline.

    DATA: BEGIN OF WA,

    FLIGHT TYPE SFLIGHT,

    PFLI TYPE SPFLI,

    CARR TYPE SCARR,

    END OF WA.

    SELECT * INTO WA

    FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P

    ON FCARRID = PCARRID AND

    FCONNID = PCONNID )

    INNER JOIN SCARR AS C

    ON FCARRID = CCARRID

    WHERE P~CITYFROM = 'FRANKFURT'

    AND P~CITYTO = 'NEW YORK'

    AND F~FLDATE BETWEEN '20010910' AND '20010920'

    AND FSEATSOCC < FSEATSMAX.

    WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID,

    WA-FLIGHT-CONNID.

    ENDSELECT.

    And for all entries,

    this will help u.

    use of FOR ALL ENTRIES:

    1. INNER JOIN

    DBTAB1 <----


    > DBTAB2

    It is used to JOIN two DATABASE tables

    having some COMMON fields.

    2. Whereas

    For All Entries,

    DBTAB1 <----


    > ITAB1

    is not at all related to two DATABASE tables.

    It is related to INTERNAL table.

    3. If we want to fetch data

    from some DBTABLE1

    but we want to fetch

    for only some records

    which are contained in some internal table,

    then we use for alll entries.

    *----


    1. simple example of for all entries.

    2. NOTE THAT

    In for all entries,

    it is NOT necessary to use TWO DBTABLES.

    (as against JOIN)

    3. use this program (just copy paste)

    it will fetch data

    from T001

    FOR ONLY TWO COMPANIES (as mentioned in itab)

    4

    REPORT abc.

    DATA : BEGIN OF itab OCCURS 0,

    bukrs LIKE t001-bukrs,

    END OF itab.

    DATA : t001 LIKE TABLE OF t001 WITH HEADER LINE.

    *----


    itab-bukrs = '1000'.

    APPEND itab.

    itab-bukrs = '1100'.

    APPEND itab.

    *----


    SELECT * FROM t001

    INTO TABLE t001

    FOR ALL ENTRIES IN itab

    WHERE bukrs = itab-bukrs.

    *----


    LOOP AT t001.

    WRITE :/ t001-bukrs.

    ENDLOOP.

    cheers,

    Hema.

    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.