09-11-2007 10:55 AM
09-11-2007 10:57 AM
09-11-2007 10:59 AM
<b>inner join</b>
select a~pernr
a~bukrs
b~vorna
into table itab
from pa0001 as a join pa0002 as b
on apernr = bpernr
where pernr in s_pernr.
Regards
Vasu
09-11-2007 10:59 AM
09-11-2007 10:59 AM
Inner join
IF p_bsart IS INITIAL.
SELECT ekko~bukrs
ekko~lifnr
ekko~ebeln
ekko~waers
ekko~bsart
ekko~ekorg
ekko~ekgrp
ekpo~ebelp
ekpo~txz01
ekpo~matnr
ekpo~werks
ekpo~menge
ekpo~meins
ekpo~netpr
ekpo~netwr
INTO TABLE t_itab1 FROM
ekko INNER JOIN ekpo ON ekkoebeln = ekpoebeln
WHERE ekko~ebeln IN s_ebeln AND
ekko~bukrs IN s_bukrs AND
ekko~lifnr IN s_lifnr AND
ekko~ekorg IN s_ekorg AND
ekko~ekgrp IN s_ekgrp AND
ekpo~matnr IN s_matnr.
The difference between an INNER JOIN and an OUTER JOIN is the following. If a query on an INNER JOIN of VBAK (outer table) and VBAP (inner table) finds a record in VBAK but no matching records in VBAP, then no data is retrieved from the database because the inner table is empty. If you still want to keep VBAK rows for which there are no matching VBAP rows, you need to use the OUTER JOIN construct available in ABAP/4 Open SQL in 4.x..
Hi
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.
09-11-2007 11:00 AM
09-11-2007 11:02 AM
use select statement with for ALL ENTRIES OPTION.
In the majority of cases inner joins will actually be the requirement, but in the minority of cases you will want to use an outer join. Outer joins are useful where you may need all records from a certain table, that meet the selection criteria, and all data from another table(s), IF it exists. But if the latter data does not exist, you still want to get the first table data.
Using outer joins places a heavy load on the database engine (although no heavier that if you coded a nested select), so be very parsimonious about the use of outer joins.
The performance of the join depends on the database optimizer used especially if there are more than two tables used for joins.
Try to give maximum number of conditions in the ON clause. This is because the ON conditions are evaluated first and the virtual table created as a result is the one on which the WHERE clause applies.
Use subqueries if possible.
Make sure you follow this :
Consolidated selections
No row by row processing
No check statements
No selections within loops
Selections are 'into' internal tables - no appends
SQL trace check completed
All programs checked to make sure that they are using the full index and in the
Correct order.
Minimum or zero number of identical selects.
Use of appropriate Indexes
check this code for your problem .
A real life scenario that has been optimized.
Data : Begin of I_vbak occurs 0,
Vbeln like vbak-vbeln,
End of I_vbak.
Data : Begin I_vbap occurs 0,
Vbeln like vbap-vbeln,
Posnr like vbap-posnr,
Matnr like vbap-matnr,
Kwmeng like vbap-kwmeng,
End of I_vbap.
Data : Begin of I_makt occurs 0,
Matnr like makt-matnr,
Maktx like makt-maktx,
End of I_makt.
Data : d_lines like sy-subrc.
Start-of-selection.
Refresh : I_vbak , I_vbap , I_makt.
Select vbeln from VBAK into table I_vbak
Where erdat = p_erdat And
vkorg = p_vkorg And
vtweg = p_vtweg And
spart = p_spart.
Clear d_lines.
Describe table I_vbak lines d_lines.
Check d_lines <> 0.
Select vbeln posnr matnr kwmeng from VBAP into table I_vbap
For all entries in table I_vbak
Where vbeln = I_vbak-vbeln.
Clear d_lines.
Describe table I_vbap lines d_lines.
Check d_lines <> 0.
End-of-selection.
Sort I_vbap by vbeln matnr.
Select matnr maktx from makt into I_makt
For all entries in I_vbap
Where spras = sy-langu And
matnr = I_vbap-matnr.
Sort I_makt by matnr.
Loop at I_vbap.
Clear I_makt.
Read I_makt with key matnr = I_vbap-matnr binary search.
Write 😕 I_vbnap-vbeln,
I_vbap-psonr,
I_vbap-matnr,
I_Makt-matnr,
I_vbap-kwmeng.
Endloop.
Reward points if helpful .
Revert back for more help.
Regards
vijay
09-11-2007 11:01 AM
INNER JOIN results are an intersection of the tables being joined where in only if both the tables havethe data the result is pused onto the result set.
WHERE as in LEFT OUTR JOIN you can push the data of the LEF T table on the resultset even when the join condition is not met.
The use is that you wantto have all the data that is there in left table and also the right table if the join condition is a success then right table fileds will have data else they are initial.
OUTJOIN's are used in MAINTENANNCE VIEWS, HELP VIEWS .
INNER JOINS are used DATABSE VIEWS.
09-11-2007 11:02 AM
<u>http://help.sap.com/saphelp_nw2004s/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm</u>
09-11-2007 11:03 AM
09-11-2007 11:08 AM
Hi,
<b><u>Inner Join:-</u></b>
<i>SELECT pcarrid pconnid ffldate bbookid
INTO CORRESPONDING FIELDS OF TABLE itab
FROM ( ( spfli AS p
INNER JOIN sflight AS f ON pcarrid = fcarrid AND
pconnid = fconnid )
INNER JOIN sbook AS b ON bcarrid = fcarrid AND
bconnid = fconnid AND
bfldate = ffldate )
WHERE p~cityfrom = 'FRANKFURT' AND
p~cityto = 'NEW YORK' AND
fseatsmax > fseatsocc.</i>
<u><b>Left Outer Join</b></u>
<i>
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 = 'FRANKFURT'.</i>
Enjoy SAP.
Pankaj Singh.
09-11-2007 11:08 AM
Hi,
<u><b>Inner join:</b></u>
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 pcarrid pconnid ffldate bbookid
INTO CORRESPONDING FIELDS OF TABLE itab
FROM ( ( spfli AS p
INNER JOIN sflight AS f ON pcarrid = fcarrid AND
pconnid = fconnid )
INNER JOIN sbook AS b ON bcarrid = fcarrid AND
bconnid = fconnid AND
bfldate = ffldate )
WHERE p~cityfrom = 'FRANKFURT' AND
p~cityto = 'NEW YORK' AND
fseatsmax > fseatsocc.
LOOP AT itab INTO wa.
AT NEW fldate.
WRITE: / wa-carrid, wa-connid, wa-fldate.
ENDAT.
WRITE / wa-bookid.
ENDLOOP.
This example links the columns CARRID, CONNID, FLDATE, and BOOKID of the table SPFLI, SFLIGHT, and SBOOK, and creates a list of booking numbers for all flights from Frankfurt to New York that are not fully booked. An alias name is assigned to each table.
<u><b>Left outer join:</b></u>
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 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 = 'FRANKFURT'.
LOOP AT itab INTO wa.
WRITE: / wa-carrid, wa-carrname, wa-connid.
ENDLOOP.
<u><b>3 tables inner join:</b></u>
Select single VbrkBukrs VbrkKunrg Vbrk~Vbeln
VbrkFkdat VbrkBstnk_Vf Vbrk~Zterm
Tvzbt~Vtext
VbakVbeln VbakBstdk
LikpVbeln Likplfdat Likp~Lfuhr
into w_vbrk
from vbrk
inner join Tvzbt on TvzbtZterm = VbrkZterm and
Tvzbt~Spras = sy-langu
Inner join Vbfa as SalesLnk
on SalesLnk~vbeln = pu_vbeln and
SalesLnk~vbtyp_v = c_order
inner join Vbak on VbakVbeln = SalesLnkVbelv
Inner join Vbfa as DeliveryLnk
on DeliveryLnk~vbeln = pu_vbeln and
DeliveryLnk~vbtyp_v = c_Delivery
inner join Likp on LikpVbeln = DeliveryLnkVbelv
where vbrk~vbeln = pu_Vbeln.
Regards,
Bhaskar