topic Re: joins in Application Development and Automation Discussions

joins

Former Member — Mon, 27 Nov 2006 05:05:43 GMT

Hi,

Can any one explain me in detail what is the difference between inner join and outer join.

Re: joins

Former Member — Mon, 27 Nov 2006 05:07:46 GMT

You can find the difference here ...

http://help.sap.com/saphelp_nw04/helpdata/en/67/7e4b3eaf72561ee10000000a114084/content.htm

Regards,

Ravi

Note - Please mark all the helpful answers

Re: joins

Former Member — Mon, 27 Nov 2006 05:08:41 GMT

Hi,

For inner join the record should be there in both the tables for the ON condition...

For outer join the record in the second table need not be there for the ON condition..

Check this sap documentation...

In order to determine the result of a SELECT statement where the FROM clause contains a left outer join, the database system creates a temporary table containing the lines that meet the ON condition. The remaining fields from the left hand table (tabref1) are then added to this table, and their corresponding fields from the right hand table are filled with NULL values. The system then applies the WHERE condition to the table

Thanks

Naren

Re: joins

jayanthi_jayaraman — Mon, 27 Nov 2006 05:08:54 GMT

Hi,

If both table contains same key value,then only a record will be selected in inner join.But it is not like that for outer join.

Re: joins

Former Member — Mon, 27 Nov 2006 05:10:26 GMT

Hi Kan

To keep it simple,

Inner join requires entry with the key in both tables.

Outer join displays entry even a record in found in right hand table.

Eg:

Table1

Fld1 Fld2

A 1

B 2

C 3

Table2

Fld1 Fld3

A x

C z

Inner Join - Output:

Fld1 Fld2 Fld3

A 1 x

C 3 z

Outer Join - Output:

Fld1 Fld2 Fld3

A 1 x

B 2

C 3 z

As you can see, inner join has no record for B since no record exists for the key in Table 2 and Outer Join displays the record but with blank value for FLD3.

Hope this gives you some idea.

Kind Regards

Eswar

Re: joins

Former Member — Mon, 27 Nov 2006 05:13:06 GMT

Hi ,

The following is the difference between Inner join and Outer join in simple words.

This is an extraction from SAP help.

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

Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view. Help views and maintenance views, however, implement an outer join.

Re: joins

anversha_s — Mon, 27 Nov 2006 05:15:02 GMT

hi,

table emp

empno name

a sasi

b xxx

c yyy

table sal

empno salary

a 1000

b 2000

Inner join

****************

select e~empno e~name 
s~sal 
into table int_table
from emp as e
inner join sal
on 
e~empno = s~empno.

if you made inner join between table a and b by emp no

the selection retrives only if the condition satisfy the output will be

a sasi 1000

b xxx 2000

Outer join

*************************

select e~empno e~name 
s~sal into table  int_table
from emp as e
LEFT OUTER JOIN sal
on 
e~empno = s~empno.

if you made outer join (left /right ) the left table kept as it is the

if the condition satisfy the right table entries will fetch else leave it blank

the output will be

a sasi a 1000

b xxx b 2000

c yyy

rgds

Anver

<i>hope this helped</i>

Re: joins

Former Member — Mon, 27 Nov 2006 06:32:50 GMT

I think this link may help u

http://database.ittoolbox.com/documents/popular-q-and-a/inner-and-outer-join-sql-statements-2108

Re: joins

Former Member — Mon, 27 Nov 2006 08:26:29 GMT

The following example , code snippet and the result will give u a clear idea

SQL: Joins

A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.

There are different kinds of joins. Let's take a look at a few examples.

Inner Join (simple join)

Chances are, you've already written an SQL statement that uses an inner join. It is is the most common type of join. Inner joins return all rows from multiple tables where the join condition is met.

For example,

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers, orders

WHERE suppliers.supplier_id = orders.supplier_id;

This SQL statement would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let's look at some data to explain how inner joins work:

We have a table called suppliers with two fields (supplier_id and supplier_ name).

It contains the following data:

supplier_id supplier_name

10000 IBM

10001 Hewlett Packard

10002 Microsoft

10003 Nvidia

We have another table called orders with three fields (order_id, supplier_id, and order_date).

It contains the following data:

order_id supplier_id order_date

500125 10000 2003/05/12

500126 10001 2003/05/13

If we ran the SQL statement below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date

FROM suppliers, orders

WHERE suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id name order_date

10000 IBM 2003/05/12

10001 Hewlett Packard 2003/05/13

The rows for Microsoft and Nvidia from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables.

Outer Join

Another type of join is called an outer join. This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).

For example,

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date

from suppliers, orders

where suppliers.supplier_id = orders.supplier_id(+);

This SQL statement would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

The after the orders.supplier_id field indicates that, if a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null> in the result set.

The above SQL statement could also be written as follows:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date

from suppliers, orders

where orders.supplier_id(+) = suppliers.supplier_id

Let's look at some data to explain how outer joins work:

We have a table called suppliers with two fields (supplier_id and name).

It contains the following data:

supplier_id supplier_name

10000 IBM

10001 Hewlett Packard

10002 Microsoft

10003 Nvidia

We have a second table called orders with three fields (order_id, supplier_id, and order_date).

It contains the following data:

order_id supplier_id order_date

500125 10000 2003/05/12

500126 10001 2003/05/13

If we ran the SQL statement below:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date

from suppliers, orders

where suppliers.supplier_id = orders.supplier_id(+);

Our result set would look like this:

supplier_id supplier_name order_date

10000 IBM 2003/05/12

10001 Hewlett Packard 2003/05/13

10002 Microsoft <null>

10003 Nvidia <null>

Re: joins

Former Member — Thu, 14 Dec 2006 11:53:14 GMT

The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join.In a join, at least two of the tables must have the same column.

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

2)With an outer join, records are also selected for which there is no entry in some of the tables used in the view.

3)Database views implement an inner join. The database therefore only provides those records for which there is an entry in all the tables used in the view.

4) Help views and maintenance views, however, implement an outer join.

Regards,

Gunasree.

Re: joins

Former Member — Fri, 15 Dec 2006 10:43:39 GMT

Hello,

Records should be there in both the tables for the ON condition, in INNER JOIN.

Records in the second table need not be there for the ON condition,in OUTER JOIN.

Regards,

Shehryar Dahar