deta base related

[Former Member]

Hi all,

what is the Outer Join? and what is the diff b/w Outerjoins and innerjoins?

ple send the syntax and examples also.

Regards,

sri.

[Former Member]

Hi all,

what is the Outer Join? and what is the diff b/w Outerjoins and innerjoins?

ple send the syntax and examples also.

Regards,

sri.

[Former Member]

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.

Example for Inner join:

SELECT A_{EBELN A}LIFNR A_{KNUMV B}EBELP B_{NETWR B}NETPR B_{WERKS B}MATNR

L_{NAME1 L}NAME2

FROM EKKO AS A

INNER JOIN EKPO AS B ON A_{EBELN = B}EBELN

INNER JOIN LFA1 AS L ON L_{LIFNR = A}LIFNR

• INNER JOIN EKKN AS C ON C_{EBELN = A}EBELN

INTO CORRESPONDING FIELDS OF TABLE itab

WHERE B~BUKRS = 'company code' .

Left outer join

Usually, when defining InfoSets, the objects are linked via inner join operators. However, you can also use left outer joins. Inner join and left outer join are only different in the situation where one of the involved tables does not contain any suitable record which meets the join conditions.

With an inner join (table 1 inner join table 2), no record is included in the result set in this case. However, this means that the corresponding record from tables 1 is not considered in the results set.

With an left outer join (table 1 left outer join table2), exactly one record is included in the results set in this case´. In this record, the fields from table 1 contain the values of the record from table 1 and the fields from table 2 are all filled with the initial value.

Example of left outer join:

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 = '20081015'

ORDER BY SCUSTOM_{NAME SBOOK}FLDATE.

WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID.

ENDSELECT.

I hope it helps.

Best Regards,

Vibha

Please mark all the helpful answers

[Former Member]

Hello,

Out join is the opposite of inner join.

There are two types of outer joins: left outer join and right outer join. When you use an outer join only the records that exists in the outer table are selected (if is an LEFT outer join only the records from the left table -in the select command - that doesn't exists in the right table are selected and if is an RIGHT outer join, the recors from the left table that doesn't exists in the left table -from select command - are selected).

