Skip to Content
0

Question on select queries joins

Mar 29, 2017 at 11:17 AM

226

avatar image

Hello Experts,

I have a question on select queries using joins.

Are the inner joins same as left inner join and right inner join if yes the below select query is giving same results even if I use left inner join, or right inner joion or left outer join or right outer join or simply join or any other join this query is same results.

SELECT a~vbeln , a~erdat , a~erzet , a~ernam ,
       b~posnr , b~matnr , b~matwa
     INTO TABLE @data(it_final)
     from vbak as a left join vbap as b
       on a~vbeln = b~vbeln
     WHERE a~vbeln in @s_vbeln.

As per theory if we use left outer join if VBAK table has one sales order vbeln with no say12345 and VBAP table has 5 records ( 5items ) in vbap table for the same sales order it should show only one as the left table is VBAK where as if the left table is VBAP it should show all the entries.

I had googled and searched a lot in SCN and all the results are same.

Kindly suggest an example illustrating differences.

Thanks,

rg

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
Jelena Perfiljeva
Mar 30, 2017 at 07:29 PM
0

I thought this was already explained...

SELECT mara~matnr mbew~bwkey FROM MARA LEFT JOIN MARC ON mara~matnr = mbew~matnr and ... WHERE ...

This will select all the records in MARA table that match WHERE criteria. If there is also a record in MBEW table that matches criteria in ON and WHERE then mbew~bwkey field will be filled in for the respective materials. If there is no record that meets this criteria then mbew~bwkey will be blank for those materials.

If there is more than one record in MBEW that fits the criteria in ON and WHERE then you'll see a record for each MATNR / BWKEY combination as a result.

As I said, LEFT JOIN is used when you are looking for additional information and are not sure if it exists. But you still want to get the qualified records of the FROM table regardless. Not sure how this could be made more clear... It's not even ABAP concept, it's just SQL basics.

Share
10 |10000 characters needed characters left characters exceeded
Horst Keller
Mar 29, 2017 at 11:35 AM
1

See

https://help.sap.com/http.svc/rc/abapdocu_751_index_htm/7.51/en-US/index.htm?file=abapselect_join.htm and the examples there.

Results set for inner joins

In a single results set, an inner join joins the columns of the rows in the results set of the left side with the columns of the rows in the results set of the right side. This results set contains all combinations of rows whose columns meet the condition join_cond. If there are no rows in the results set of the left and right sides that meet join_cond, a row is not created in the resulting results set.


Results set for outer joins

The outer join creates the same results set as the inner join. The difference is that, for each selected row on the left side as LEFT OUTER JOIN or on the right side as RIGHT OUTER JOIN, at least one row is created in the results set, even if no rows on the other side meet the condition join_cond. The columns on the other side that do not meet the condition join_cond are filled with null values.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Horst Keller,

Thanks for your reply. I already went through the link provided . just want to understand if the left outer join , right outer join and inner join works the same way when the ON condition is used for key fields and for non key fields..

Please see the below two queries which run , query1 outputs the same results irrsepective of whether we are using inner join ,left outer join or right outer join. and Query1 uses all key fields , query2 uses non key fields and when we use inner join we are having output as the common records for both the tables and when using left outer join it results all the entries of the left table and common entries for both the tables and similarly right table entries and common entries when using right outer join .

Please suggest if the outer joins would not work in OPEN SQL when using key fields in ON condition of query

Query1

SELECT a~vbeln , a~erdat , a~erzet , a~ernam ,
b~posnr , b~matnr , b~matwa

INTO TABLE @data(it_final)
from vbak as a left join vbap as b
on a~vbeln = b~vbeln
WHERE a~vbeln in @s_vbeln.

Query2

SELECT A~EBELN , A~LIFNR , A~KNUMV,
B~KUNNR, B~LAND1 , B~NAME1
* B~EBELP , B~NETWR ,B~NETPR , B~WERKS , B~MATNR ,
* L~NAME1 , L~NAME2
FROM EKKO AS A
RIGHT OUTER JOIN kna1 AS B ON ( A~KUNNR = B~KUNNR )
* INNER JOIN LFA1 AS L ON L~LIFNR = A~LIFNR
* INNER JOIN EKKN AS C ON C~EBELN = A~EBELN
INTO TABLE @data(itab)
* WHERE B~BUKRS = 'C365'
where b~KUNNR in @s_KUNNR.
and a~loekz in @s_loekz .

Thanks,

rg

0

The joins work as documented and the results depend on the underlying data model, respective the cardinality of the selected data. If you have a 1:1 cardinality for the selected data, inner an right outer joins do the same. For 1:m, m:1 or other n:m cardinalities the results can differ. It is not the question if you use key or other fields, but what are the result sets produced by the conditions.

