What Is Inner and Left Outer Join

by: Pavanpraveen

First know about what is inner join

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. 

Get help for your ABAP problems
ABAP Forum - Do you have a ABAP Question?

ABAP Programming Books
ABAP Books - Certification, BAPI, Java, Web Programming, Smart Forms, Sapscripts Reference Books

More ABAP Functions Tips
ABAP Functions Examples

ABAP Tips
ABAP Forum for Discussion and Samples Program Codes for Abapers

Best regards,
SAP Basis, ABAP Programming and Other IMG Stuff
http://www.erpgreat.com

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.  The site www.erpgreat.com is in no way affiliated with SAP AG.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.