An Illustrated Guide to the SQL OUTER JOIN

We’ve already discussed the SQL CROSS JOIN and INNER JOIN statements. It’s time to explore another: OUTER JOIN. What is it? How does it work? Let’s find out!

If you’ve read our other posts, you know that you can link the data in two or more database tables using one of the many types of SQL join operator. Today, we’ll discuss the three kinds of OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. These join records based on matching row values, but they do it a bit differently than other join statements.

What Is an SQL OUTER JOIN?

To answer this question, we have to delve into the different types of OUTER JOIN:

• LEFT OUTER JOIN returns every record in the left table and all matching records from the right table. If there’s no match found, a NULL is shown next to the unmatched record.
• RIGHT OUTER JOIN returns every record in the right table and all matching records from the left table. If there’s no match found, a NULL is shown next to the unmatched record.
• FULL OUTER JOIN returns all records from both tables. All unmatched records are paired with NULLs.

Now let’s look at the tables we will use to illustrate these operators.

The “shirt” table only has one field, “color_shirt”:

The “pants” table also has one field, “color_pants”:

These tables are self-explanatory. Just imagine them as two parts of an outfit: the color of your pants and the color of your shirt. The idea is to find shirts and pants with identical colors.

Good to Know: The left table is the first table listed and is found after the FROM clause. The right table is the second table listed and is found after the JOIN clause. You can usually omit the OUTER keyword in any OUTER JOIN — FULL JOIN, LEFT JOIN, and RIGHT JOIN will work just as well in many databases (but check your documentation to be sure).

Using a LEFT OUTER JOIN

The LEFT OUTER JOIN retrieves all records from the first (left) table and matches them to records from the second (right) table. Any non-matching records from the left table are also selected, but with NULL values where the right table records would be.

Have a look at the example.

SELECT color_shirt, color_pants 
FROM shirt
LEFT JOIN pants ON color_shirt=color_pants;

The left table (after FROM) is “shirt” and the right table (after LEFT JOIN) is “pants”. The ON predicate states the condition for matching records from “shirt” with records from “pants”. This condition is that the values in the “shirt”.“color_shirt” field and those in the “pants”.“color_pants” field must match. If there is no match, records from the “shirt” table will be shown, but a NULL value is set where the matching “pants” record would be.

These are the results from this query:

And here is an illustration of how this query works and what the results look like:

As you see, the matching green and blue outfits are together. The yellow shirt has no pants because the “pants” table does not have any fields with a “yellow” value.

Using a RIGHT OUTER JOIN

The RIGHT OUTER JOIN works like the LEFT JOIN, but with one major difference: it selects all records from the right table (in this case, “pants”). The records from the left table (“shirt”) will only be shown if they match.

Look at the query:

SELECT color_shirt, color_pants 
FROM shirt	
RIGHT JOIN pants ON color_shirt=color_pants;

Here is the result:

And here is the illustration of using RIGHT JOIN and its results. All the pants are shown, but there is no matching shirt for the pink pair of pants.

Using a FULL OUTER JOIN

Let’s recap what we’ve done so far. With LEFT JOIN, all shirts and any matching pants were returned. With RIGHT JOIN, all pants and any matching shirts were returned. What will happen if you use a FULL OUTER JOIN? It shows all records from both tables. If possible, it will match the records; if not, a NULL will be shown where the matching record would be.

Let’s look an example query:

SELECT color_shirt, color_pants 
FROM shirt
FULL  JOIN pants ON color_shirt=color_pants;

Notice that in a FULL JOIN which table is left and which is right is negligible. The result will be the same.

Here is the result:

The result set contains all the records stored in the “shirt” table and in the “pants” table.

The picture shows that FULL JOIN returned all possible clothes: all shirts and all pants. The matching pairs (green and blue) are shown together, and the unmatched items (yellow shirt and pink pants) are shown separately.

Want to Learn More About OUTER JOINs?

There’s more to discover about using OUTER JOINS. To learn more, check out LearnSQL.com’s SQL Basics course.