DEV Community

loading...

SQL: Outer Joins

Wendy Calderon
#WomanInTech with a passion for exploring, learning and utilizing technology for the common good. Software Engineer at @followLantern
Updated on ・4 min read

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:
Alt Text
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.

Alt Text
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:

Alt Text

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

Alt Text

The results would be the following:

Alt Text

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.
Alt Text
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.

Alt Text

Alt Text

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.
Alt Text
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:

Alt Text

Alt Text

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.
Alt Text
Alt Text
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:

Alt Text
And our results:

Alt Text
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.

Discussion (10)

Collapse
blackr1234 profile image
blackr1234 • 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.

Collapse
wendisha profile image
Wendy Calderon Author

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.

Collapse
mandaputtra profile image
Manda Putra

some critics about naming. Maybe use doctorname instead of confusing lastname or last_name.

Thanks for sharing good article! 😄

Collapse
wendisha profile image
Wendy Calderon Author

Thank you for the feedback! 😊

Collapse
euankennedy profile image
euankennedy

TIL MySQL doesn’t support full outer joins?! Whaaaat?

Collapse
wendisha profile image
Wendy Calderon Author

I know, hahaha...
But the good news is that there's always a work-around :)

Collapse
jeastham1993 profile image
James Eastham

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!!

Collapse
wendisha profile image
Wendy Calderon Author

Thank you for your feedback James!
I'm glad you liked it!

Collapse
nlsanyu profile image
NLSanyu

Such a great explanation of the concept. Thanks.

Collapse
wendisha profile image
Wendy Calderon Author

Thank you for you feedback! :)