inner join vs outer join

[Former Member]

respected sir,

may i know what is inner join and outer join

what diff b/w these two

thank u

[Former Member]

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

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

[Former Member]

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.

Regards,

Bhaskar

[Former Member]

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:

ID

FirstName

LastName

BirthDate

Attendance has the following fields:

ID

AttendanceDate

AttendanceCode

MinutesAbsent

StudentSchedule as the following fields:

ID

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

[Former Member]

Check these to know the difference..

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

Regards,

Santosh

[prasanth_kasturi]

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.

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

[Former Member]

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.