Missing data when joining tables in a query?

Question

I'm brand new to Access, so not sure if I'm even asking the right question.

I have one Table, tblStudents, that has many rows of students with columns such as student ID, student name, student mentor, etc. Another table, tblMentors, is joined to tblStudents that table via the Mentor ID column.

When I drag tblStudents into the query design window and make a quick query with just their names and IDs, it returns 1930 students. However, as soon as I drag tblMentors into the design window, without changing anything else about the query, only 1116 students show up. This is remains true when I "reverse the join" by draging tblMentors into the design window before tblStudents. I assume this is happening because many students do not have a mentor, and thus the cell in their mentor ID column is blank--I think all of these students are just being excluded from the query. But I want to include all the students.

Is there any way for me to fix this by adding something like "includeNullValues = TRUE" to the SQL? Or is it possible to fix it by applying Nz() to all of the tables referenced by the query? How?

SQL that returns 1930 students:

SELECT tblStudents.StudentID, tblStudents.LastName, tblStudents.FirstName, tblStudents.Class
FROM tblStudents;

SQL that returns 1116 students:

SELECT tblStudents.StudentID, tblStudents.LastName, tblStudents.FirstName, tblStudents.Class
FROM tblMentors INNER JOIN tblStudents ON tblMentors.MentorID = tblStudents.MentorID;

SQL that returns 1116 students (with reversed join):

SELECT tblStudents.StudentID, tblStudents.LastName, tblStudents.FirstName, tblStudents.Class
FROM tblMentors INNER JOIN tblStudents ON tblMentors.MentorID = tblStudents.MentorID;

Answer 1

Using OUTER JOIN instead of INNER JOIN seems to be the answer!

Answer 2

Ok I've tried both

FROM tblStudents LEFT JOIN tblStudents ON tblStudents.MentorID=tblMentors.MentorID;

and

FROM tblStudents LEFT OUTER JOIN tblStudents ON tblStudents.MentorID=tblMentors.MentorID;

They both cause an error that says "Syntax error in JOIN operation"

Answer 3

Nevermind, I'm dumb

FROM tblStudents LEFT OUTER JOIN tblMentors ON tblStudents.MentorID=tblMentors.MentorID;

works

Answer 4

An outer join is effectively a union. It says to return all the results of the left and/or right tables and join those rows that are in both. Outer joins can be left, right or full. A full returns all rows of both tables (hence a union). A left outer returns all the rows in the left table + any matching rows in the right table joined with it. Unmatched rows on the right have null values. Right outer does the opposite. Hence you'd use an outer join if you want either left/right/all tables joining, when possible.

An inner join is a "real" join. Only the rows that are in both the left and right table are returned.

You can read more about database joins here.