What's the difference between an inner join and an outer join?

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='ENGLISH 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.AttendanceDate='12/4/2003'" was put into the WHERE clause of the statement, the outer join would basically turn back into an inner join.

(Editted 12/9/2003 for grammar and spelling. :))
(Editted 11/18/2006. Removed use of Fixed-width fonts because now it looks bad after the redesign of the website)

Thanks. Very Good. It Really helps, coz it is with an example.

You took this question right from a monster.com post!

Do you really thing ab is going to help you land that job ?