ABAP SELECT - JOIN condition vs. WHERE condition

[Tomas_Buryanek]

Hello,

this looks like basic ABAP question. I explored abapdocu. I see there are some clear differences (for example subqueries cannot be used in join_cond). But in other cases I am still a bit unsure what is preferred and why?

Is it just "semantic" what decides which cond do you use?

Here are examples of the SELECTs:
1) join_cond (ON)

SELECT mara~matnr INTO @l_material
  FROM mara
  INNER JOIN marc ON ( marc~matnr = mara~matnr AND
                       marc~werks = @p_werks ).

2) sql_cond (WHERE)

SELECT mara~matnr INTO @l_material
  FROM mara
  INNER JOIN marc ON ( marc~matnr = mara~matnr )
  WHERE marc~werks = @p_werks.

[SimoneMilesi]

Hi Tomas

I never used the first one if not with LEFT JOIN, but this for my own sake: I prefer to read the JOIN conditions in the ON... and the "filters" in WHERE.

About optimization, did you already try SAT and Trace to see if the behaviour changes?

[Sandra_Rossi]

With INNER JOIN, I guess there's no difference, but it's very different with OUTER JOIN. Is the question only for INNER JOIN ?

[Tomas_Buryanek]

sandra.rossi Only INNER, so it makes same result.

simone.milesi I understand this opinion. Interesting that there is example (I know it is just example, not recommendation or so) in abapdocu on JOIN ... ON where there is INNER JOIN example with join_cond used same like in my first example (parameter from selection screen)...
EDIT: did not tried SAT or trace. I will do...
EDIT2: ST05 trace is about the same. "Explain" for both selects is identical.

Thank you both 🙂

[matt]

It depends how the database works in the end. I did an inner join on HANA and because I had some conditions in the join instead of the where, I used up all the memory.

There are general discussions concerning the best use of ON and WHERE across the web. it's not a SAP/ABAP specific issue.