inner joins and for all entries

Former Member · ‎10-13-2006

Hi Experts,

could you please help me how many types of ineer joins and their purpose

and what is the purpose of for all entries

plz give some documentation on these areas

Thanks,

Divya sree

Former Member · ‎10-13-2006

Hi divya,

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 · ‎10-13-2006

Will try to keep it simple.

Joins:

  Table1 Values:
  Field1 Field2
  ABC    123
  BCD    234
  CDE    345

  Table2 Values:
  Field1 Field3
  ABC    lmn
  CDE    nop

  Inner Join: TABLE1 inner join TABLE2
     Returns values when the key record exists in both tables.
     Internal Table
      Field1 Field2 Field2
      ABC    123    lmn
      CDE    345    nop

 Here the record BCD is excluded as itz not existing in table2.

   Outer Join:
      Returns values irrespective of existence in second table.
      Internal Table
      Field1 Field2 Field3
      ABC    123    lmn
      BCD    234
      CDE    345    nop

   Here the record BCD is still extracted but leaves it as blank where the record is not found.

For all Entries:
   Itz almost the same as inner join but here the first1 is internal table. Internal Table <b>inner join</b> DB Table.

One more important thing to remember is while using the for all entries statement, we have to check that internal table is not initial otherwise it extracts all the details from the second database table.

Hope the above info can give you some idea.

Kind Regards

Eswar

anversha_s · ‎10-14-2006

hi,

Inner join and outer join

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

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

FOR ALL ENTRIES 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.

Hope this helps!

Regards,

anver

pls mark points if userful

Former Member · ‎10-16-2006

hi,

Inner Join and Outer Join ..

Check

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

... FOR ALL ENTRIES IN itab WHERE cond

Effect

Only selects the records that meet the logical condition cond when each replacement symbol itab-f is replaced with the value of component f of the internal table itab for at least one line of the table. SELECT ... FOR ALL ENTRIES IN itab WHERE cond 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. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records (in the current client).

Example

Displaying the occupancy of flights on 28.02.2001:

TYPES: BEGIN OF ftab_type,

carrid TYPE sflight-carrid,

connid TYPE sflight-connid,

END OF ftab_type.

DATA: ftab TYPE STANDARD TABLE OF ftab_type WITH

NON-UNIQUE DEFAULT KEY INITIAL SIZE 10,

free TYPE I,

wa_sflight TYPE sflight.

Suppose FTAB is filled as follows:
CARRID CONNID
--------------
LH 2415
SQ 0026
LH 0400

SELECT * FROM sflight INTO wa_sflight

FOR ALL ENTRIES IN ftab

WHERE CARRID = ftab-carrid AND

CONNID = ftab-connid AND

fldate = '20010228'.

free = wa_sflight-seatsocc - wa_sflight-seatsmax.

WRITE: / wa_sflight-carrid, wa_sflight-connid, free.

ENDSELECT.

The statement has the same effect as:

SELECT DISTINCT * FROM sflight INTO wa_sflight

WHERE ( carrid = 'LH' AND

connid = '2415' AND

fldate = '20010228' ) OR

( carrid = 'SQ' AND

connid = '0026' AND

fldate = '20010228' ) OR

( carrid = 'LH' AND

connid = '0400' AND

fldate = '20010228' ).

free = wa_sflight-seatsocc - wa_sflight-seatsmax.

WRITE: / wa_sflight-carrid, wa_sflight-connid, free.

ENDSELECT.

Notes

You can only use ... FOR ALL ENTRIES IN itab WHERE cond in a SELECT statement.

In the logical condition cond, the symbol itab-f is always a replacement symbol, and should not be confused with the component f of the header line of the internal table itab. The internal table itab does not need to have a header line.

Each component of the internal table that occurs in a replacement symbol in the WHERE condition must have exactly the same type and length as the corresponding component in the database table.

You cannot use replacement symbols in comparisons in LIKE, BETWEEN, or IN expressions.

If you use FOR ALL ENTRIES IN itab, you cannot use ORDER BY f1 ... fn in the ORDER-BY clause.

If you use FOR ALL ENTRIES IN itab, you cannot use a HAVING clause as well.

Regards,

Santosh

Former Member · ‎10-16-2006

WE have only ONE INNER JOIN in ABAP and one OUTER JOIN.

1) When you do inner join, it will select all the data from the LEFT table which statiscies the condition given with the RIGHT table. This is nothing but LEFT INNER JOIN or SIMPLY INNER JOIN

2) Where as OUTER JOIN, will picks up all the values from both the tables which satisfies the condition.

http://sap-img.com/abap/inner-joins.htm

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

3) FOR ALL ENTRIES are used with internal tables.

If we want to select all the value for the internal table content then, we can use FOR ALL ENTRIES.

EG: http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_ForAllEntries.asp

~thomas.

Former Member · ‎10-17-2006

remark to this :

SELECT * FROM t001

INTO TABLE t001

FOR ALL ENTRIES IN itab

WHERE bukrs = itab-bukrs.

you should code "describe table itab line sy-tfill" before that statement coz if there is no entry in your itab it will select all the T001 ! for t001 it's ok but guess if you do this on COEP or such big tables...

Former Member · ‎10-18-2006

Hi,

Go thru this docu.

SELECT - join

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.

FOR ALL ENTRIES WHERE

Syntax

... FOR ALL ENTRIES IN itab WHERE ... col operator itab-comp ...

Effect

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.

The logical expression sql_cond of the WHERE condition can comprise various logical expressions by using AND and OR. However, if FOR ALL ENTRIES is specified, there must be at least one Comparison with a column of the internal table itab, which can be specified either statistically or dynamically (Release 6.40 and higher). In a statement with a SELECTstatement with FOR ALL ENTRIES, the addition ORDER BY can only be used with the addition PRIMARY KEY.

The whole logical expression sql_cond is evaluated for each individual line of the internal table itab. The resulting set of the SELECT statement is the union of the resulting sets from the individual evaluations. Duplicate lines are automatically removed from the resulting set. If the internal table itab is empty, the whole WHERE statement is ignored and all lines in the database are put in the resulting set.

Notes

In Release 6.10 and higher, the same internal table can be specified after FOR ALL ENTRIES and after INTO.

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

Example

Exporting all flight data for a specified departure city. The relevant airlines and flight numbers are first put in an internal table entry_tab, which is evaluated in the WHERE condition of the subsquent SELECT statement.

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.

Thanks

Sunil