inner joins

[Former Member]

difference between inner joins and for all entries?

giv me with an example...

[Former Member]

difference between inner joins and for all entries?

giv me with an example...

[anversha_s]

hi,

<b>FOR ALL ENTRIES</b> is an effective way of doing away with using JOIN on two tables.

You can check the below code -

SELECT BUKRS BELNR GJAHR AUGDT

FROM BSEG

INTO TABLE I_BSEG

WHERE BUKRS = ....

SELECT BUKRS BELNR BLART BLDAT

FROM BKPF

INTO TABLE I_BKPF

FOR ALL ENTRIES IN I_BSEG

WHERE BUKRS = I_BSEG-BUKRS

AND BELNR = I_BSEG-BELNR

AND BLDAT IN SO_BLDAT.

*******************************8

look another example

what is the use of FOR ALL ENTRIES

1. INNER JOIN

DBTAB1 <----

---

> DBTAB2

It is used to JOIN two DATABASE tables

having some COMMON fields.

2. Whereas

For All Entries,

DBTAB1 <----

---

> ITAB1

is not at all related to two DATABASE tables.

It is related to INTERNAL table.

3. If we want to fetch data

from some DBTABLE1

but we want to fetch

for only some records

which are contained in some internal table,

then we use for alll entries.

*----

---

1. simple example of for all entries.

2. NOTE THAT

In for all entries,

it is NOT necessary to use TWO DBTABLES.

(as against JOIN)

3. use this program (just copy paste)

it will fetch data

from T001

FOR ONLY TWO COMPANIES (as mentioned in itab)

4

REPORT abc.

DATA : BEGIN OF itab OCCURS 0,

bukrs LIKE t001-bukrs,

END OF itab.

DATA : t001 LIKE TABLE OF t001 WITH HEADER LINE.

*----

---

itab-bukrs = '1000'.

APPEND itab.

itab-bukrs = '1100'.

APPEND itab.

*----

---

SELECT * FROM t001

INTO TABLE t001

FOR ALL ENTRIES IN itab

WHERE bukrs = itab-bukrs.

*----

---

LOOP AT t001.

WRITE 😕 t001-bukrs.

ENDLOOP.

*****************<b>inner join</b>**********

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

Hope this helps!

Regards,

Anver

if hlped pls mark points

[Former Member]

Hello,

If you want to fetch data by combining <b>more than 2 tables</b> we <b>use inner joins</b>.

If you want to fetch data from a table based on a selection made we go for for all entries .

In other way round for all entries can be used when you read data by combining <b>only</b> two tables eg: VBAK ~ VBAP.

reward if helps.

Thanks,

Krishna

[Former Member]

Refer these threads:

Regards,

ravi

[former_member283648]

Hi,

Inner joins is used to combine entries from two different database tables.

An example would be

select MATKL from vbak as a

inner join vbap as b

on a_{vbeln = b}vbeln

where posnr = '0001'.

And in case of for all entries you pick entries from one or more database tables and comparing with entries in an internal table.

select MATKL from vbak as a

inner join vbap as b

on a_{vbeln = b}vbeln

FOR ALL ENTRIES IN IT_TAB

where posnr = IT_TAB-POSNR.

[Former Member]

Please note that if you going to be reading loads of records that it is more efficient to use the inner join as it will only read thru all these records once sequentially.

Instead of first the one big database table and then comparing your results to another database table

[Former Member]

Inner joins -> it is basically a type of join where you relate two database tables whereby it would return the records which match the conditions.

For all enteries -> It is basically a variation of SELECT statement where it is interpreted as looping around a XYZ internal table and fetching records from the database matching the data from XYZ table.

Examples :

Inner join

SELECT a_{matnr b}maktl into table itab

from mara as a inner join makt as b on a_{matnr = b}matnr

where b~spras = 'EN'.

For all Enteries

SELECT matnr maktl into table itab

from makt

for all enteries in imat

where matnr = imat-matnr

and spras = 'EN'.

Both the SELECt would fetch data from makt for material and its text in English language.

Regards

Anurag

[Former Member]

Inner Join:

SELECT MKPF_{BUDAT MKPF}CPUTM

MSEG_{MATNR MSEG}WERKS MSEG~EBELN

MSEG_{EBELP MSEG}ERFMG

INTO CORRESPONDING FIELDS OF TABLE W_DTL_INVOICE

FROM MKPF INNER JOIN MSEG

ON MKPF_{MBLNR = MSEG}MBLNR

AND MKPF_{MJAHR = MSEG}MJAHR

WHERE MKPF~BUDAT > '20040721' AND

MSEG~BWART = '101' AND

MSEG~BUKRS = '1733'.

For all entries:

SELECT BUDAT CPUTM FROM MKPF

INTO CORREPSONDING FIELDS OFI_MKPF

WHERE BUDAT > '20040721'.

IF SY-SUBRC = 0.

SELECT MATNR WERKS EBELN EBELP ERFMG FROM MSEG

INTO CORRESPONDING FIELDS OF TABLE W_DTL_INVOICE

FOR ALL ENTRIES IN MKPF

WHERE MBLNR = MKPF-MBLNR

AND MJAHR = MSEG~MJAHR

AND MSEG~BWART = '101'

AND MSEG~BUKRS = '1733'.

ENDIF.

I hope it helps.

Best Regards,

Vibha

*Please mark all the helpful answers

[Former Member]

Hi...

difference btwn inner join and for all entries..

when you use for all entries to select values form to tables. it should have atleast one primary key field in common...

if you dont have a primary key field in commom, then you go for the inner joins or outer joins.....

regards

kothai

[Former Member]

Hi sunil,

When using FOR ALL ENTRIES the number of matching records is restricted to the number of records in the internal table. If the number of records in the database tables is too large then join would cause overheads in performance. Additionally a JOIN bypasses the table buffering.

regards,

keerthi.

[Former Member]

hi,

<b>using FOR ALL ENTRIES</b>

Only selects the records that meet the logical condition,returns the union of the solution sets of all SELECT statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result set.

tables:mara,marc.

parameters:p_matnr like mara-matnr.

data:begin of itab1 occurs 0,

matnr like mara-matnr,

ernam like mara-ernam,

end of itab1.

data:begin of itab2 occurs 0,

matnr like marc-matnr,

werks like marc-werks,

mmsta like marc-mmsta,

end of itab2.

select matnr ernam from mara

into table itab1

where matnr = p_matnr.

if sy-subrc = 0.

select matnr werks mmsta from marc

into table itab2

for all entries in itab1

where matnr = itab1-matnr.

endif.

sy-sburc returns whether the operation is successful,if it is then u can proceed further

<b>using INNER JOIN</b>

it just regroups the data from 2 or more relational database tables

select mara~matnr ernam werks mmsta

into table itab3

from mara inner join marc

on mara_{matnr = marc}matnr

where mara~matnr = p_matnr.

for more info press f1 and read the help

Regards,

Sowjanya.