You really should produce some sample data (e.g. in the demo tables delivered with the documentation, don't fear, you can fill them with data as you like) and play around a bit.

0
Ramya g Mar 30, 2017 at 09:50 AM
-1

Hi,

Thank you very much for your replies, I got a understanding now.

For the below query as per my understanding it brings all the records satisfying where condition and the material number that exists in MARA as we are using left outer join with MARA and MARM and the rows common to both the tables with MARC .

Could you please explain what does it mean by the portion in bold and underlined below mean for the query as we see the ON condition as they are using MBEW and MARC in ON condition

LEFT OUTER JOIN mbew AS mb ON mb~matnr = mc~matnr
AND mb~bwkey = mc~werks
LEFT OUTER JOIN makt AS mt ON mt~matnr = ma~matnr

SELECT ma~matnr mc~werks ma~brgew ma~bstme ma~ean11 ma~ekwsl
ma~ernam ma~ersda ma~extwg ma~ferth
ma~groes ma~labor ma~laeda ma~matkl ma~meins
ma~mtart ma~mtpos_mara ma~normt ma~ntgew
mv~prodh ma~spart ma~stoff ma~vabme ma~volum
ma~zz_launchdat ma~zz_prdlinetype ma~zz_project
mm~breit mm~hoehe mm~laeng mm~meinh mm~umren mm~umrez
mc~altsl mc~atpkz mc~ausme
mc~basmg mc~beskz mc~bstfe mc~bstma mc~bstmi mc~bstrf mc~bwtty
mc~casnr mc~disgr mc~disls mc~dismm
mc~dispo mc~dzeit mc~eisbe mc~eislo mc~ekgrp mc~fevor mc~fhori
mc~herkl mc~kautb mc~ladgr mc~lgfsb
mc~lgpro mc~loggr mc~losgr mc~maabc mc~mabst mc~matgr mc~mfrgr
mc~minbe mc~mmsta mc~mmstd
mc~mtvfp mc~ncost mc~plifz mc~prctr mc~rgekz mc~rwpro mc~sbdkz
mc~schgt mc~sernp mc~sfcpf mc~shflg
mc~shzet mc~sobsk mc~sobsl mc~stawn mc~strgr mc~ueeto mc~uneto
mc~vspvb mc~webaz mc~wzeit
mc~zz_dmg_ind mv~aumng mv~dwerk mv~lfmng mv~mtpos mv~mvgr1
mv~mvgr2 mv~mvgr3 mv~mvgr4
mv~mvgr5 mv~prat2 mv~prat5 mv~prat6 mv~prat8 mv~prodh mv~provg
mv~scmng mv~sktof mv~versg mv~vkorg
mv~vmsta mv~vmstd mv~zz_sd_brand mv~zz_sd_prodmgr
mv~zz_sd_trdprcind mb~bklas mb~bwkey mb~ekalr mb~hkmat
mb~hrkft mb~kosgr mb~lbkum mb~lplpr mb~pdatl mb~pdatv mb~pdatz
mb~peinh mb~pprdl mb~pprdv
mb~pprdz mb~salk3 mb~stprs mb~vplpr mb~vprsv mb~zpld1 mb~zpld2
mb~zpld3 mb~zplp1 mb~zplp2 mb~zplp3
mb~zplpr mt~maktx mt~spras md~diskz md~lbstf md~lgort md~lminb
md~lsobs mc~abcin
INTO TABLE i_mara_marc
FROM mara AS ma
LEFT OUTER JOIN marm AS mm ON mm~matnr = ma~matnr
INNER JOIN marc AS mc ON mc~matnr = ma~matnr
LEFT OUTER JOIN mvke AS mv ON mv~matnr = mc~matnr
LEFT OUTER JOIN mbew AS mb ON mb~matnr = mc~matnr
AND mb~bwkey = mc~werks
LEFT OUTER JOIN makt AS mt ON mt~matnr = ma~matnr
LEFT OUTER JOIN mard AS md ON md~matnr = mc~matnr
AND md~werks = mc~werks

wHERE ma~matnr IN s_matnr

AND mc~werks IN s_werks
AND mc~stawn IN s_stawn
AND ma~bstme IN s_bstme
AND ma~ernam IN s_ernam
AND ma~ersda IN s_ersda
AND ma~labor IN s_labor
AND ma~laeda IN s_laeda
AND ma~matkl IN s_matkl
AND ma~meins IN s_meins
AND ma~mtart IN s_mtart
AND ma~spart IN s_spart
AND ma~vabme IN s_vabme
AND ma~zz_prdlinetype IN s_zzpdln
AND ma~zz_project IN s_zzproj
AND mc~ausme IN s_ausme
AND mc~beskz IN s_beskz
AND mc~bwtty IN s_bwtty
AND mc~disgr IN s_disgr
AND mc~dispo IN s_dispo
AND mc~fevor IN s_fevor
AND mc~loggr IN s_loggr
AND mc~losgr IN s_losgr
AND mc~maabc IN s_maabc
AND mc~matgr IN s_matgr
AND mc~mmsta IN s_mmsta
AND mc~ncost IN s_ncost
AND mc~prctr IN s_prctr
AND mc~rgekz IN s_rgekz
AND mc~schgt IN s_schgt
AND mc~sobsk IN s_sobsk
AND mc~sobsl IN s_sobsl.

Thanks,

rg

Share
10 |10000 characters needed characters left characters exceeded
Jelena Perfiljeva
Mar 29, 2017 at 06:12 PM
0

In layman's terms, we use INNER JOIN when we want to see only the records where all the criteria from all the tables involved is met. For example, if you want to see sales order lines (VBAP) only for the materials with specific master fields (MARA) and don't need any other orders then you'd use INNER JOIN.

We use OUTER JOIN when we would like to get the results from one side regardless of whether there is a match on the other side. For example, I need a list of customers (KNA1) but I know that only some of them have an email address (ADR6). But regardless of whether a customer has an email, I still would like to show that customer on the list (Email column would simply be blank in this case). So I'd use an OUTER JOIN (formerly known as LEFT JOIN).

As Horst correctly mentioned, your other questions are secondary to that and don't have much to do with the nature of JOIN. You might want to check some general SQL information websites where these and other concepts might be explained in simpler terms.

P.S. There are different schools of thought regarding the use of aliases (AS ...). I prefer not to use them at all (vbak~... works fine) but if you feel otherwise at least kindly use something more descriptive than "a" and "b". Think about those who will be maintaining the code.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 10, 2017 at 04:38 PM
0

See this link: JOINS

Share
10 |10000 characters needed characters left characters exceeded