In the following you can find some examples: [http://help.sap.com/saphelp_nw04s/helpdata/en/67/7e4b3eaf72561ee10000000a114084/frameset.htm] and [https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/5c68a190-0201-0010-f0b0-8ade1a71dc8c].

Regards,

[Former Member]

Hi,

Hope the below will be usefull.

Select... FROM tabref1 [INNER] JOIN tabref2 ON cond

Effect

Selects data from the transparent database tables or views specified in tabref1 and tabref2. tabref1 and tabref2 either have the same form as in variant 1 or are themseleves joine expressions. The key word INNER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized in the ABAP-Dictionary.

In a relational data structure, it is quite normal for data that belongs together to be split up across several tables to help standardization (see relational database). To regroup this information in a database query, you can link tables using a join command. This formulates conditions for the columns of the tables involved. An inner join contains all combinations of lines from database table tabref1 with lines from database table tabref2 that meet the condition specified in the logical condition ON cond.

Inner join between table 1 and table 2 where column D sets 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

|--|||--|

\ /

\ /

\ /

\ /

\/

Inner 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 |

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

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

Example

Output a list of all flights from Frankfurt to New York between 10th and 20th September, which are not sold out:

DATA: DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.

SELECT F_{CARRID F}CONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID AND

F_{CONNID = P}CONNID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '19970910' AND '19970920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

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

Note

In order to determine the result of a SELECT statement where the FROM clause contains a join, the database system creates a temporary table containing the lines that meet the ON condition. The WHERE condition is then applied to the temporary table. It does not matter in an inner join whether the condition is in the ON or the WHERE clause. The following example returns the same solution as the previous one.

Example

Output a list of all flights from Frankfurt to New York between 10th and 20th September, which are not sold out:

Effect

Instead of the TABLES addition, you should use the USING or CHANGING addition. When you use the TABLES addition, only tables with the table type STANDARD are allowed. The internal tables specified are passed to the FORM together with their header lines. If you use a table without a header line as a TABLES parameter, the system automatically generates a header line for it. This is only valid within the FORM. For this reason, you should not use global commands such as HIDE in the header line. For details of how to specify a type for a TABLES parameter, see specifying a type. TABLES parameters are always passed by reference.

Example

DATA: BEGIN OF X OCCURS 0.

INCLUDE STRUCTURE SFLIGHT.

DATA: ADDITION(8) TYPE C,

END OF X.

...

PERFORM U TABLES X.

...

FORM U TABLES X STRUCTURE SFLIGHT.

WRITE: X-FLDATE.

ENDFORM.

Addition 2

... USING [VALUE(p1)|p1] ... [VALUE(pn)|pn]

Effect

Defines the formal parameters p1,...pn, which are replaced by actual parameters when the subroutine is called.

You can assign a type to the formal parameters p1 ... pn (see assigning types). You can also specify the method for passing the parameter.

Note

Passing methods:

USING ... p ...

The parameter is passed by reference. The reference field can be changed within the subroutine. The changes apply beyond the subroutine.

USING ... VALUE(p) ...

If you use the VALUE(...) addition, you can pass the field contents to a corresponding local field. VALUE parameters behave like local fields.

Example

TYPES: BEGIN OF FLIGHT_STRUC,

FLCARRID LIKE SFLIGHT-CARRID,

PRICE LIKE SFLIGHT-FLDATE,

END OF FLIGHT_STRUC.

DATA: MY_FLIGHT TYPE FLIGHT_STRUC OCCURS 0,

IBOOK1 LIKE SBOOK OCCURS 0,

IBOOK2 LIKE IBOOK1 OCCURS 0,

STRUC LIKE SBOOK.

PERFORM DISPLAY USING MY_FLIGHT IBOOK1 IBOOK2 STRUC.

FORM DISPLAY USING P_ITAB LIKE MY_FLIGHT[]

P_BOOK1 LIKE IBOOK1[]

P_BOOK2 LIKE IBOOK2[]

P_STRU LIKE STRUC.

DATA: L_FLIGHT LIKE LINE OF P_ITAB,

L_CARRID LIKE L_FLIGHT-FLCARRID.

...

WRITE: / P_STRU-CARRID, P_STRU-CONNID.

...

LOOP AT P_ITAB INTO L_FLIGHT WHERE FLCARRID = L_CARRID.

...

ENDLOOP.

ENDFORM. Frankfurt to New York between 10th and 20th September 1997:

DATA: DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.

SELECT F_{CARRID F}CONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID

WHERE F_{CONNID = P}CONNID

AND P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '19970910' AND '19970920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

ENDSELECT.

Note

Since not all of the database systems supported by SAP use the standard syntax for ON conditions, the syntax has been restricted. It only allows those joins which produce the same results on all of the supported database systems:

Only a table or view may come to the right of the JOIN statement - not another join expression

The only logical operator you can use in the JOIN condition is AND

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

If there is an outer join in the FROM clause, each ON condition must contain at least one "real" JOIN condition (one containing a field from both tabref1 and tabref2.

Note

If you specify '*' as the field list in the SELECT clause, and an internal table or work area instead of a field list in the INTO clause, the fields are inserted into the target area in the sequence in which they occur in the table in the FROM clause but according to the structure of the target work area. This may, however, contain gaps due to alignment requirements. For this reason, you should define your work area with reference to the type of a database table instead of as a list of fields. An example of this appears later in the documentation.

Variant 4

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

Effect

Selects data from the transparent database tables or views specified in tabref1 and tabref2. tabref1 and tabref2 either have the same form as in variant 1 or are themseleves joine expressions. The key word OUTER can be omitted. The database tables or views specified in tabref1 and tabref2 must be recognized in the ABAP Dictionary and declared in the program with an appropriate TABLES statement.

In order to determine the result of a SELECT statement 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 NULL values. The system then applies the WHERE condition to the table. t

Left outer join between table 1 and table 2 where column D sets the join condition.

Tabelle 1 Tabelle 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 customers with their bookings (if applicable) for 15th October 1997:

TABLES: SCUSTOM, SBOOK.

SELECT SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

SBOOK_{FLDATE SBOOK}CARRID SBOOK_{CONNID SBOOK}BOOKID

INTO (SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY,

SBOOK-FLDATE, SBOOK-CARRID, SBOOK-CONNID,

SBOOK-BOOKID)

FROM SCUSTOM LEFT OUTER JOIN SBOOK

ON SCUSTOM_{ID = SBOOK}CUSTOMID AND

SBOOK~FLDATE = '19971015'

ORDER BY SCUSTOM_{NAME SBOOK}FLDATE.

WRITE: / SCUSTOM-NAME, SCUSTOM-POSTCODE, SCUSTOM-CITY,

SBOOK-FLDATE, SBOOK-CARRID, SBOOK-CONNID,

SBOOK-BOOKID.

ENDSELECT.

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

Note

When you use a left outer join in the FROM clause of a SELECT command, it makes a crucial difference whether the logical condition is in the ON or the WHERE clause. 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 statement, not another join expression

The only logical operator allowed in the ON condition is AND

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

Comparisons in the WHERE condition may not contain fields from the right-hand table

You can only use EQ (or 😃 as comparisons in the ON condition

The ON condition must contain at least one "real" JOIN condition (a condition containing a field from both tabref1 and tabref2)

Note

If you specify '*' as the field list in the SELECT clause, and an internal table or work area instead of a field list in the INTO clause, the fields are inserted into the target area in the sequence in which they occur in the table in the FROM clause but according to the structure of the target work area. This may, however, contain gaps due to alignment requirements. For this reason, you should define your work area with reference to the type of a database table instead of as a list of fields. An example of this appears later in the documentation.

Example

Example of a JOIN containing more than 2 tables: Select all flights from Frankfurt to New York between 10th and 20th September 1997 where there are free places, and display the name of the airline.

DATA: CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID,

DATE LIKE SFLIGHT-FLDATE,

NAME LIKE SCARR-CARRNAME.

SELECT F_{CARRID F}CONNID F_{FLDATE C}CARRNAME

INTO (CARRID, CONNID, DATE, NAME)

FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID AND

F_{CONNID = P}CONNID )

INNER JOIN SCARR AS C

ON F_{CARRID = C}CARRID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '19970910' AND '19970920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / NAME, DATE, CARRID, CONNID.

ENDSELECT.

Reward if usefull.

Regrards,

Ramya.R