inner and outer join

[former_member549415]

hi experts,

when we used inner join and outer join ,then where the data is filtred e.g. application server ,database or presentation?

thaks in advanced.

[Former Member]

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.

Follow this link:

http://help.sap.com/saphelp_erp2005/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm

for example below are the 2 tables and their values.

A B

1000 1000 A

2000 1000 B

3000 3000 C

inner join will fetch the common records as like follows.

1000 A

1000 B

3000 C

If it is the case of outer join the output will be

1000 A

1000 B

2000

3000 C

Thanks,

Anon

[Former Member]

Hi,

You will be using INNER and OUTER joins in a SELECT query. SELECT query always refers to Data base only. So INNER and OUTER joins always filter the data at Data base level only.

thanks & regards

Kishore Kumar Maram

[Former Member]

hi,

you can use inner join where you want only common data from two or more tables.

and outer join when you want common as well as all data from one table.

e.g if you apply left outer joiin on table1 and table 2

then you will get common data from both table and remaning lines fron table1 . in data which are not common will have null values corresponding to table2 entries.

reward if useful.

[anversha_s]

hi,

Please go thru this. u will get clear idea.

table - emp

empno name

a sasi

b xxx

c yyy

table- sal

empno salary

a 1000

b 2000

Inner join

****************

select e~empno e~name 
s~sal 
into table int_table
from emp as e
inner join sal
on 
e~empno = s~empno.

if you made inner join between table a and b by emp no

the selection retrives only if the condition satisfy the output will be

a sasi 1000

b xxx 2000

Outer join

*************************

select e~empno e~name 
s~sal into table  int_table
from emp as e
LEFT OUTER JOIN sal
on 
e~empno = s~empno.

if you made outer join (left /right ) the left table kept as it is the

if the condition satisfy the right table entries will fetch else leave it blank

the output will be

a sasi a 1000

b xxx b 2000

c yyy

Rgds

Anversha

[Former Member]

Hi

inner join

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.

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.

[Former Member]

Hi Somnath,

The data is filtered at the database only and not at the presentation layer or applicatiion layer.Only the filtered data is transferred to the application layer.

Pls refer the following link for any clarifications you might have

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3976358411d1829f0000e829fbfe/content.htm

Pls reward points if found useful..