joins

[Former Member]

plz give me some materials of using inner and outer joins

[Former Member]

Hi Rohit,

Some tips:

Proper use of Inner Join

When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.

Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.

Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.

Select a_{airln a}lnnam b_{fligh b}cntry into table int_airdet

From zairln as a inner join zflight as b on a_{airln = b}airln.

In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.

----

---

An inner join is a SQL statement that links segments (lines) of tables. It

is

hierarchical, meaning that you start from one table that will control the

subsequent flow of selected data.

You have inner left joins and left-out joins. In inner left joins one line

of

the left table will always have a related line from the right side table.

In

left-out joins, the lines from the left table will be retrieved from the

database even if there is no match from the right side table.

Examples:

SELECT MARAMATNR MARABSRH MAKTMAKTX INTO (MARA-MATNR, MARA-BSRH,

MAKT-MAKTX )

FROM ( MARA INNER JOIN MAKT ON MAKTMATNR = MARAMATNR )

WHERE MARAMATNR IN MATNR

AND MAKTSPRAS = SY-LANGU.

ENDSELECT.

I'm sure you understand this one: If the description of the material exist

in

the logon language, them the materials in the select-options MATBR are

going to

be retrieved from the database, if they do not have a description in that

language - ZERO matches.

SELECT MARAMATNR MARABSRH MAKTMAKTX INTO (MARA-MATNR, MARA-BSRH,

MAKT-MAKTX )

FROM ( MARA LEFT OUTER JOIN MAKT ON MAKTMATNR = MARAMATNR )

WHERE MARAMATNR IN MATNR

AND MAKTSPRAS = SY-LANGU.

ENDSELECT.

http://help.sap.com/saphelp_erp2004/helpdata/en/f1/713c3b35703079e10000000a114084/content.htm

Cheers

VJ

Message was edited by: Vijayendra Rao