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