Diff b/w inner join & for all entries

[Former Member]

Hi all!!

How to extract data from multiple tables?

What is the difference between inner join and for all entries?

Thanks.

[Former Member]

Hi,

INNER JOIN:

inner join used for combin two table.

support u can fetch two table. u can write select query two time .data base fetch time take twotime read from db.

so using primary key we easy write inner join. but both table primary key and both field refer same field.

Inner Join and Outer 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.

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.

FOR ALL ENTRIES works with a database in a quantity-oriented manner. Initially all data is collected in an internal table. Make sure that this table contains at least one entry (query sy-subrc or DESCRIBE), otherwise the subsequent transaction will be carried out without any restrictions).

SELECT...FOR ALL ENTRIES IN is treated like a SELECT statement with an external OR condition. The system only selects those table entries that meet the logical condition .

Using FOR ALL ENTRIES is recommended when data is not being read from the database, that is, it is already available in the program, for example, if the user has input the data. Otherwise a join is recommended.

Sample code for inner join with for all entries

SELECT s_{posnr s}lfimg s_{mbdat f}budat

INTO CORRESPONDING FIELDS OF TABLE i_delivery

FROM lips as s

INNER JOIN mkpf as f ON s_{vbeln = f}LE_VBELN

for all entries in itab " << change

WHERE s~vbeln in s_vbeln

AND s~mbdat in s_mbdat

AND f~budat in s_budat

AND s~erdat in s_erdat2

AND S~posnr = itab-posnr. "<< change

Regards

[Former Member]

U can extract the data from multiple tables in 3 ways.

1. Inner / outer joins

2. For All Entries

3. Logical Databases

Generally Innerjoins are used in between the tables who have parent child relation ship.

We can use for all entries in between two tables with having a single field in common. (Most Preferabble)

So use as per ur necessity and of the using innerjoin is an performance issue.

[Former Member]

Check out the below related threads

[vinod_vemuru2]

Hi Rajnikanth,

Extraction of data from multiple tables can be done in 2 ways.

1. Joins(Inner/Outer)

2. FOR ALL ENTRIES.

1. Joins.

Joins are used to get the data from multiple tables with single select query into one internal table.

2. For all entries will work like a select end select. But it will fetch the data in a single go. So it is much faster than Nested selects, select end select and Joins. But there are some mandatory checks has to be done for using FOR ALL ENTRIES.

1.CHECK whether driver table is INITIAL or NOT.(Mandatory)

2.SORT the Driver table based on the fields to be specified in WHERE CLAUSE. (To Improve performance)

3.DELETE ADJACENT DUPLICATES FROM Driver table. (To Improve performance)

Draw backs: Although it’s very good to use this still there are few draw backs with this.

1.If we don’t check the Driver table is INITIAL or NOT and if it is INITIAL then the select will fetch all the entries in data base which is unexpected result.

2.Field type and length of Driver table and driven table must match in the WHERE clause. Otherwise program will through syntax error. Some times for this requirement we need to take some extra internal table.

Both have their own advantages and disadvantages.

In joins u have all the data in single table but performance wise joins are slower compared to for all entries.

Specifying join condition is difficult task. If some thing goes wrong here it will fetch unexpected records.

For all entries limitations are already stated above.

So use JOINS or for all entries based on ur requirement.

Also check below links.

/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better

Thanks,

Vinod.

[Former Member]

inner join & for all entries both are same. but the difference way of using.

Mainly we can use innerjoins we r fetching less data . suppose

take two tables spfli(carrid,connid)

sflight(carrid,connid,fldate)

suppose u want fetch he information of flight kingfisher on which date it scheduled to travel

then u can go for innerjoins

select spfli~carrid

spfli~connid

fldate

from spfli

inner join sflight on

spfli-carrid=sflightcarrid and

spfli-connid=sflightconnid

where spfli-carrid = 'kf'

into table itab.

suppose u want get all the flight details in which date they scheduled u go for forallentries

first fetch data from spfli into it_spfli

select carrid

connid

from spfli

into table it_spfli

select carrid

connid

fldate

from sflight

into table it_sflight

forr all entries in table it_spfli

where carrid = it_spfli-carrid

connid = it_spfli-connid