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: 

outer join and inner join

Former Member
0 Kudos

hi all

can anyone send me a simple stmt using outer join and inner join separately.

i need it at the earliest to use in my report.

full points for helpful answers

5 REPLIES 5

vinod_vemuru2
Active Contributor
0 Kudos

Hi Venkat,

Check below code for inner join.


     SELECT a~vbeln
             a~fkart     a~vbtyp a~vkorg
             a~erdat a~zuonr   b~posnr b~meins
             b~fklmg b~ntgew b~gewei b~matnr b~vkbur
             FROM vbrk AS a INNER JOIN vbrp AS b
             ON a~vbeln  EQ b~vbeln
             WHERE a~vbtyp NE lc_n
             AND   a~vbtyp NE lc_o
             AND   a~vbtyp NE lc_u  
             AND   b~erdat IN r_erdat
             AND   a~erdat IN r_erdat
             AND   b~aland EQ c_aland.

This will fetch all the invoices which satisfies the above WHERE clause conditions.

Here a means VBRK and b means VBRP.

Basically INNER JOIN will select matched records of the join tables.(i.e Intersection of the join tables)

Outer join has two types LEFT and RIGHT.

LEFT OUETR JOIN will fetch Left union and RIGHT OUTER JOIN will fetch RIGHT union.

eg: Left table is A and right table is B then LEFT OUTER JOIN will give ALL records that matches where clause for table A and matched records of B. Right OUTER JOIN will give all records of B which matches where claause for table B and matched records of A.

Hope it is clear.

Thanks,

Vinod.

Edited by: Vinod Reddy Vemuru on Jun 19, 2008 9:41 PM

former_member721730
Active Participant
0 Kudos

Hi,

Here the sample code.

data: begin of t_mara occurs 0,

matnr type matnr,

maktx type maktx,

end of t_mara.

select amatnr bmaktx

into table t_mara

from ( mara as a inner join makt as b

on amatnr = bmatnr ) up to 100 rows

where b~spras = 'E'.

loop at t_mara.

write:/ t_mara-matnr, t_mara-maktx.

endloop.

select amatnr bmaktx

into table t_mara

from ( mara as a left outer join makt as b

on amatnr = bmatnr ) up to 100 rows.

skip 3.

loop at t_mara.

write:/ t_mara-matnr, t_mara-maktx.

endloop.

in the left outer join should not be in the where condition.

I is very simple and can be used for other tables.

regards,

Saravanan V

Former Member
0 Kudos

hi,

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.

eg for inner join.

SELECT AEBELN ALIFNR AKNUMV BEBELP BNETWR BNETPR BWERKS BMATNR

LNAME1 LNAME2

FROM EKKO AS A

INNER JOIN EKPO AS B ON AEBELN = BEBELN

INNER JOIN LFA1 AS L ON LLIFNR = ALIFNR

  • INNER JOIN EKKN AS C ON CEBELN = AEBELN

INTO CORRESPONDING FIELDS OF TABLE itab

WHERE B~BUKRS = 'company code' .

eg for outer join

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 = '20081015'

ORDER BY SCUSTOMNAME SBOOKFLDATE.

WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

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

BOOKING-BOOKID.

ENDSELECT.

pls reward if helpful.

Former Member
0 Kudos

Hi,

Conceptually :

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.

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: Inner Join:

===============

SELECT ccarrname pconnid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON pcarrid = ccarrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON fcarrid = pcarrid

AND fconnid = pconnid ).

=================

Example of : Left outer join:

==================

SELECT scarrid scarrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON scarrid = pcarrid

AND p~cityfrom = p_cityfr.

==================

Best Regards,

Saurabh

Former Member
0 Kudos

thanks alot