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:

It's because you are doing a filter in the WHERE clause on a column of the "left join" table.
If there's no line in the "left join" table, its columns will be considered as NULL value and the WHERE condition will fail.
General solution: move the condition to the ON clause.

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 = '' ) ).