In our last article, we introduced the concept of the NULL (or “unknown”) value and described how SQL treats this value when encountered in logical operations such as AND and OR.  In this article, we take a look at how NULL values impact a more complicated operation – the JOINing of two tables.

Let's begin by taking a look at two sample tables that comprise the course database for a school.  We might first have a list of courses and instructors, as shown below.  (Note that, for simplicity's sake, we are making the assumption that there is only one section of each course and, therefore, that we can use it as the primary key.)  In this case, the instructor Adams has not yet been assigned to a course and the Computers course has no instructor assigned yet.

Now, let's assume that we have a table containing course registrations for students.  Again, for simplicity's sake, we'll assume that each student must take one and only one course.  Every student must take a class, so we will include a NULL record for any student whose course registration information is unknown.  In this case, we're not sure what course Alan is taking.

If we perform an INNER JOIN, NULL values do not match each other.  For example, if we perform the following query:

SELECT *
FROM Instructors INNER JOIN Students
ON Instructors.Course = Students.Course

We'd get the following result:

The OUTER JOIN operations includes data from one or both tables that doesn't match data in the other table.  There are three types of OUTER JOINs:

• The LEFT OUTER JOIN includes rows from the table specified on the left side of the JOIN statement that don't match with rows from the table on the right side of the JOIN statement.
• The RIGHT OUTER JOIN includes rows from the table specified on the right side of the JOIN statement that don't match with rows from the table on the left side of the JOIN statement.
• The FULL OUTER JOIN includes rows from both tables that don't match data in the other table.

If we performed the following query:

SELECT *
FROM Instructors LEFT OUTER JOIN Students
ON Instructors.Course = Students.Course

We'd get the following result set:

Similarly, the query:

SELECT *
FROM Instructors RIGHT OUTER JOIN Students
ON Instructors.Course = Students.Course

Would give us:

And, finally, the query:

SELECT *
FROM Instructors FULL OUTER JOIN Students
ON Instructors.Course = Students.Course

Would yield:

And that's a look at how NULL values are treated by the JOIN operation!