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

difference..inner join and outer join

Hai can anyone tell me the diff. between

inner join , outer join and left outer-join.

and when we use these in select queries.

if possible, with an example.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 20, 2005 at 12:03 PM

    Hi Hari,

    hte following link can help you.

    http://help.sap.com/saphelp_nw04/helpdata/en/cf/21ec77446011d189700000e8322d00/content.htm

    the following examples can be found by presing F1 in se38 by writing inner/outer join statement.

    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 c~carrname p~connid f~fldate 
           INTO CORRESPONDING FIELDS OF TABLE itab 
           FROM ( ( scarr AS c 
             INNER JOIN spfli AS p ON p~carrid   = c~carrid 
                                  AND p~cityfrom = p_cityfr 
                                  AND p~cityto   = p_cityto ) 
             INNER JOIN sflight AS f ON f~carrid = p~carrid 
                                    AND f~connid = p~connid ). 
    
    LOOP AT itab INTO wa. 
      WRITE: / wa-fldate, wa-carrname, wa-connid. 
    ENDLOOP. 
    
    
    
    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 s~carrid s~carrname p~connid 
           INTO CORRESPONDING FIELDS OF TABLE itab 
           FROM scarr AS s 
           LEFT OUTER JOIN spfli AS p ON s~carrid   =  p~carrid 
                                      AND p~cityfrom = p_cityfr. 
    
    LOOP AT itab INTO wa. 
      IF wa-connid = '0000'. 
        WRITE: / wa-carrid, wa-carrname. 
      ENDIF. 
    ENDLOOP. 
    

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 20, 2005 at 11:58 AM

    You can find a good example in SAP help.

    Just press F1 on FROM in the select statement.

    Also

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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 20, 2005 at 11:59 AM

    Hello Hari Krishna,

    TABEL1 inner join TABLE2:

    only entries found in TABLE1 and TABLE2 are the result.

    TABEL1 outher join TABLE2:

    all entries found in TABLE1 and, if found the corresponding data from TABLE2 are the result.

    If you find my answer useful, please don't forget the reward.

    Regards,

    Juergen

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 20, 2005 at 12:00 PM

    hi,

    table a table b

    empno name empno salary

    a sasi a 1000

    b xxx b 2000

    c yyy

    Inner join

    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

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 20, 2005 at 12:42 PM

    Hi hari,

    check this...

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

    example prog for inner join

    REPORT demo_select_inner_join.
    
    DATA: BEGIN OF wa,
            carrid TYPE spfli-carrid,
            connid TYPE spfli-connid,
            fldate TYPE sflight-fldate,
            bookid TYPE sbook-bookid,
          END OF wa,
          itab LIKE SORTED TABLE OF wa
                    WITH UNIQUE KEY carrid connid fldate bookid.
    
    SELECT  p~carrid p~connid f~fldate b~bookid
      INTO  CORRESPONDING FIELDS OF TABLE itab
      FROM  ( ( spfli AS p
                INNER JOIN sflight AS f ON p~carrid = f~carrid AND
                                           p~connid = f~connid    )
                INNER JOIN sbook   AS b ON b~carrid = f~carrid AND
                                           b~connid = f~connid AND
                                           b~fldate = f~fldate     )
      WHERE p~cityfrom = 'FRANKFURT' AND
            p~cityto   = 'NEW YORK'  AND
            f~seatsmax > f~seatsocc.
    
    LOOP AT itab INTO wa.
      AT NEW fldate.
        WRITE: / wa-carrid, wa-connid, wa-fldate.
      ENDAT.
      WRITE / wa-bookid.
    ENDLOOP.
    
    

    example prog for left outer join

    
    REPORT demo_select_left_outer_join .
    
    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 s~carrid s~carrname p~connid
      INTO CORRESPONDING FIELDS OF TABLE itab
      FROM scarr AS s
           LEFT OUTER JOIN spfli AS p ON s~carrid   =  p~carrid AND
                                         p~cityfrom = 'FRANKFURT'.
    
    LOOP AT itab INTO wa.
      WRITE: / wa-carrid, wa-carrname, wa-connid.
    ENDLOOP.
    

    http://www.sap-img.com/abap/several-nested-inner-join-statements-can-be-inefficient.htm

    http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_InnerJoinStatement.asp

    reward points for helpfull answers.

    regards,

    venu.

    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.