LEFT OUTER JOIN returns no rows

Question

I have two tables with the same key. I want to, in one SELECT, without subsequent manipulation of data in an internal table, retrieve all records in the left-hand table where there is NO CORRESPONDING record in the right-hand table (i.e. columns from the right-hand table would be empty).

The most logical thing to do would be the following, but this does not compile, because you may not use a field from the right-hand side in an outer join in the WHERE clause:

  select e~equnr into lt_equnr
    from equi as e
    left outer join eqbs as b on e~equnr = b~equnr
    where e~matnr = material
      and b~b_werk = space.

An alternative that looks promising and compiles is this, but it does not work, as it brings back even those that have corresponding entries in the right-hand table:

  select e~equnr into table lt_equnr
    from equi as e
    left outer join eqbs as b on e~equnr = b~equnr
    and b~b_werk = space
      where e~matnr = material.

This option merely blanks out fields from the right-hand side, but still includes everything in the result set. This can be confirmed by selecting fields from the right-hand side.

Another option, which also does not work, is using a sub-select:

  select e~equnr into table lt_equnr
    from equi as e
    where e~matnr = material
      and e~equnr not in ( select equnr from equi where equnr = e~equnr ).

Answer 1

You could try it with a subquery:

SELECT e~equnr INTO TABLE lt_equnr
  FROM equi AS e
  WHERE e~matnr = material
    AND NOT EXISTS ( SELECT b~equnr
                       FROM eqbs as b
                       WHERE b~equnr = e~equnr ).

Answer 2

As pointed out in the comments on the question, there was a bug in my code. In my sub-select I was using the LHS table. (My sub-select was referencing EQUI instead of EQBS).

By fixing my sub-select thus, it works:

  select e~equnr into table lt_equnr
    up to max_entries rows
    from equi as e
    where e~matnr = material
      and e~equnr not in ( select equnr from eqbs where equnr = e~equnr ).