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

inner joins and for all entries

Hi Experts,

could you please help me how many types of ineer joins and their purpose

and what is the purpose of for all entries

plz give some documentation on these areas

Thanks,

Divya sree

Add a comment
10|10000 characters needed characters exceeded

Related questions

6 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 13, 2006 at 11:37 AM

    Hi divya,

    what is the 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.

    regards,

    amit m.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 13, 2006 at 11:42 AM

    Will try to keep it simple.

    Joins:
    
      Table1 Values:
      Field1 Field2
      ABC    123
      BCD    234
      CDE    345
    
      Table2 Values:
      Field1 Field3
      ABC    lmn
      CDE    nop
    
      Inner Join: TABLE1 inner join TABLE2
         Returns values when the key record exists in both tables.
         Internal Table
          Field1 Field2 Field2
          ABC    123    lmn
          CDE    345    nop
    
     Here the record BCD is excluded as itz not existing in table2.
    
       Outer Join:
          Returns values irrespective of existence in second table.
          Internal Table
          Field1 Field2 Field3
          ABC    123    lmn
          BCD    234
          CDE    345    nop
    
       Here the record BCD is still extracted but leaves it as blank where the record is not found.
    
    For all Entries:
       Itz almost the same as inner join but here the first1 is internal table. Internal Table <b>inner join</b> DB Table.

    One more important thing to remember is while using the for all entries statement, we have to check that internal table is not initial otherwise it extracts all the details from the second database table.

    Hope the above info can give you some idea.

    Kind Regards

    Eswar

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 14, 2006 at 06:58 AM

    hi,

    <b>Inner join and outer join</b>

    table emp

    empno name

    a sasi

    b xxx

    c yyy

    table sal

    empno salary

    a 1000

    b 2000

    Inner join

    ****************

    select eempno ename

    s~sal

    into table int_table

    from emp as e

    inner join sal

    on

    eempno = sempno.

    if you made inner join between table a and b by emp no

    the selection retrives only if the condition satisfy the output will be

    a sasi 1000

    b xxx 2000

    Outer join

    *************************

    select eempno ename

    s~sal into table int_table

    from emp as e

    LEFT OUTER JOIN sal

    on

    eempno = sempno.

    if you made outer join (left /right ) the left table kept as it is the

    if the condition satisfy the right table entries will fetch else leave it blank

    the output will be

    a sasi a 1000

    b xxx b 2000

    c yyy

    *****************************************************

    <b>FOR ALL ENTRIES</b> is an effective way of doing away with using JOIN on two tables.

    You can check the below code -

    SELECT BUKRS BELNR GJAHR AUGDT

    FROM BSEG

    INTO TABLE I_BSEG

    WHERE BUKRS = ....

    SELECT BUKRS BELNR BLART BLDAT

    FROM BKPF

    INTO TABLE I_BKPF

    FOR ALL ENTRIES IN I_BSEG

    WHERE BUKRS = I_BSEG-BUKRS

    AND BELNR = I_BSEG-BELNR

    AND BLDAT IN SO_BLDAT.

    *******************************8

    look another example

    what is the 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.

    Hope this helps!

    Regards,

    anver

    pls mark points if userful

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      hi,

      <b>Inner Join and Outer Join ..</b>

      Check

      http://help.sap.com/saphelp_erp2005vp/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm

      <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 (in the current client).

      Example

      Displaying the occupancy of flights on 28.02.2001:

      TYPES: BEGIN OF ftab_type,

      carrid TYPE sflight-carrid,

      connid TYPE sflight-connid,

      END OF ftab_type.

      DATA: ftab TYPE STANDARD TABLE OF ftab_type WITH

      NON-UNIQUE DEFAULT KEY INITIAL SIZE 10,

      free TYPE I,

      wa_sflight TYPE sflight.

      • Suppose FTAB is filled as follows:

      • CARRID CONNID

      • --------------

      • LH 2415

      • SQ 0026

      • LH 0400

      SELECT * FROM sflight INTO wa_sflight

      FOR ALL ENTRIES IN ftab

      WHERE CARRID = ftab-carrid AND

      CONNID = ftab-connid AND

      fldate = '20010228'.

      free = wa_sflight-seatsocc - wa_sflight-seatsmax.

      WRITE: / wa_sflight-carrid, wa_sflight-connid, free.

      ENDSELECT.

      • The statement has the same effect as:

      SELECT DISTINCT * FROM sflight INTO wa_sflight

      WHERE ( carrid = 'LH' AND

      connid = '2415' AND

      fldate = '20010228' ) OR

      ( carrid = 'SQ' AND

      connid = '0026' AND

      fldate = '20010228' ) OR

      ( carrid = 'LH' AND

      connid = '0400' AND

      fldate = '20010228' ).

      free = wa_sflight-seatsocc - wa_sflight-seatsmax.

      WRITE: / wa_sflight-carrid, wa_sflight-connid, free.

      ENDSELECT.

      Notes

      You can only use ... FOR ALL ENTRIES IN itab WHERE cond in a SELECT statement.

      In the logical condition cond, the symbol itab-f is always a replacement symbol, and should not be confused with the component f of the header line of the internal table itab. The internal table itab does not need to have a header line.

      Each component of the internal table that occurs in a replacement symbol in the WHERE condition must have exactly the same type and length as the corresponding component in the database table.

      You cannot use replacement symbols in comparisons in LIKE, BETWEEN, or IN expressions.

      If you use FOR ALL ENTRIES IN itab, you cannot use ORDER BY f1 ... fn in the ORDER-BY clause.

      If you use FOR ALL ENTRIES IN itab, you cannot use a HAVING clause as well.

      Regards,

      Santosh

  • author's profile photo Former Member
    Former Member
    Posted on Oct 16, 2006 at 11:30 AM

    WE have only ONE INNER JOIN in ABAP and one OUTER JOIN.

    1) When you do inner join, it will select all the data from the <b>LEFT</b> table which statiscies the condition given with the <b>RIGHT</b> table. This is nothing but <b>LEFT INNER JOIN or SIMPLY INNER JOIN</b>

    2) Where as OUTER JOIN, will picks up all the values from both the tables which satisfies the condition.

    http://sap-img.com/abap/inner-joins.htm

    http://help.sap.com/saphelp_erp2005vp/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm

    3) FOR ALL ENTRIES are used with internal tables.

    If we want to select all the value for the internal table content then, we can use FOR ALL ENTRIES.

    EG: http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_ForAllEntries.asp

    ~thomas.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2006 at 02:28 PM

    remark to this :

    SELECT * FROM t001

    INTO TABLE t001

    FOR ALL ENTRIES IN itab

    WHERE bukrs = itab-bukrs.

    you should code "describe table itab line sy-tfill" before that statement coz if there is no entry in your itab it will select all the T001 ! for t001 it's ok but guess if you do this on COEP or such big tables...

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2006 at 07:11 AM

    Hi,

    Go thru this docu.

    SELECT - join

    Syntax

    ... [(] {dbtab_left [AS tabalias_left]} | join

    {[INNER] JOIN}|{LEFT [OUTER] JOIN}

    {dbtab_right [AS tabalias_right] ON join_cond} [)] ... .

    Effect

    The join syntax represents a recursively nestable join expression. A join expression consists of a left-hand and a right- hand side, which are joined either by means of [INNER] JOIN or LEFT [OUTER] JOIN . Depending on the type of join, a join expression can be either an inner ( INNER) or an outer (LEFT OUTER) join. Every join expression can be enclosed in round brackets. If a join expression is used, the SELECT command circumvents SAP buffering.

    On the left-hand side, either a single database table, a view dbtab_left, or a join expression join can be specified. On the right-hand side, a single database table or a view dbtab_right as well as join conditions join_cond can be specified after ON. In this way, a maximum of 24 join expressions that join 25 database tables or views with each other can be specified after FROM.

    AS can be used to specify an alternative table name tabalias for each of the specified database table names or for every view. A database table or a view can occur multiple times within a join expression and, in this case, have various alternative names.

    The syntax of the join conditions join_cond is the same as that of the sql_cond conditions after the addition WHERE, with the following differences:

    At least one comparison must be specified after ON.

    Individual comparisons may be joined using AND only.

    All comparisons must contain a column in the database table or the view dbtab_right on the right-hand side as an operand.

    The following language elements may not be used: BETWEEN, LIKE, IN.

    No sub-queries may be used.

    For outer joins, only equality comparisons (=, EQ) are possible.

    If an outer join occurs after FROM, the join condition of every join expression must contain at least one comparison between columns on the left-hand and the right-hand side.

    In outer joins, all comparisons that contain columns as operands in the database table or the view dbtab_right on the right-hand side must be specified in the corresponding join condition. In the WHERE condition of the same SELECT command, these columns are not allowed as operands.

    Resulting set for inner join

    The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

    Resulting set for outer join

    The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

    Example

    Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

    PARAMETERS: p_cityfr TYPE spfli-cityfrom,

    p_cityto TYPE spfli-cityto.

    DATA: BEGIN OF wa,

    fldate TYPE sflight-fldate,

    carrname TYPE scarr-carrname,

    connid TYPE spfli-connid,

    END OF wa.

    DATA itab LIKE SORTED TABLE OF wa

    WITH UNIQUE KEY fldate carrname connid.

    SELECT ccarrname pconnid f~fldate

    INTO CORRESPONDING FIELDS OF TABLE itab

    FROM ( ( scarr AS c

    INNER JOIN spfli AS p ON pcarrid = ccarrid

    AND p~cityfrom = p_cityfr

    AND p~cityto = p_cityto )

    INNER JOIN sflight AS f ON fcarrid = pcarrid

    AND fconnid = pconnid ).

    LOOP AT itab INTO wa.

    WRITE: / wa-fldate, wa-carrname, wa-connid.

    ENDLOOP.

    Example

    Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

    PARAMETERS p_cityfr TYPE spfli-cityfrom.

    DATA: BEGIN OF wa,

    carrid TYPE scarr-carrid,

    carrname TYPE scarr-carrname,

    connid TYPE spfli-connid,

    END OF wa,

    itab LIKE SORTED TABLE OF wa

    WITH NON-UNIQUE KEY carrid.

    SELECT scarrid scarrname p~connid

    INTO CORRESPONDING FIELDS OF TABLE itab

    FROM scarr AS s

    LEFT OUTER JOIN spfli AS p ON scarrid = pcarrid

    AND p~cityfrom = p_cityfr.

    LOOP AT itab INTO wa.

    IF wa-connid = '0000'.

    WRITE: / wa-carrid, wa-carrname.

    ENDIF.

    ENDLOOP.

    FOR ALL ENTRIES WHERE

    Syntax

    ... FOR ALL ENTRIES IN itab WHERE ... col operator itab-comp ...

    Effect

    If the addition FOR ALL ENTRIES is specified before the language element WHERE, then the components comp of the internal table itab can be used as operands when comparing with relational operators.

    The internal table itab must have a structured line type and the component comp must be compatible with the column col.

    The logical expression sql_cond of the WHERE condition can comprise various logical expressions by using AND and OR. However, if FOR ALL ENTRIES is specified, there must be at least one Comparison with a column of the internal table itab, which can be specified either statistically or dynamically (Release 6.40 and higher). In a statement with a SELECTstatement with FOR ALL ENTRIES, the addition ORDER BY can only be used with the addition PRIMARY KEY.

    The whole logical expression sql_cond is evaluated for each individual line of the internal table itab. The resulting set of the SELECT statement is the union of the resulting sets from the individual evaluations. Duplicate lines are automatically removed from the resulting set. If the internal table itab is empty, the whole WHERE statement is ignored and all lines in the database are put in the resulting set.

    Notes

    In Release 6.10 and higher, the same internal table can be specified after FOR ALL ENTRIES and after INTO.

    The addition FOR ALL ENTRIES is only possible before WHERE conditions of the SELECT statement.

    Example

    Exporting all flight data for a specified departure city. The relevant airlines and flight numbers are first put in an internal table entry_tab, which is evaluated in the WHERE condition of the subsquent SELECT statement.

    PARAMETERS p_city TYPE spfli-cityfrom.

    TYPES: BEGIN OF entry_tab_type,

    carrid TYPE spfli-carrid,

    connid TYPE spfli-connid,

    END OF entry_tab_type.

    DATA: entry_tab TYPE TABLE OF entry_tab_type,

    sflight_tab TYPE SORTED TABLE OF sflight

    WITH UNIQUE KEY carrid connid fldate.

    SELECT carrid connid

    FROM spfli

    INTO CORRESPONDING FIELDS OF TABLE entry_tab

    WHERE cityfrom = p_city.

    SELECT carrid connid fldate

    FROM sflight

    INTO CORRESPONDING FIELDS OF TABLE sflight_tab

    FOR ALL ENTRIES IN entry_tab

    WHERE carrid = entry_tab-carrid AND

    connid = entry_tab-connid.

    Thanks

    Sunil

    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.