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.
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
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
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:
Outer Joins are a powerful tool at our disposal, and hopefully these lines brought some clarity on the matter.