10-20-2005 12:51 PM
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.
10-20-2005 1: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.
10-20-2005 12:58 PM
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
10-20-2005 12:59 PM
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
10-20-2005 1: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
10-20-2005 1: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.
10-20-2005 1:07 PM
10-20-2005 1:37 PM
hai..thanx everyone
now i understood wht exactly mean it.
full marks for everyone.
10-20-2005 1:40 PM
10-20-2005 1: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.