diff between joins & forallentries

[Former Member]

hi gurus we r retreving data from 2 tables we use either joins or forallentries , what is the actual diff between joins & forallentries .

[Former Member]

in Joins : we will fetch data using single select query by passing all required matching conditions.

Forallentries : We need to write separate select queries. Compare to Joins and Forallentries,joins will work better then the for all entries and it is quicker.

If answer is helpful then point shoud be rewarded.

Edited by: kinjal patel on Jul 15, 2008 5:59 AM

[Former Member]

join is used for fetching data from multiple database tables.

for all entries is used in the where clause when you want to fetch specific records that are there in the itab... which you specify in for all entries

[Former Member]

Hi,

There is difference between Join and for all entries. These two keywords holds a total different meaning.

Join : Join is to fetch data from two or more data base tables which has some key fields in common.

For All Entries : For all entries clause is to restrict the data seection in where clause. e.g. you have 100 records in your internal table itab and you want to fetch data from DB tables XXXX. then by using for all entries clause, only those records which are present in Itab and XXXX would come in output table.

One thing you must remember that which using for all entries clause always fetch all the primary keys of the DB table else you may get false information.

For more details you may browse SDN or Service Market place. You will get loads of documents.

Hope this helps!!!

Regards,

Lalit

[Former Member]

Hi,

In joint statment atatime u can get two table records into internal tables.

forall entries first u can get records from one table and then again u can get from another table for all entries which is in itab1.

but performans is very good in for all entries.

[Former Member]

hi,

select pernr ename gbdat from pa0001 into corresponding fields of table itab where pernr in S_PERNR.

if not itab[] is initial.

select pernr gbdat from pa0002 into corresponding fields of table jtab for all entries in itab

where pernr = itab-pernr.

endif.

in the above code for forall entries we select pernr and gbdat from pa0002 only for the pernr of itab selected from pa0001.

Performancewise it is better to use forall entries than join.

regards,

sreelakshmi

[Former Member]

Hi vasu,

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.

regards,

amit m.

[Former Member]

Hi,

both r of equal functionality with some options / restrictions.....

**JOIN is better performance wise ---no need to use FOR ALL ENTRIES if der r only 2 DB tables

**FOR ALL ENTRIES is also better if u r using more dan 4 or 5 DB tables where somtimes using JOIN condition is little bit complex

****most importantly FOR ALL ENTRIES hits the DB more than once also is used when u want comparing tables

*****wen we use FOR ALL ENTRIES...we hav to use SORT & DELETE ADJASCENT DUPLICATES in the code and also we hav to check each table(internal) if initial or not

*****Both r useful...but depends on ur requirement

Reward all helpful answers

Thanks

[Former Member]

hi,

Syntax of for all entries...

*FOR ALL ENTRIES IN itab WHERE col <operator> itab-comp *

The addition FOR ALL ENTRIES is only possible before WHERE conditions of the SELECT statement

If the addition FOR ALL ENTRIES is specified before the language element WHERE, then the components comp of the internal table itab can be used as operands when comparing with relational operators.

The internal table itab must have a structured line type and the component comp must be compatible with the column col.

eg.

PARAMETERS p_city TYPE spfli-cityfrom.

TYPES: BEGIN OF entry_tab_type,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

END OF entry_tab_type.

DATA: entry_tab TYPE TABLE OF entry_tab_type,

sflight_tab TYPE SORTED TABLE OF sflight

WITH UNIQUE KEY carrid connid fldate.

SELECT carrid connid

FROM spfli

INTO CORRESPONDING FIELDS OF TABLE entry_tab

WHERE cityfrom = p_city.

SELECT carrid connid fldate

FROM sflight

INTO CORRESPONDING FIELDS OF TABLE sflight_tab

FOR ALL ENTRIES IN entry_tab

WHERE carrid = entry_tab-carrid AND

connid = entry_tab-connid.

Syntax of join...

[(] {dbtab_left [AS tabalias_left]} | join

{[INNER] JOIN}|{LEFT [OUTER] JOIN}

{dbtab_right [AS tabalias_right] ON join_cond} [)] ... .

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.

• 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.

*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.

eg:-

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 )

hope this will helps u.

please reward if it helps.

thanks.

[Former Member]

Hi vasu,

Joins:

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Forallentries:

... [FOR ALL ENTRIES IN <itab>] WHERE <cond>

The condition <cond> may contain one or more comparisons, tests for belonging to intervals, value list checks, subqueries, selection table queries or null value checks, all linked with AND, OR, and NOT. If you use the FOR ALL ENTRIES addition, the condition <cond> is checked for each line of the internal table <itab> as long as <cond> contains a field of the internal table as an operand. For each line of the internal table, the system selects the lines from the database table that satisfy the condition. The result set is the union of the individual selections resulting from each line.

Regards,

Sravanthi

[Former Member]

Vasu,

Pls open to give points if your problem is addressed.

This wont take any thing from you but will give a boost to ppl answering questions over here.

Ppl answering here do work in project and take their time out to answer the questions. At times even one has to try and search before posting an answer.

But ya dont give points just to give. Mark all the helpful answers with points!!!!

Hope you will do this in future!!!

Regards,

Lalit