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

inner join vs outer join

Former Member — Fri, 28 Mar 2008 11:43:46 GMT

respected sir,

may i know what is inner join and outer join

what diff b/w these two

thank u

Re: inner join vs outer join

Former Member — Fri, 28 Mar 2008 11:45:42 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.

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.

Use this link also

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

Regards,

Brown.

Edited by: Brown on Mar 28, 2008 12:47 PM

Re: inner join vs outer join

Former Member — Fri, 28 Mar 2008 11:48:20 GMT

Hi,

The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

Regards,

Bhaskar

Re: inner join vs outer join

Former Member — Fri, 28 Mar 2008 11:50:48 GMT

Hi,

When you wish to get data from two related tables, you can use an inner join or an outer join to define how the data is related.

For my examples, let's assume where is a table called "student" to hold student information, a table called "attendance" to hold student's attendance, and a table called StudentSchedule that holds a student's current schedule:

Student has the following fields:

FirstName

LastName

BirthDate

Attendance has the following fields:

AttendanceDate

AttendanceCode

MinutesAbsent

StudentSchedule as the following fields:

Course

An inner join gets data from both tables where the specified data exists in both tables. For example, if you wanted a list of students in your database that were absent on December 4, 2003, you would use an inner join between the two examples tables "Student" and "Attendance":

SELECT Student.ID, Student.FirstName, Student.LastName,

Attendance.AttendanceCode,

Attendance.MinutesAbsent FROM Student INNER JOIN Attendance

ON Student.ID=Attendance.ID

WHERE Attendance.AttendanceDate=' 12/4/2003'

The above statement will only return students with attendance information on the specified date. Students who do not have attendance would not display.

Like so:

ID FirstName LastName AttendanceCode MinutesAbsent

10 Steve Bartman Tardy 22

32 Dale Thropmorton ExcAbsent 200

(maybe there are 200 kids in the database, but only Steve and Dale where absent on 12/4/2003. They are the only students to display)

An outer join gets data from the source table at all times, and returns data from the outer joined table ONLY if it matches the criteria. You would use this type of join using my examples tables if you wanted a list of all students in a specified course, and you wanted attendance information if it existed. You would use an inner join between Student and StudentSchedule to only get the students in the speicified course (for example 'ENGLISH 9'), but you would use an outer join against Attendance because you want ALL students in the course, not just students with attendance information on 12/4/2003.

When using outer joins, fields will be set to NULL if data does not exist in the outer-joined table.

SELECT Student.ID, Student.FirstName, Student.LastName,

Attendance.AttendanceCode,

Attendance.MinutesAbsent

FROM Student

INNER JOIN StudentSchedule ON

StudentSchedule.ID=Student.ID

LEFT OUTER JOIN Attendance ON

Student.ID=Attendance.ID AND

Attendance.AttendanceDate=' 12/4/2003'

WHERE StudentSchedule.Course='E NGLISH 9'

ID FirstName LastName AttendanceCode MinutesAbsent

10 Steve Bartman Tardy 22

32 Dale Thropmorton ExcAbsent 200

44 Jennifer Lopez NULL NULL

(Steve, Dale, and Jennifer all all in English. Steve and Dale were absent but Jennifer was not)

Notice how the AttendanceDate filter is in the ON clause instead of the WHERE clause. This is because joins are processed first and then filter information is applied afterwords. If "Attendance.AttendanceDat e='12/4/2003'" was put into the WHERE clause of the statement, the outer join would basically turn back into an inner join.

rewards if helpful.

Regards

Sourabh verma

Re: inner join vs outer join

Former Member — Fri, 28 Mar 2008 11:53:11 GMT

Check these to know the difference..

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

Regards,

Santosh

Re: inner join vs outer join

prasanth_kasturi — Fri, 28 Mar 2008 11:55:58 GMT

The set of hits determined by an inner join can therefore be a subset of the hits determined with an outer join.

refer to below link

http://help.sap.com/saphelp_nw70/helpdata/en/cf/21ec77446011d189700000e8322d00/content.htm

The Main Didfference is that with inner join we get records common in the tables.(like intersection)

and with outer join we get all the records (jst like union)

Reward if helpful

Prasanth

Re: inner join vs outer join

Former Member — Fri, 28 Mar 2008 11:57:45 GMT

Hi,

for example below are the 2 tables and their values.

A B

1000 1000 A

2000 1000 B

3000 3000 C

inner join will fetch the common records as like follows.

1000 A

1000 B

3000 C

If it is the case of outer join the output will be

1000 A

1000 B

2000

3000 C

Regards,

Sanki.