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
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
LEFT OUTER JOINreturns 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 JOINreturns 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 JOINreturns 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.
shirt” table only has one field, “
pants” table also has one field, “
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 —
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
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 “
color_shirt” field and those in the “
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
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 “
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 Vertabelo Academy’s SQL Basics course.