SQL: Outer Joins

#sql #programming #postgres #database

In our second part of learning how to work with the SQL Join clause, we will focus on the Outer Joins. The first part, where we talk about Inner Joins, can be found here.

So, what is an Outer Join? Just like an Inner Join, an Outer Join is used to query databases combining tables based on a related column. Different from an Inner Join, an Outer Join could be used to return data that is not common among tables.

There are various types of Outer Joins, and we will explain them based on examples. For said examples, let's consider the same two tables of our previous post:

Full Outer Join: This version of the Join clause will return all records from both tables, adding null where a match isn't present. In the next image we use the Venn Diagram to graphically explain the result of joining these two tables with the Full Outer Join clause, or simply called Full Join.

Let's fetch all records from out pets' names, the doctors who have seen them, and the date they were seen, implementing an Outer Join. The syntax would look like the following:

Since Full Joins are not supported on MySQL, here's the syntax to be used in this DBMS:

The results would be the following:

As you can see, we have two rows for our cat Daisy, who was seen by two different doctors and/or on two different dates. Our fish Jules has not been seen, that's why it doesn't return any information about a doctor or date, and these fields are filled with null.

Left Outer Join: This Join, also known as Left Join, returns all records from the first table (or the left table) with the matches from the second table (or right table), adding null where no matches are found.

If we want to see which species have been seen by which doctors, we can query our database with a Left Join to return these results.

We see all records from our pets table with it's corresponding matches from the checkups table, and blank spaces for those records from pets without a match in checkups. Remember, these blank spaces are to be considered as null.

The Right Outer Join works the same as the Left Join, but inverting the tables: it will return all records of the right or second table, with matches from the left or first table, and null values where no match is found. If we were to replace the previous query with a Right Join, it would return the same results, except for the last row, because the null value corresponds to the checkups table.

Left Outer Join with WHERE clause: Remember when we mentioned at the beginning of this post that one difference between the Inner Join and the Outer Join is that the last one allows us to return values that are not matches between both tables? This can be achieved by adding a WHERE statement to our query.

So let's say we want to see which of our pets has not had a checkup. We know, from our previous examples, that for our fish Jules we received null values when trying to return similar data. We could use this value to comform our new query, so that the result will show that Jules has not been checked:

Pretty cool, huh? And the same goes for the Right Outer Join with a WHERE clause.

Full Outer Join with WHERE clause: This is the type of Join that allows us to return the records unique to both tables. In other words, we would be getting everything but the matches.

We select the columns of our interest from the pets table, Full Outer Joining checkups on the column they both have in common, WHERE these column's records are null, meaning where they don't have a value.

Again, MySQL does not support Full Joins, so the query would be:

And our results:

Our tiny example only includes one record that is unique to one of the tables, the pets table to be exact, so this is the reason for our result.

Outer Joins are a powerful tool at our disposal, and hopefully these lines brought some clarity on the matter.

Top comments (10)

blackr1234 • Dec 2 '19 • Edited

Great article. However, in the full outer join example, you should give a better description of what a full outer join will do in the context of your vet example.

Let's say we want to know which doctor checked our pets, and the date they were seen.

I would not use full outer join but a left join instead.

Wendy Calderon • Dec 2 '19

Thank you for your feedback! :)
This specific example could have better wording, or description as you mentioned. I will revise it tomorrow and edit accordingly.