hi experts

[Former Member]

hi guru.

please tell me about exactly the <b>outer join functionlity</b> in real time.

thank u.

subhasis.

[Former Member]

hi guru.

please tell me about exactly the <b>outer join functionlity</b> in real time.

thank u.

subhasis.

[Former Member]

Hi ,

Check this program.

REPORT ztest_selects LINE-SIZE 80 MESSAGE-ID 00.

DATA: t001      TYPE t001,
      bkpf      TYPE bkpf.

SELECT-OPTIONS: s_bukrs FOR  bkpf-bukrs MEMORY ID buk      OBLIGATORY.

SELECT-OPTIONS: s_belnr FOR  bkpf-belnr MEMORY ID bln      OBLIGATORY.
PARAMETERS:     p_gjahr LIKE bkpf-gjahr MEMORY ID gjr      OBLIGATORY.

SELECTION-SCREEN ULINE.

PARAMETERS:     p_loop1 TYPE i                             OBLIGATORY
                        DEFAULT 5,
                p_loop2 TYPE i                             OBLIGATORY
                        DEFAULT 10.

TYPES: BEGIN OF t001_type,
         bukrs  TYPE t001-bukrs,
       END   OF t001_type,

       BEGIN OF bkpf_type,
         bukrs  TYPE bkpf-bukrs,
         belnr  TYPE bkpf-belnr,
         gjahr  TYPE bkpf-gjahr,
       END   OF bkpf_type.

DATA: t001_int  TYPE TABLE OF t001_type,
      t001_wa   TYPE          t001_type,
      bkpf_int  TYPE TABLE OF bkpf_type,
      bkpf_wa   TYPE          bkpf_type.

DATA: start     TYPE i,
      end       TYPE i,
      dif       TYPE i.

START-OF-SELECTION.

  DO p_loop1 TIMES.
    PERFORM simple_select.
    PERFORM nested_select.
    PERFORM for_all_entries.
    PERFORM inner_join.
    PERFORM outer_join.
    PERFORM sub_query.
    PERFORM unqualified_select.
    SKIP 1.
  ENDDO.

*&---------------------------------------------------------------------*
*&      Form  simple_select
*&---------------------------------------------------------------------*
* First we get documents using a select statement that is
* fully qualified on the primary key. Because buffering may be an issue,
* the first select will be disregarded in this test. However, in real
* life, this would be the important time.
*----------------------------------------------------------------------*
FORM simple_select.

* Do an initial select of the documents we intend to get. Due to
* buffering, the first select may take much longer then the next one.

  SELECT  bukrs belnr gjahr
    FROM  bkpf
    INTO  TABLE bkpf_int
    WHERE bukrs IN s_bukrs
      AND belnr IN s_belnr
      AND gjahr EQ p_gjahr.

  IF sy-subrc <> 0.
    MESSAGE ID '00' TYPE 'E' NUMBER '001' WITH
               'No Data meets selection criteria'.
  ENDIF.

* Next we get the same document using the same fully qualified select
* statement. We will use this in comparisons.
  GET RUN TIME FIELD start.

  DO p_loop2 TIMES.
    SELECT  bukrs belnr gjahr
      FROM  bkpf
      INTO  TABLE bkpf_int
      WHERE bukrs IN s_bukrs
        AND belnr IN s_belnr
        AND gjahr EQ p_gjahr.
  ENDDO.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for first SELECT   (fully qualified)',
          055 ':', dif, 'microseconds'.
ENDFORM.                    " simple_select

*&---------------------------------------------------------------------*
*&      Form  nested_select
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM nested_select.

* Use the same fully qualified SELECT, but this time nested. As usual,
* Ignore the first SELECT and use the subsequent ones for comparison.

  REFRESH: bkpf_int.

  SELECT  bukrs FROM t001
    INTO  t001_wa
    WHERE bukrs IN s_bukrs.

    SELECT  bukrs belnr gjahr
      FROM  bkpf
      INTO  bkpf_wa
      WHERE bukrs EQ t001_wa-bukrs
        AND belnr IN s_belnr
        AND gjahr EQ p_gjahr.

      APPEND bkpf_wa TO bkpf_int.

    ENDSELECT.
  ENDSELECT.

* Next we get the same document using the same fully qualified select
* statement. We will use this in comparisons.
  GET RUN TIME FIELD start.

  DO p_loop2 TIMES.

    REFRESH: bkpf_int.

    SELECT  bukrs FROM t001
      INTO  t001_wa
      WHERE bukrs IN s_bukrs.

      SELECT  bukrs belnr gjahr
        FROM  bkpf
        INTO  bkpf_wa
        WHERE bukrs EQ t001_wa-bukrs
          AND belnr IN s_belnr
          AND gjahr EQ p_gjahr.

        APPEND bkpf_wa TO bkpf_int.

      ENDSELECT.
    ENDSELECT.
  ENDDO.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for second SELECT  (nested)',
          055 ':', dif, 'microseconds'.
