topic Re: Inner join and left outer join in Application Development and Automation Discussions

Inner join and left outer join

Former Member — Sun, 22 Feb 2009 08:55:56 GMT

Hello experts ,

I was create join between 7 tables in my query , how can i know when i must to use with inner join and when use with left outer join , and i want to know what is the different with inner join and left outer join generally ?

Thanks for your help ,

Avi.

Re: Inner join and left outer join

Former Member — Sun, 22 Feb 2009 09:02:27 GMT

Inner join is used to get all enteries fronm 2nd table which has it's corresponding in 1st table, means on which field condition inner join is made.

but suppose in ist table there is a field which has a value but in inner join table that field has no corresponding value than the field of ist table will also not be shown while selecting.means value of the fields from both table will not be picked.

but when u use left outer join it will pick the values from ist table too however it have no corresponding value in 2nd table.

in short.

The INNER JOIN keyword return rows when there is at least one match in both tables.
The LEFT Outer JOIN keyword returns all rows from the left table (table_name1), even if there are 
no matches in the right table (table_name2).

hope u got it

Edited by: tahir naqqash on Feb 22, 2009 2:06 PM

Re: Inner join and left outer join

Former Member — Sun, 22 Feb 2009 09:03:01 GMT

Hi,

Please [search|https://www.sdn.sap.com/irj/scn/advancedsearch?query=innerjoinandleftouter+join&cat=sdn_all] the SCN before the Posting. You can find the lot of thread's for your query.

Re: Inner join and left outer join

Former Member — Sun, 22 Feb 2009 09:09:21 GMT

sap help has all answers for such generic queries so make an habit of searching the answer urself

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm

кu03B1ятu03B9к

Re: Inner join and left outer join

Former Member — Sun, 22 Feb 2009 09:17:35 GMT

hi,

inner join produces a result set of only those records from the left(outer) table for which there is a corresponding entry in the right(inner) table.

left outer join produces a result set of all records from the left(outer) table irrespective of whether there is a corresponding entry in the right(inner) table.

use inner join when u want to combine two tables based on common field values.Else use left outer join.

tab1

f1 f2 f3

a1 b1 c1

a2 b2 c2

a3 b3 c3

tab1

f1 f2 f4

a1 b1 d1

a2 b2 d2

a4 b4 d4

SELECT s_{f1 s}f2 s_{f3 p}f4 INTO TABLE itab

FROM tab1 AS s INNER JOIN tab2 AS p

ON s_{f1 = p}f1 AND s_{f2 = p}f2.

result:(for f1=a3 and f2=b3 there is no entry in tab2 .....so that row is not included in itab)

itab

f1 f2 f3 f4

a1 b1 c1 d1

a2 b2 c2 d2

SELECT s_{f1 s}f2 s_{f3 p}f4 INTO TABLE itab

FROM tab1 AS s LEFT OUTER JOIN tab2 AS p

ON s_{f1 = p}f1 AND s_{f2 = p}f2.

result:(for f1=a3 and f2=b3 row is included in itab)

itab

f1 f2 f3 f4

a1 b1 c1 d1

a2 b2 c2 d2

a3 b3 c3

Regards

Jay