Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

diff between inner join and outer join

Former Member
0 Kudos

hi,

can any one tell me the difference between inner join and outer join.

if your having any notes related to this problem ,please give me.

thank you

7 REPLIES 7

Former Member
0 Kudos

hi

In an inner join, a line from the left-hand database table or join is only included in the selection if there is one or more lines in the right-hand database table that meet the ON condition <cond>. The left outer join, on the other hand, reads lines from the left-hand database table or join even if there is no corresponding line in the right-hand table.

Regards

Aditya

Former Member
0 Kudos

Hi,

Inner join basically results in when there is atleast one field common in two or more tables which we want to relate.

On the other hand, if there is no any field common in two tables to relate, then we use outer join to relate two tables.

Former Member
0 Kudos

To keep it simple, let me keep it this way: Considering two table A and B and internal table ITAB: Inner Join: It requires an entry with KEY of A in B, to be extracted to internal table. Outer Join: Irrespective of whether an entry exists or not in B it still extracts data from A. Eg:

Table: A
--------

Fld1	Fld2
------------
X	100
Y	200
Z	300

Table: B
--------

Fld1	Fld3
------------
X	abc
X	def
Z	pqr		" No entry for Key Y in table B so it is not extracted


ITAB fields: FLD1, FLD2, FLD3

Inner Join:
-----------

FLD1	FLD2	FLD3
--------------------
X	100	abc
X	100	def
Z	300	pqr

Outer Join:
-----------

FLD1	FLD2	FLD3
--------------------
X	100	abc
X	100	def
Y	200		" No entry for Key Y in table B so FLD3 is blank
Z	300	pqr

Former Member
0 Kudos

Hi,

Joins are used to fetch data fast from Database tables:

Tables are joined with the proper key fields to fetch the data properly.

If there are no proper key fields between tables don't use Joins;

Important thing is that don't USE JOINS FOR CLUSTER tableslike BSEG and KONV.

Only use for Transparenmt tables.

You can also use joins for the database VIews to fetch the data.

JOINS

... FROM tabref1 INNER JOIN tabref2 ON cond

Effect

The data is to be selected from transparent database tables and/or views determined by tabref1 and tabref2. tabref1 and tabref2 each have the same form as in variant 1 or are themselves Join expressions. The keyword INNER does not have to be specified. The database tables or views determined by tabref1 and tabref2 must be recognized by 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 the process of standardization (see relational databases). To regroup this information into a database query, you can link tables using the join command. This formulates conditions for the columns in the tables involved. The inner join contains all combinations of lines from the database table determined by tabref1 with lines from the table determined by tabref2, whose values together meet the logical condition (join condition) specified using ON>cond.

Inner join between table 1 and table 2, where column D in both tables in the join condition is set the same:

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 September 10th and 20th, 2001 that are not sold out:

DATA: DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.

SELECT FCARRID FCONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON FCARRID = PCARRID AND

FCONNID = PCONNID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

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

AND FSEATSOCC < FSEATSMAX.

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 FCARRID FCONNID F~FLDATE

INTO (CARRID, CONNID, DATE)

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON FCARRID = PCARRID

WHERE FCONNID = PCONNID

AND P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

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

AND FSEATSOCC < FSEATSMAX.

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

-


-


-


-


-


-


-


-


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 SCUSTOMNAME SCUSTOMPOSTCODE SCUSTOM~CITY

SBOOKFLDATE SBOOKCARRID SBOOKCONNID SBOOKBOOKID

INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

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

BOOKING-BOOKID)

FROM SCUSTOM LEFT OUTER JOIN SBOOK

ON SCUSTOMID = SBOOKCUSTOMID AND

SBOOK~FLDATE = '20011015'

ORDER BY SCUSTOMNAME SBOOKFLDATE.

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

Note

In some cases, '*' may be specivied as the field list in the SELECT clause, and an internal table or work area is entered in 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 llen in der FROM clause, according to the structure of each table work area. There can be gaps between the 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, as in the following example (not simply by counting the total number of fields).

Example

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

DATA: BEGIN OF WA,

FLIGHT TYPE SFLIGHT,

PFLI TYPE SPFLI,

CARR TYPE SCARR,

END OF WA.

SELECT * INTO WA

FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P

ON FCARRID = PCARRID AND

FCONNID = PCONNID )

INNER JOIN SCARR AS C

ON FCARRID = CCARRID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

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

AND FSEATSOCC < FSEATSMAX.

WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID,

WA-FLIGHT-CONNID.

ENDSELECT.

Reward Points if found helpfull..

Cheers,

Chandra Sekhar.

Former Member
0 Kudos

Hi naveen,

Inner Joins:++ The typical join operation, which uses some comparison operator like = or <>

++ These include equi-joins and natural joins

++ Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table.

++ For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

++ Inner joins return rows only when there is at least one row from both tables that matches the join condition.

++ Inner joins eliminate the rows that do not match with a row from the other table

Outer Joins:

++ Outer joins can be a left, a right, or full outer join

++ Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.

++ All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join

++ All rows from both tables are returned in a full outer join

I think this information helps u to come out of confusion,

Plz rewards points,

Thanks & regards,

Ganesh.

0 Kudos

Hi,

please read the online documentation on FROM clause of SELECT statement. You can be assured that this is correct.

Regards,

Clemens

Former Member
0 Kudos

Hi,

Inner join will fetch all the common values.

P( A INTERSECT B )

say there are two tables named EKKO,EKPO

The structure of the tables are.

EKKO EKPO

0001 0001 A

0002 0001 B

0003 0002 C

The result will be

0001 A

0001 B

0002 C

Because they are common.

Whereas in Outer join all the relevant record will be fetched as follows.

P(AUB)

0001 A

0001 B

0002 C

0003

Reward points if useful.

Regards,

Sankar.