ENDFORM.                    " nested_select

*&---------------------------------------------------------------------*
*&      Form  for_all_entries
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM for_all_entries .

* Use the same fully qualified SELECT, but this time with
* FOR ALL ENTRIES.

* Ignore the first set of SELECTs
  SELECT  bukrs
    FROM  t001
    INTO  TABLE t001_int
    WHERE bukrs IN s_bukrs.

  SELECT  bukrs belnr gjahr
    FROM  bkpf
    INTO TABLE bkpf_int
    FOR ALL ENTRIES IN t001_int
    WHERE bukrs EQ t001_int-bukrs
      AND belnr IN s_belnr
      AND gjahr EQ p_gjahr.

* Use these SELECTs in comparisons.
  GET RUN TIME FIELD start.

  DO p_loop2 TIMES.
    SELECT  bukrs
      FROM  t001
      INTO  TABLE t001_int
      WHERE bukrs IN s_bukrs.

    SELECT  bukrs belnr gjahr
      FROM  bkpf
      INTO TABLE bkpf_int
      FOR ALL ENTRIES IN t001_int
      WHERE bukrs EQ t001_int-bukrs
        AND belnr IN s_belnr
        AND gjahr EQ p_gjahr.
  ENDDO.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for third SELECT   (using FOR ALL ENTRIES)',
          055 ':', dif, 'microseconds'.

ENDFORM.                    " for_all_entries

*&---------------------------------------------------------------------*
*&      Form  inner_join
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM inner_join .

* Use the same fully qualified SELECT, but this time with an INNER JOIN.

  SELECT  t001~bukrs bkpf~belnr bkpf~gjahr
    FROM  bkpf
      INNER JOIN t001 ON
        t001~bukrs EQ bkpf~bukrs
    INTO TABLE bkpf_int
    WHERE t001~bukrs IN s_bukrs
      AND bkpf~belnr IN s_belnr
      AND bkpf~gjahr EQ p_gjahr.

* Use this select in comparisons.
  GET RUN TIME FIELD start.

  DO p_loop2 TIMES.
    SELECT  t001~bukrs bkpf~belnr bkpf~gjahr
      FROM  bkpf
        INNER JOIN t001 ON
          t001~bukrs EQ bkpf~bukrs
      INTO TABLE bkpf_int
      WHERE t001~bukrs IN s_bukrs
        AND bkpf~belnr IN s_belnr
        AND bkpf~gjahr EQ p_gjahr.
  ENDDO.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for fourth SELECT  (using an INNER JOIN)',
          055 ':', dif, 'microseconds'.

ENDFORM.                    " inner_join

*&---------------------------------------------------------------------*
*&      Form  outer_join
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM outer_join .

* Use the same fully qualified SELECT, but this time with an OUTER JOIN.

  SELECT  t001~bukrs bkpf~belnr bkpf~gjahr
    FROM  bkpf
      LEFT OUTER JOIN t001 ON
        t001~bukrs EQ bkpf~bukrs
    INTO TABLE bkpf_int
    WHERE bkpf~bukrs IN s_bukrs
      AND bkpf~belnr IN s_belnr
      AND bkpf~gjahr EQ p_gjahr.

* Use this select in comparisons.
  GET RUN TIME FIELD start.

  DO p_loop2 TIMES.
    SELECT  t001~bukrs bkpf~belnr bkpf~gjahr
      FROM  bkpf
        LEFT OUTER JOIN t001 ON
          t001~bukrs EQ bkpf~bukrs
      INTO TABLE bkpf_int
      WHERE bkpf~bukrs IN s_bukrs
        AND bkpf~belnr IN s_belnr
        AND bkpf~gjahr EQ p_gjahr.
  ENDDO.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for fifth SELECT   (using an OUTER JOIN)',
          055 ':', dif, 'microseconds'.

ENDFORM.                    " outer_join

*&---------------------------------------------------------------------*
*&      Form  sub_query
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM sub_query .

* And a sub-query

  SELECT bukrs belnr gjahr
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs IN
      ( SELECT bukrs
          FROM  t001
          WHERE bukrs IN s_bukrs )
      AND belnr IN s_belnr
      AND gjahr EQ p_gjahr.

