SQL: Nulls and Joins

In this article, we will examine how NULL’s in SQL can affect the JOINing of two tables. The two sample tables are listed below:

In the first table, instructor Holmes’ course is left NULL, and the English classes instructor is left NULL. In the second table, James’ course is also left as NULL.

Moving forward, if we perform an INNER JOIN, NULL values do not match each other. For example, when performing the query:

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

This gives you this table:

OUTER JOIN operations include data from one or both tables that don’t match data in the other table. The three types of OUTER JOINs are list here:

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

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

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