How to deal with null values in joins?

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!