topic Re: innner join? in Application Development and Automation Discussions

innner join?

Former Member — Fri, 20 Jun 2008 10:05:05 GMT

hi abapers.

wat is innerr join and outer join.....wat is the difference between inner join and for all entries? . which one is performance wise very good(tel me the reason)

Re: innner join?

Former Member — Fri, 20 Jun 2008 10:06:25 GMT

Hi Sankaran,

please check this link

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21ec77446011d189700000e8322d00/content.htm

The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view. With an outer join, records are also selected for which there is no entry in some of the tables used in the view.

The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view. Help views and maintenance views, however, implement an outer join.

Please check this link

for the DIFFERENCES

Best regards,

raam

Re: innner join?

Former Member — Fri, 20 Jun 2008 10:07:20 GMT

Hi,

Pls search through the SDN. You have many threads regarding this.

Regards,

Sharath

Re: innner join?

Former Member — Fri, 20 Jun 2008 10:09:43 GMT

hi check this..

Re: innner join?

Former Member — Fri, 20 Jun 2008 10:09:57 GMT

Hi,

Go through this links:

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm

Regards

Adil

Re: innner join?

Former Member — Fri, 20 Jun 2008 10:11:32 GMT

HI,

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.

Regards,

Suman

Re: innner join?

Former Member — Fri, 20 Jun 2008 10:12:32 GMT

What Is Inner and Left Outer Join

inner join

Example for Inner join:

SELECT A_{EBELN A}LIFNR A_{KNUMV B}EBELP B_{NETWR B}NETPR B_{WERKS B}MATNR

L_{NAME1 L}NAME2

FROM EKKO AS A

INNER JOIN EKPO AS B ON A_{EBELN = B}EBELN

INNER JOIN LFA1 AS L ON L_{LIFNR = A}LIFNR

INNER JOIN EKKN AS C ON C_{EBELN = A}EBELN

INTO CORRESPONDING FIELDS OF TABLE itab

WHERE B~BUKRS = 'company code' .

Left outer join

Usually, when defining InfoSets, the objects are linked via inner join operators. However, you can also use left outer joins. Inner join and left outer join are only different in the situation where one of the involved tables does not contain any suitable record which meets the join conditions.

With an inner join (table 1 inner join table 2), no record is included in the result set in this case. However, this means that the corresponding record from tables 1 is not considered in the results set.

With an left outer join (table 1 left outer join table2), exactly one record is included in the results set in this case´. In this record, the fields from table 1 contain the values of the record from table 1 and the fields from table 2 are all filled with the initial value.

Example of left outer join:

DATA: CUSTOMER TYPE SCUSTOM,

BOOKING TYPE SBOOK.

SELECT SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

SBOOK_{FLDATE SBOOK}CARRID SBOOK_{CONNID SBOOK}BOOKID

INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID)

FROM SCUSTOM LEFT OUTER JOIN SBOOK

ON SCUSTOM_{ID = SBOOK}CUSTOMID AND

SBOOK~FLDATE = '20081015'

ORDER BY SCUSTOM_{NAME SBOOK}FLDATE.

WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID.

ENDSELECT.

go through these links.

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

http://www.sap-img.com/abap/usage-of-for-all-entries-in-select-statement.htm

Re: innner join?

Former Member — Fri, 20 Jun 2008 10:14:42 GMT

Hi,

Inner join means retriving dat from 2 dat base tables , so hitting data base several time which is not preferable,

and

For all entries means u can select data from DBtable to itab and then u can perform that itab with another itab , so no.of hits on DB will be reduced, and it is preferable,

and left outer join is if we have 2 itabs and we want to retrive data based on one field then in first itab we have one more additional value then the second itab then that value also come into final itab

Regds,

Murali.