1. Technology
Send to a Friend via Email
NULLs and JOINs
 Related Resources
• SQL Fundamentals
• Starting a Career in Databases
• Certification Resources

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.

Course Instructor
Math Smith
Science Jones
NULL Adams
Computers NULL

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.

Student Course
Ryan Math
Betty Science
Alan NULL

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:

Course Instructor Student
Math Smith Ryan
Science Jones Betty

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:

Course Instructor Student
Math Smith Ryan
Science Jones Betty
NULL Adams NULL
Computers NULL NULL

Similarly, the query:

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

Would give us:

Course Instructor Student
Math Smith Ryan
Science Jones Betty
NULL NULL Alan

And, finally, the query:

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

Would yield:

Course Instructor Student
Math Smith Ryan
Science Jones Betty
Computers NULL NULL
NULL NULL Alan
NULL Adams NULL

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

You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

©2014 About.com. All rights reserved.