topic Re: INNER JOIN in Application Development and Automation Discussions

INNER JOIN

Former Member — Mon, 11 Jun 2007 07:29:11 GMT

HAI,

WHAT IS THE DIFF TYPE OF INNER JOIN AND OUTER JOIN ?

EXPLAIN EACH TYPE .

THANK YOU

ASHOK KUMAR

Re: INNER JOIN

Former Member — Mon, 11 Jun 2007 07:33:46 GMT

HI,

see this link it will explain u in detail with simple examples

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

reward if helpful.

rgds,

bharat.

Re: INNER JOIN

Former Member — Mon, 11 Jun 2007 07:33:58 GMT

Hi Ashok

The different type of joins are:

Self join

Inner join

Cross join

Left outer join

Right outer join

inner join or an outer join.

n With an inner join, you only get those records which have an entry in all the tables included in the

view. With an outer join, on the other hand, those records that do not have a corresponding entry in

some of the tables included in the view are also selected.

n The hit list found with an inner join can therefore be a subset of the hit list found with an outer join.

n Database views implement an inner join. You only get those records which have an entry in all the

tables included in the view.

Check this links

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

http://help.sap.com/saphelp_nw04/helpdata/en/29/ca0b40c6c01961e10000000a155106/content.htm

reward all helpfull answers

Regards

Pavan

Re: INNER JOIN

Former Member — Mon, 11 Jun 2007 07:37:07 GMT

HI Ashok,

Inner join fetchs only matching rows from DB.

Outer join: it fetches matching and no-matching row from DB.

Regards

Suresh.d

Re: INNER JOIN

Former Member — Mon, 11 Jun 2007 07:39:17 GMT

See This

Table 1 Table 2

|--|||--|

\ /

Inner Join

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

Example

Output a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that 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 '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / DATE, CARRID, CONNID.

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 a table alias.

Note

In order to determine the result of a SELECT command where the FROM clause contains a join, the database system first 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 WHEREclause. The following example returns the same solution as the previous one.

Example

Output of a list of all flights from Frankfurt to New York between September 10th and 20th, 2001 that 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

WHERE F_{CONNID = P}CONNID

AND P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

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 that produce the same results on all of the supported database systems:

Only a table or view may appear to the right of the JOIN operator, not another join expression.

Only AND is possible in the ON condition as a logical operator.

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

If an outer join occurs in the FROM clause, all the ON conditions must contain at least one "real" JOIN condition (a condition that contains a field from tabref1 amd a field from tabref2.

Note

In some cases, '*' may be specified in the SELECT clause, and an internal table or work area is entered into the INTO clause (instead of a list of fields). If so, the fields are written to the target area from left to right in the order in which the tables appear in the FROM clause, according to the structure of each table work area. There can then be gaps between table work areas if you use an Alignment Request. For this reason, you should define the target work area with reference to the types of the database tables, not simply by counting the total number of fields. For an example, see below:

Variant 3

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

|--|||--|

\ /

Left Outer Join

NULL

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

Reward All Helpfull Answers...........

Re: INNER JOIN

Former Member — Mon, 11 Jun 2007 07:50:49 GMT

hi,

inner 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 or select query.

Database views implement an inner join.

outer join

With an outer join, records are also selected for which there is no entry in some of the tables used in the view or query.

Help views and maintenance views, however, implement an outer join.

types of joins

1) Self Join

2) Inner Join

3) Outer Join

4) Equi Join

regards,

Ashokreddy.

Re: INNER JOIN

Former Member — Mon, 11 Jun 2007 08:14:08 GMT

HI Ahok kumar,

If u any answer is sloved u r requirment give the points to that and close the question.

Regards.

DSK.

Re: INNER JOIN

Former Member — Mon, 11 Jun 2007 08:27:26 GMT

Take Table1 containing columns field1 field2 field3 and Table2 field1 field4 field5.

Inner Join on the tables can be applied based on the common column field1 and it can fetch all the other columns of both the tables. It fetches the records common to both the tables.

If you put outer join, it can be left-outer or right-outer. Left-outer means all records from table1 and commmon records from table2 and Right-outer means common records from table1 and all records from table2.