Differences between Inner join and outer join

[Former Member]

Please any one tell what are the differences between Inner join and Outer join plz tell me

points will be rewarded

[Former Member]

Hi Prasad,

The inner join is like the intersection, meaning that all conditions have

to be true for a record to be included. All conditions are and'ed

together. The outer join is the union, where the records are or'ed

together. I'm not sure about the extractor you are creating. Is it a

generic data extractor? If so, you have the option of using a view which

would be created in the data dictionary, and in which case you can't add

logic like you have in your code. I would not attempt to put all 12

selects into a single query using joins, I would pull data into internal

tables using array fetches (use for all entries clause), loop through the

internal tables and write out an output table. Trying to join too many

tables over complicates the issue in my opinion.

-

-

---

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. The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

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.

-

-

---

SQL Syntax for the Joins.

INNER JOIN: Retrieves customers with orders only. For example, you want to determine the amount ordered by each customer and you only want to see those who have ordered something

SELECT Customers., Orders.

FROM Customers INNER JOIN Orders ON Customers.CustomerID =

Orders.CustomerID

-

-

---

LEFT OUTER JOIN: Retrieves all customers with or without orders. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN if you switch the side of each table.

SELECT Customers., Orders.

FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =

Orders.CustomerID

-

-

---

RIGHT OUTER JOIN: Retrieves all orders with or without matching customer records. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

SELECT Customers., Orders.

FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =

Orders.CustomerID

Here i am giving you the syntax. Just go through this once.

inner join :

select P_{<fieldname> d}.

outer join:

select P_{<fieldname> d}.

-

-

---

I hope this is helpful to you, if so please reward me.

Thanks,

Thishya

[Former Member]

Hi Durga Prasad,

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 it will help you.

If satisfied rewards me..

Kind Regards

Yogesh Gupta

[Former Member]

Hai Prasad,

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.

The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

Regards.

Eshwar.

[Former Member]

hi

i will just tell u very basic difference between inner join and outer join..

in inner join only those entries are shown which are in both the tables.. like if join is on field 'name'. then only those entries will be shown in join table which are in both the tables..

where as in outer join all the entries of both the tables will be shown.. like if there in some X value in table A and table B doesnt have X then also it will be shown in join table.. the only thing will be values corresponding to y table will bne null at that time..

i hope it helped

give rewards if helpful

take care

palak

[Former Member]

Hi,,

Inner Join returns only those rows which hv matching rows from both the table,

while

Outer Join returns all rows from atleast one table

Regards

Sandipan

[Former Member]

Hi Durga,

Picked from ABAP Keyword Documentation

Variant 2

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

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

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

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 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 '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

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

WA-FLIGHT-CONNID.

ENDSELECT.

Hope this helps a bit...

Reward if useful..

Cheers

Kripa Rangachari.

[Former Member]

Hi

You use INNER JOIN to return all rows from both tables where there is a match. ie. in the resulting table all the rows and colums will have values.

In OUTER JOIN the relulting table may have empty colums. Outer join may be either LEFT or RIGHT

LEFT OUTER JOIN returns all the rows from the first table, even if there are no matches in the second table.

RIGHT OUTER JOIN returns all the rows from the second table, even if there are no matches in the first table.

Inner join:

We use this when we compare two colums from two different table .Based on equality or non equality, we retrieve the rows matched.

eg.

Select emp.empid , order.orderid

from emp Innerjoin order

on Emp.empid=order.empid

This example gives all the rows from emp,order tables where the empid's in both the tables are same.

Outer Join:

There are three types of outer joins namely:

Left Outer Join---For retreiving all the columns from the first table irrespective of the column match.

Right Outer Join---For retreiving all the columns from the second table irrespective of the column match

Full Outer Join---For retreiving all the columns from both the tables irrespective of column match.

Eg.

If we have two tables named stud1,stud2 with the following data

Stud1: id Name

1 xxx

2 yyy

3 zzz

4 www

stud2 : id name

1 aaa

2 bbb

3 ccc

4 ddd When we use Left Outer Join we get the output as:

1 aaa

2 bbb

3 <Null>

4 ccc

When we use Right Outer Join we get the output as:

1 aaa

2 bbb

4 ccc

<Null> ddd

When we use Full Outer Join we get the output as:

1 aaa

2 bbb

3 <Null>

4 ccc

<Null> ddd

Edited by: Jyothsna M on Mar 25, 2008 4:16 AM

[Former Member]

You use INNER JOIN to return all rows from both tables where there is a match. ie. in the resulting table all the rows and colums will have values.

In OUTER JOIN the relulting table may have empty colums. Outer join may be either LEFT or RIGHT

LEFT OUTER JOIN returns all the rows from the first table, even if there are no matches in the second table.

RIGHT OUTER JOIN returns all the rows from the second table, even if there are no matches in the first table.

Outer Join:

There are three types of outer joins namely:

Left Outer Join---For retreiving all the columns from the first table irrespective of the column match.

Right Outer Join---For retreiving all the columns from the second table irrespective of the column match

Full Outer Join---For retreiving all the columns from both the tables irrespective of column match.