topic Re: outer join and inner join in Application Development and Automation Discussions

outer join and inner join

Former Member — Thu, 19 Jun 2008 15:43:40 GMT

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

Re: outer join and inner join

vinod_vemuru2 — Thu, 19 Jun 2008 16:10:07 GMT

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

Re: outer join and inner join

Former Member — Thu, 19 Jun 2008 16:30:03 GMT

Hi,

Here the sample code.

data: begin of t_mara occurs 0,

matnr type matnr,

maktx type maktx,

end of t_mara.

select a_{matnr b}maktx

into table t_mara

from ( mara as a inner join makt as b

on a_{matnr = b}matnr ) up to 100 rows

where b~spras = 'E'.

loop at t_mara.

write:/ t_mara-matnr, t_mara-maktx.

endloop.

select a_{matnr b}maktx

into table t_mara

from ( mara as a left outer join makt as b

on a_{matnr = b}matnr ) 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

Re: outer join and inner join

Former Member — Fri, 20 Jun 2008 01:26:30 GMT

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

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

pls reward if helpful.

Re: outer join and inner join

Former Member — Fri, 20 Jun 2008 07:44:53 GMT

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

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

Example of : Left outer join:

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

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.

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

Best Regards,

Saurabh

Re: outer join and inner join

Former Member — Fri, 20 Jun 2008 09:27:47 GMT

thanks alot