|NULLs and JOINs|
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:
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:
If we performed the following query:
We'd get the following result set:
Similarly, the query:
Would give us:
And, finally, the query:
And that's a look at how NULL values are treated by the JOIN operation!