learn join

[Former Member]

hi

i wont to learn about join how to do inner outer left...

Regards

[abdulazeez12]

Inner join

IF p_bsart IS INITIAL.

SELECT ekko~bukrs

ekko~lifnr

ekko~ebeln

ekko~waers

ekko~bsart

ekko~ekorg

ekko~ekgrp

ekpo~ebelp

ekpo~txz01

ekpo~matnr

ekpo~werks

ekpo~menge

ekpo~meins

ekpo~netpr

ekpo~netwr

INTO TABLE t_itab1 FROM

ekko INNER JOIN ekpo ON ekko_{ebeln = ekpo}ebeln

WHERE ekko~ebeln IN s_ebeln AND

ekko~bukrs IN s_bukrs AND

ekko~lifnr IN s_lifnr AND

ekko~ekorg IN s_ekorg AND

ekko~ekgrp IN s_ekgrp AND

ekpo~matnr IN s_matnr.

The difference between an INNER JOIN and an OUTER JOIN is the following. If a query on an INNER JOIN of VBAK (outer table) and VBAP (inner table) finds a record in VBAK but no matching records in VBAP, then no data is retrieved from the database because the inner table is empty. If you still want to keep VBAK rows for which there are no matching VBAP rows, you need to use the OUTER JOIN construct available in ABAP/4 Open SQL in 4.x..

Hi

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.

[Former Member]

Hi,

Try this,

Select tab1_{field1 tab2}field2

into table t_table

from tab1

inner join tab2

on tab1_{field1 = tab2}field2

where field1 in s_field1.

follow these link for sample programs and statement on INNER JOIN...

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

http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_InnerJoinStatement.asp

Thanks,

<b><REMOVED BY MODERATOR></b>

Message was edited by:

Alvaro Tejada Galindo

[Former Member]

Hi

A join condition describes how the records of the two tables are connected.

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. (ABAP allows left outer join.)

[paruchuri_nagesh]

hi

EPORT ZINNERJOINS no standard page heading line-count 60(4).

TABLES : LFA1,EKKO,EKPO.

SELECT-OPTIONS : S_LIFNR FOR LFA1-LIFNR.

DATA : BEGIN OF ITAB OCCURS 0,

LIFNR LIKE LFA1-LIFNR,

NAME1 LIKE LFA1-NAME1,

EBELN LIKE EKKO-EBELN,

AEDAT LIKE EKKO-AEDAT,

EBELP LIKE EKPO-EBELP,

MENGE LIKE EKPO-MENGE,

END OF ITAB.

SELECT LFA1_{LIFNR LFA1}NAME1 EKKO_{EBELN EKKO}AEDAT EKPO_{EBELP EKPO}MENGE INTO TABLE ITAB FROM LFA1 INNER JOIN EKKO ON LFA1_{LIFNR = EKKO}LIFNR

INNER JOIN EKPO ON EKKO_{EBELN = EKPO}EBELN WHERE LFA1~LIFNR IN S_LIFNR.

LOOP AT ITAB.

WRITE:/ ITAB-LIFNR,ITAB-NAME1,ITAB-EBELN,ITAB-AEDAT,ITAB-EBELP,ITAB-MENGE.

ENDLOOP.

top-of-page.

write : / 'this is vendors report'.

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.

Note

If the same column name occurs in several database tables in a join expression, they have to be identified in all remaining additions of the SELECT statement by using the column selector ~.

<b><REMOVED BY MODERATOR></b>

regards

Nagesh.Paruchuri

Message was edited by:

Alvaro Tejada Galindo

[Former Member]

inner join:

suppose in table A the values are like the following:

material no type

1 fert

2 fert

3 roh

4 dien

suppose in tableB the values are like the following:

material no Plant

1 P1

2 P2

3 P3

Now if we want to find out the information about those materialswhich are comman from table like:

Material Material Description and Plant

then we have to go for inner join

like:

types : begin of ty_main,

matnr type mara-matnr,

mtart type mara-mtart,

werks type marc-werks,

end of ty_main.

data : it_main type standard table of ty_main,

wa_main type ty_main.

start-of-selection.

select a~matnr"

a~mtart"

b~werks

into table it_main

from mara as a

inner join marc as b

on a_{matnr = b}matnr

where a~matnr in s_matnr ."s_matnr(Values given by the user in the

selection screen)

if sy-subrc = 0."if selected.

at this point the values in the inetrnal tabel main will only those records

which are comman in both table A and Table B

which means:

Values in it_main will be like:

material number material type Plant

1 fert P1

2 fert P2

3 roh P3

here number of records is :3

endif.

Outer join:Now if we want to find not only those records which are comman to both tables but also those records which are in Table but not in B(Both comman and un comman with respect to Table A )

select a~matnr"

a~mtart"

b~werks

into table it_main

from mara as a

left outer join marc as b

on a_{matnr = b}matnr

where a~matnr in s_matnr ."s_matnr(Values given by the user in the

selection screen)

if sy-subrc = 0.

material number material type Plant

1 fert P1

2 fert P2

3 roh P3

4 dien

here the number of records is :4.

endif.

In this scenario:

we get the values of A along with values of B which are comman to both A and B

and also get the value of A which is not in B.First it assumes data is there in B and joins and then as the corresponding value for that number in A for Table B is kept as blank.Now the sata will be like this.

I think my answer is enough to throw adequate light on joining concept which gives you correct idea.If you feel it serves ur purpose give me marks.

Feel free to contact me if you have any further queries.

Thanks & Regards

Anand