Outer Joins

1. Introduction

There are instances when we do not only want to look at data that matches between two or more tables but also data that does not match to be returned. We can look for data that does not exist in the left table, does not exist in the right table, or a case of having data that does not match. In this tutorial, we will focus on data that does not match in either table.

2. Example

Let us come back to our initial dataset with the representative and department tables to help illustrate the outer joins:

CREATE TABLE representative ( representative_id INT PRIMARY KEY, first_name VARCHAR (30) NOT NULL, last_name VARCHAR (30) NOT NULL );

CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR (100) NOT NULL, manager_id INT, constraint fk_manager FOREIGN KEY (manager_id) REFERENCES representative (representative_id) );

INSERT INTO representative (representative_id, first_name, last_name)
VALUES (1, 'Bob','Evans'), (2, 'Tango','Rushmore'), (3, 'Danika','Arkane'), (4, 'Mac','Anderson');

INSERT INTO department (department_id, department_name, manager_id)<br>
VALUES (1, 'Sales', 1), (2, 'Marketing', 3), (3, 'IT', 4), (4, 'Finance', null), (5, 'Support', null);

There are multiple types of outer joins including the full outer join and the left and right outer joins that we will discuss in the upcoming tutorials.

The full outer join or full join would return the data from tall rows in both the left and right tables. If they match, it will return data from both sides. If there is not a match, the columns of the table will be filled with NULL.

Let us first look at the structure of the statement:

SELECT <columnlist> 
FROM <table1>
FULL OUTER JOIN <table2> ON <table1>.<table1column1> = <table2>.<table2column1>;

Notice that this is very similar to a regular JOIN ON statement with only the FULL OUTER added to the JOIN. Using our two tables, we can run the full outer join like:

SELECT * 
FROM representative
FULL OUTER JOIN department ON representative.representative_id = department.manager_id;

This will return the three records that match the representative_id from the representative table and the department_id from the department table. It will also return the rows from the department table that do not have a matching row in the representative table (4th and 5th row in the result set). It will also return the rows from the representative table that do not have a match in the department table (6th row):

This is an important point. If every row in both tables matches one another, the full outer join would have the same result set.

The following Venn diagram shows what the data would return:

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.