topic ABAP SELECT - JOIN condition vs. WHERE condition in Application Development and Automation Discussions

ABAP SELECT - JOIN condition vs. WHERE condition

Tomas_Buryanek — Fri, 22 Feb 2019 14:53:04 GMT

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.

Re: ABAP SELECT - JOIN condition vs. WHERE condition

SimoneMilesi — Fri, 22 Feb 2019 15:00:18 GMT

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?

Re: ABAP SELECT - JOIN condition vs. WHERE condition

Sandra_Rossi — Fri, 22 Feb 2019 15:05:00 GMT

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

Re: ABAP SELECT - JOIN condition vs. WHERE condition

Tomas_Buryanek — Fri, 22 Feb 2019 15:12:20 GMT

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 🙂

Re: ABAP SELECT - JOIN condition vs. WHERE condition

matt — Fri, 22 Feb 2019 18:59:07 GMT

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.