Difference between inner join and left outer joins?

[Former Member]

Hi all,

Can anyone explain me the difference between inner join and left outer join with an example.

And when shall we go for left outer join?

regds

haritha

[Former Member]

Hi Haritha

hope this will help you.

Pls reward if help.

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

[Former Member]

Hi Haritha,

Using Joins we can select data from two tables with the JOIN keyword, like this:

Example INNER JOIN

Syntax

SELECT field1, field2, field3

FROM first_table

INNER JOIN second_table

ON first_table.keyfield = second_table.foreign_keyfield

Who has ordered a product, and what did they order?

SELECT Employees.Name, Orders.Product

FROM Employees

INNER JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.

Result

Name Product

Hansen, Ola Printer

Svendson, Stephen Table

Svendson, Stephen Chair

Example LEFT JOIN

Syntax

SELECT field1, field2, field3

FROM first_table

LEFT JOIN second_table

ON first_table.keyfield = second_table.foreign_keyfield

List all employees, and their orders - if any.

SELECT Employees.Name, Orders.Product

FROM Employees

LEFT JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.

Result

Name Product

Hansen, Ola Printer

Svendson, Tove

Svendson, Stephen Table

Svendson, Stephen Chair

Pettersen, Kari

Example RIGHT JOIN

Syntax

SELECT field1, field2, field3

FROM first_table

RIGHT JOIN second_table

ON first_table.keyfield = second_table.foreign_keyfield

List all orders, and who has ordered - if any.

SELECT Employees.Name, Orders.Product

FROM Employees

RIGHT JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.

Result

Name Product

Hansen, Ola Printer

Svendson, Stephen Table

Svendson, Stephen Chair

Example

Who ordered a printer?

SELECT Employees.Name

FROM Employees

INNER JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID

WHERE Orders.Product = 'Printer'

Thankyou,

Regards.

[sallyxu-cn]

Simple and straight forward.

Thanks.

[Former Member]

hi,

difference between inner join and outer join is:

inner join means it selects same data only in both the tables

outer join means first it selects same data and second it selects different data from the both tables.

[Former Member]

Hi,

Joins are used to join 2 or more tables.

INNER JOIN: Records will be fetched based on the common data.

Ex: SELECT mara_{matnr marc}werks

FROM MARA

INNER JOIN MARC

ON mara-matnr = marc-matnr

Where mara-matnr = xxxxxx.

Data from MARA & MARC will be fetched only when matnr exist in both the tables. If data doesnt exist in one table, those records will not be fetched.

OUTTER JOIN: Even if data doesnt exist in the right most table, (i.e., table on which outer join is done,) left most table data will be considered.

Ex: SELECT mara_{matnr makt}maktx

FROM MARA

OUTER JOIN MAKT

ON mara~matnr = makt-matnr

WHERE mara~matnr = xxxxxx.

In this case, even if material description is not available, material number should be considered.

[Former Member]

hi haritha,

i want to tell you one thing i.e., the answer of this question is same in any database i.e., SAP or sql or oracle.

Now see the difference.

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 stud2: id Name

1 xxx 1 aaa

2 yyy 2 bbb

3 zzz 4 ccc

4 www 6 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

Based on the outputs it might be easy for u to understand the concept...

*Kindly reward me *

[Former Member]

HI

The join syntax represents a recursively nestable join expression. A join expression consists of a left-hand and a right- hand side, which are joined either by means of INNER JOIN or LEFT OUTER JOIN . Depending on the type of join, a join expression can be either an inner ( INNER) or an outer (LEFT OUTER) join. Every join expression can be enclosed in round brackets. If a join expression is used, the SELECT command circumvents SAP buffering.

On the left-hand side, either a single database table, a view dbtab_left, or a join expression join can be specified. On the right-hand side, a single database table or a view dbtab_right as well as join conditions join_cond can be specified after ON. In this way, a maximum of 24 join expressions that join 25 database tables or views with each other can be specified after FROM.

AS can be used to specify an alternative table name tabalias for each of the specified database table names or for every view. A database table or a view can occur multiple times within a join expression and, in this case, have various alternative names.

The syntax of the join conditions join_cond is the same as that of the sql_cond conditions after the addition WHERE, with the following differences:

At least one comparison must be specified after ON.

Individual comparisons may be joined using AND only.

All comparisons must contain a column in the database table or the view dbtab_right on the right-hand side as an operand.

The following language elements may not be used: BETWEEN, LIKE, IN.

No sub-queries may be used.

For outer joins, only equality comparisons (=, EQ) are possible.

If an outer join occurs after FROM, the join condition of every join expression must contain at least one comparison between columns on the left-hand and the right-hand side.

In outer joins, all comparisons that contain columns as operands in the database table or the view dbtab_right on the right-hand side must be specified in the corresponding join condition. In the WHERE condition of the same SELECT command, these columns are not allowed as operands.

Resulting set for inner join

The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

Resulting set for outer join

The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

Example

Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

SELECT c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

Example

Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

PARAMETERS p_cityfr TYPE spfli-cityfrom.

DATA: BEGIN OF wa,

carrid TYPE scarr-carrid,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH NON-UNIQUE KEY carrid.

SELECT s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid

AND p~cityfrom = p_cityfr.

LOOP AT itab INTO wa.

IF wa-connid = '0000'.

WRITE: / wa-carrid, wa-carrname.

ENDIF.

ENDLOOP.

we r using 2 type of joins in abap they are

1) inner join.

this will join 2 tables using an common fiend and return the result with field values wich are common in both the tables

itab1 itab2

a b c a d

1 2 3 1 5

2 3 4 3 6

after innerjoining itab1 n itab2 on field a we wil get the o/p as

a b c d

1 2 3 5

only common field is taken..

2)left outer join

here it wil work in opossite way it will give values whic are not common

itab1 itab2

a b c a d

1 2 3 1 5

2 3 4 3 6

after left outer joining itab1 n itab2 on field a we wil get the o/p as

a b c d

2 3 4

only fields which are not common is taken from the left table..other field(d here) wil be empty

I think it will help u.

Reward Points if helpful.

Check for ALVROBOT.COM also it is also helpful.