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:
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)
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.
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.
some critics about naming. Maybe use
doctorname
instead of confusinglastname
orlast_name
.Thanks for sharing good article! π
Thank you for the feedback! π
TIL MySQL doesnβt support full outer joins?! Whaaaat?
I know, hahaha...
But the good news is that there's always a work-around :)
I love a good outer join! Great article Wendy.
Great idea for a series too, when I first picked up SQL it took me forever to wrap my head around joins. Venn diagrams are the way!!
Thank you for your feedback James!
I'm glad you liked it!
Such a great explanation of the concept. Thanks.
Thank you for you feedback! :)