where-clauseleft-joinabapopensql

Why does LEFT join omits a line from the left side, like INNER join


can you tell me why in the following select the LEFT join is working as INNER join?

    SELECT FROM @it_selected_data AS selected
          LEFT JOIN zkrh_scmt_invh AS invh
            ON invh~ebeln = selected~ebeln
        FIELDS selected~*, invh~invkind
        WHERE invh~invkind = ( SELECT MAX( invkind ) FROM zkrh_scmt_invh WHERE ebeln = invh~ebeln ) 
        INTO CORRESPONDING FIELDS OF TABLE @it_final_data.

Thanks in advance
Elias


Solution

  • Below is the copy of my answer to the same question in the SAP forum here.

    The question and answer are already given here, here and many other places, but let me explain again with my words:

    In ABAP SQL, it's not accepted to have a complex function in the ON clause (e.g. = ( SELECT ... ) in ABAP 7.58), the alternative is to keep it in the WHERE clause and indicate OR ... IS NULL:

    SELECT FROM @it_selected_data AS selected
          LEFT JOIN zkrh_scmt_invh AS invh
            ON invh~ebeln = selected~ebeln 
        FIELDS selected~*, invh~invkind
        WHERE ( invh~invkind IS NULL
             OR invh~invkind = ( SELECT MAX( invkind ) 
                                 FROM zkrh_scmt_invh 
                                 WHERE ebeln = invh~ebeln ) )
        INTO CORRESPONDING FIELDS OF TABLE @it_final_data.
    

    Another test, reproducible by anyone (DE exists in table T005T but not ZY):

    TYPES tt_land1 TYPE STANDARD TABLE OF t005-land1 WITH EMPTY KEY.
    TYPES: BEGIN OF ts_country_code_and_name,
             land1 TYPE t005t-land1,
             landx TYPE t005t-landx,
           END OF ts_country_code_and_name.
    TYPES tt_country_codes_and_names TYPE STANDARD TABLE OF ts_country_code_and_name WITH EMPTY KEY.
    
    DATA(countries) = VALUE tt_land1( ( 'DE' ) ( 'ZY' ) ).
    DATA(country_codes_and_names) = VALUE tt_country_codes_and_names( ).
    SELECT
        FROM @countries AS countries
        LEFT JOIN t005t
         ON t005t~land1 = countries~table_line
        AND t005t~spras = 'E'
        FIELDS countries~table_line AS land1, t005t~landx
        WHERE ( t005t~LAND1 IS NULL
             OR t005t~LAND1 IN ( SELECT land1 from t005 WHERE land1 LIKE 'D%' ) )
        INTO TABLE @country_codes_and_names.
    ASSERT country_codes_and_names = VALUE tt_country_codes_and_names(
        ( land1 = 'DE' landx = 'Germany' )
        ( land1 = 'ZY' landx = '' ) ).