* Use this select in comparisons.
  GET RUN TIME FIELD start.

  DO p_loop2 TIMES.
    SELECT  bukrs belnr gjahr
      FROM  bkpf
      INTO TABLE bkpf_int
      WHERE bukrs IN
        ( SELECT  bukrs
            FROM  t001
            WHERE bukrs IN s_bukrs )
        AND belnr IN s_belnr
        AND gjahr EQ p_gjahr.
  ENDDO.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for sixth SELECT   (using a sub-query)',
          055 ':', dif, 'microseconds'.

ENDFORM.                    " sub_query

*&---------------------------------------------------------------------*
*&      Form  unqualified_select
*&---------------------------------------------------------------------*
* Compare the above results with a SELECT that is only partially
* qualified.
*----------------------------------------------------------------------*
FORM unqualified_select.

* Ignore the first SELECT
  SELECT  bukrs belnr gjahr
    FROM  bkpf
    INTO  TABLE bkpf_int
    WHERE belnr IN s_belnr
      AND gjahr EQ  p_gjahr.

* Use this select in comparisons.
  GET RUN TIME FIELD start.

  DO p_loop2 TIMES.
    SELECT  bukrs belnr gjahr
      FROM  bkpf
      INTO  TABLE bkpf_int
      WHERE belnr IN s_belnr
        AND gjahr EQ  p_gjahr.
  ENDDO.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for seventh SELECT (partially qualified)',
          055 ':', dif, 'microseconds'.
ENDFORM.                    " unqualified_select

And this too.

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

[Former Member]

Hi!

Go through please on the following link:

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

Regards

Tamá

[Former Member]

... FROM tabref1 LEFT [OUTER] JOIN tabref2 ON cond

Effect

Selects the data from the transparent database tables and/or views specified in tabref1 and tabref2. tabref1 und tabref2 both have either the same form as in variant 1 or are themselves join expressions. The keyword OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized by the ABAP-Dictionary.

In order to determine the result of a SELECT command where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left-hand table (tabref1) are then added to this table, and their corresponding fields from the right-hand table are filled with ZERO values. The system then applies the WHERE condition to the table.

Left outer join between table 1 and table 2 where column D in both tables set the join condition:

Table 1 Table 2

A

B

C

D

D

E

F

G

H

a1

b1

c1

1

1

e1

f1

g1

h1

a2

b2

c2

1

3

e2

f2

g2

h2

a3

b3

c3

2

4

e3

f3

g3

h3

a4

b4

c4

3

|--|||--|

\ /

\ /

\ /

\ /

\/

Left Outer Join

|--||||||||--|

| A | B | C | D | D | E | F | G | H |

|--||||||||--|

| a1 | b1 | c1 | 1 | 1 | e1 | f1 | g1 | h1 |

| a2 | b2 | c2 | 1 | 1 | e1 | f1 | g1 | h1 |

| a3 | b3 | c3 | 2 |NULL|NULL|NULL|NULL|NULL|

| a4 | b4 | c4 | 3 | 3 | e2 | f2 | g2 | h2 |

|--||||||||--|

Example

Output a list of all custimers with their bookings for October 15th, 2001:

DATA: CUSTOMER TYPE SCUSTOM, 
      BOOKING  TYPE SBOOK. 

SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY 
       SBOOK~FLDATE SBOOK~CARRID SBOOK~CONNID SBOOK~BOOKID 
       INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY, 
             BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID, 
             BOOKING-BOOKID) 
       FROM SCUSTOM LEFT OUTER JOIN SBOOK 
         ON SCUSTOM~ID = SBOOK~CUSTOMID AND 
            SBOOK~FLDATE = '20011015' 
       ORDER BY SCUSTOM~NAME SBOOK~FLDATE. 
  WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY, 
           BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID, 
           BOOKING-BOOKID. 
ENDSELECT.

If there are columns with the same name in both tables, you must distinguish between them by prefixing the field descriptor with the table name or using an alias.

Note

For the resulting set of a SELECT command with a left outer join in the FROM clause, it is generally of crucial importance whether a logical condition is in the ON or WHERE condition. Since not all of the database systems supported by SAP themselves support the standard syntax and semantics of the left outer join, the syntax has been restricted to those cases that return the same solution in all database systems:

Only a table or view may come after the JOIN operator, not another join statement.

The only logical operator allowed in the ON condition is AND.

Each comparison in the ON condition must contain a field from the right-hand table.

Comparisons in the WHERE condition must not contain a field from the right-hand table.

The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs).

[Former Member]

hi,

With the use of outer join you can join the tables even there is no entry in all the tables used in the view.

refer the link,

this may give an idea,

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

Regards,

Indira.D

[Former Member]

Hi

If you have 2 tables tab1 and tab2.

If you use outer join,then you get data which is in Tab1 and will display

if you use innerjoin,then data from tab1 will check the data in tab2 and if corresponsing data is there,then it will display.

Thanks