topic Re: difference..inner join and outer join in Application Development and Automation Discussions

difference..inner join and outer join

Former Member — Thu, 20 Oct 2005 11:51:04 GMT

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.

Re: difference..inner join and outer join

Vinod_Chandran — Thu, 20 Oct 2005 11:58:32 GMT

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

Re: difference..inner join and outer join

former_member225448 — Thu, 20 Oct 2005 11:59:29 GMT

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

Re: difference..inner join and outer join

Former Member — Thu, 20 Oct 2005 12:00:45 GMT

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

Re: difference..inner join and outer join

Former Member — Thu, 20 Oct 2005 12:03:18 GMT

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.

Re: difference..inner join and outer join

Former Member — Thu, 20 Oct 2005 12:07:33 GMT

Hi For a comprehensible theoretical explanation check this:

http://en.wikipedia.org/wiki/Inner_join

Re: difference..inner join and outer join

Former Member — Thu, 20 Oct 2005 12:37:35 GMT

hai..thanx everyone

now i understood wht exactly mean it.

full marks for everyone.

Re: difference..inner join and outer join

Former Member — Thu, 20 Oct 2005 12:40:17 GMT

In that case can you close the post please?

Re: difference..inner join and outer join

Former Member — Thu, 20 Oct 2005 12:42:06 GMT

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.