topic Re: Joins in Application Development and Automation Discussions

Joins

Former Member — Thu, 12 Jul 2007 10:15:06 GMT

Out of two joins Inner and outer,which join is not recommended by SAP and why is not recommended ?

Thanks in advance.

Cheers,

Sriram.

Re: Joins

Former Member — Thu, 12 Jul 2007 10:17:52 GMT

well you didnt grasp the concept of a join yet

there is no better alternative, and there is no alternative recommended as the better one.

WHY? Well an inner join gives you a completeley different result than a outer join.

So it´s the requirement that recommends you which one to use.

Re: Joins

Former Member — Thu, 12 Jul 2007 10:18:33 GMT

Hi,

it depends on use,

See it is like this,

Inner join: common data between A and B

Left Outer: Common data + A' s data

Right Outer: Common data + B's Data

Reward if useful!

Re: Joins

Former Member — Thu, 12 Jul 2007 10:18:49 GMT

Syntax

... [(] {dbtab_left [AS tabalias_left]} | join

{[INNER] JOIN}|{LEFT [OUTER] JOIN}

{dbtab_right [AS tabalias_right] ON join_cond} [)] ... .

Effect

The join syntax represents a recursively nestable join expression. A join expression consists of a left-hand and a right- hand side, which are joined either by means of [INNER] JOIN or LEFT [OUTER] JOIN . Depending on the type of join, a join expression can be either an inner ( INNER) or an outer (LEFT OUTER) join. Every join expression can be enclosed in round brackets. If a join expression is used, the SELECT command circumvents SAP buffering.

On the left-hand side, either a single database table, a view dbtab_left, or a join expression join can be specified. On the right-hand side, a single database table or a view dbtab_right as well as join conditions join_cond can be specified after ON. In this way, a maximum of 24 join expressions that join 25 database tables or views with each other can be specified after FROM.

AS can be used to specify an alternative table name tabalias for each of the specified database table names or for every view. A database table or a view can occur multiple times within a join expression and, in this case, have various alternative names.

The syntax of the join conditions join_cond is the same as that of the sql_cond conditions after the addition WHERE, with the following differences:

At least one comparison must be specified after ON.

Individual comparisons may be joined using AND only.

All comparisons must contain a column in the database table or the view dbtab_right on the right-hand side as an operand.

The following language elements may not be used: BETWEEN, LIKE, IN.

No sub-queries may be used.

For outer joins, only equality comparisons (=, EQ) are possible.

If an outer join occurs after FROM, the join condition of every join expression must contain at least one comparison between columns on the left-hand and the right-hand side.

In outer joins, all comparisons that contain columns as operands in the database table or the view dbtab_right on the right-hand side must be specified in the corresponding join condition. In the WHERE condition of the same SELECT command, these columns are not allowed as operands.

Resulting set for inner join

The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

Resulting set for outer join

The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

Example

Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

SELECT c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

Example

Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

PARAMETERS p_cityfr TYPE spfli-cityfrom.

DATA: BEGIN OF wa,

carrid TYPE scarr-carrid,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH NON-UNIQUE KEY carrid.

SELECT s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid

AND p~cityfrom = p_cityfr.

LOOP AT itab INTO wa.

IF wa-connid = '0000'.

WRITE: / wa-carrid, wa-carrname.

ENDIF.

ENDLOOP.

Both type of joins we use But mostly we use INNER joins

Outer joins are rare used.

<b>Reward points for useful Answers</b>

Regards

Anji

Re: Joins

Former Member — Thu, 12 Jul 2007 10:20:13 GMT

inner joins and outer joins are used in diff scenarios....

inner join work only when corresponding fields have the same values...but this is not necessary for outer join

Re: Joins

Former Member — Thu, 12 Jul 2007 11:18:08 GMT

Hi Kemmer,

Thanks for ur reply, I mean with respect to performance criteria which join is best ?

Re: Joins

Former Member — Thu, 12 Jul 2007 11:21:34 GMT

we will use inner join in different scenario and outer join in some other scenario

as performance point try to use for all entries instead of joins

Re: Joins

Former Member — Thu, 12 Jul 2007 12:13:55 GMT

in sap it supports inner join and left outer join.

Re: Joins

Former Member — Tue, 17 Jul 2007 07:10:53 GMT

NOOOOOOOOO

the "FOR ALL ENTRIES" statement is wideley known to decrease performance, any pother way is better.

Re: Joins

Former Member — Tue, 17 Jul 2007 07:26:46 GMT

"Vox Vocis Sonat - Vox Exampli Tonat"

Quote From the Latin - English translation is Actions speak louder than words.

Here's an example

TYPES: BEGIN OF ty_selection,

vkorg TYPE vapma-vkorg,

vkbur TYPE vapma-vkbur,

vbeln TYPE vapma-vbeln,

posnr TYPE vapma-posnr,

matnr TYPE vapma-matnr,

matkl TYPE mara-matkl,

bu TYPE zpca_hg_bu_prctr-bu,

prctr TYPE vbap-prctr,

prctr1 TYPE vbap-prctr,

END OF ty_selection.

DATA: ta_selection TYPE table of ty_selection.

SELECT a_{vkorg a}vkbur b_{vbeln b}posnr a_{matnr b}absta b~lfsta

INTO CORRESPONDING FIELDS OF TABLE ta_selection

FROM ( vapma AS a

INNER JOIN vbup AS b

ON a_{vbeln EQ b}vbeln

AND a_{posnr EQ b}posnr

INNER JOIN vbuk AS c

ON b_{vbeln EQ c}vbeln )

WHERE a~matnr IN so_matnr

AND a~vkorg IN so_vkorg

AND a~vkbur IN so_vkbur

AND a~auart EQ 'TA'

AND ( b~absta NE 'C'

AND ( b~lfsta NE 'C'

AND b~fksaa NE 'C' )

AND ( c~abstk NE 'C'

AND c~gbstk NE 'C' )

) .

I want all the line items from standard sales orders where the relevant statuses in both the header and line items aren't EQ 'C' , Sales order contains specified Materials, from a specified range of Sales Org and Sales offices.

(BTW use VAPMA rather than VBAK/VBAP combo as primary table for this type of info. It's a LOT LOT faster).

Re: Joins

Former Member — Tue, 17 Jul 2007 08:37:45 GMT

hi sriram

Both hav been used by ABAPpers as per the requirement is concerned,,Both are served for diff purpose..

So you cant say which is better one among both...

Inner join and outer join both are used...

reward if usefull...!!

with cheers

asha