Skip to Content

Question on select queries joins

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Mar 30, 2017 at 07:29 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 29, 2017 at 11:35 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • 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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 29, 2017 at 06:12 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 10, 2017 at 04:38 PM

    See this link: JOINS

    Add comment
    10|10000 characters needed characters exceeded