What is a MySQL Join?

introduction to mysql

Now that we have covered all of the basics in MySQL, we’ll finish things up with a few additional points about MySQL that you are likely to come across. One of these topics is the idea of a join. So what is a join? When we do a join in MySQL, what we are doing is in essence, joining two tables together so that you can select data from both tables. This is possible when a column from the first table also contains a column with the same name in the second table. The easiest way to understand this is to see it in action, so lets jump right in using the friends and houses tables that we’ve been dealing with for the last several episodes.

Inner Join

There are many different types of joins, however the inner join is the most common one we’ll see. In all of our queries so far, we’ve been querying the friends or houses tables independently. Let’s refresh our memory of the structure of these tables.

Verrrrry Interesting… Do you notice the column that is present in both tables? That’s right, it’s the house_id that exists in both places. This is going to set us up for some join queries. So why do they call it an inner join? Observe.

inner join

The circle on the left is the friends table and the circle on the right is the houses table. This visualization shows that they both share the house_id column, and since it is in the middle of the diagram, it is called an inner join. Now let’s practice some queries using the inner join. There are a few things to be aware of, so we’ll complete several queries and then discuss. In addition, we’ll add a few more friends to our friends table. These new friends, Heather and Sandy, are free spirits. They travel constantly and have no permanent home address. Therefore, their house id is going to be null. Keep this in mind when we get to outer joins later on in this episode. In fact, let’s just add them to the database now so we don’t forget:

Inner Join Using

First up, we’ll do a simple query to test out the inner join. There are a couple of approaches with regard to the syntax you can use. In this first example, we’re going to make use of the using keyword.

Notice in the first line of the MySQL above that we are selecting columns from both the friends table and the houses table. This is what the inner join allows us to do. So if we were to read the query in plain English, it might sound like “Give me the first name, last name, and address of my friends from the friends and houses tables and use the house id as the common field”

Inner Join On

We’ll now do a query that will get us the same exact results, but using a different syntax. This one will use the on syntax.

Notice that we get the same results, yet our query looked a bit different. Again, we can read it in plain English to better understand it. The query might sound something like, “Give me the first name, last name, and address of my friends from the friends and houses tables on the condition that the house id in the friends table is equal to the house id in the houses table.”

Inner is Optional with Inner Join

In MySQL, specifying the inner portion of inner join is actually optional. When you write a join statement in MySQL, if you do not specify which type of join it is, MySQL assumes that you will be using an inner join. Let’s test it out to prove our theory:

Excellent! As we can see, this works just fine if you prefer to save yourself a couple of keystrokes.

Using Alias on Tables to Shorten Join Queries

Sometimes you may have tables that are quite long. If you’ve ever seen just a single letter in a MySQL query, especially in a dot style syntax and you weren’t quite sure what it was – chances are what you were looking at was some type of Alias in use. Let’s shorten our table names by using an alias so that they use just one letter. In this case, f will represent our friends table and h will represent the houses table.

Getting All Data From Both Tables

Most times when you see the inner join out in the wild, specific columns will be specified in the first part of the query. It is possible however to get all columns from both tables in one swoop if that is what you want to do. Let’s test this out using the asterisk * in our select statement to see how it works. We can terminate the MySQL statement with a backslash capital G for a nicer format as well.

Outer Join

Now comes the outer join. It’s best to understand the inner join before you tackle the outer, so re read the section above if you need to cement the idea in a little more. With the outer join, we can have left joins, or right joins.

Left Join

The left join looks like this from a visual perspective. What this is trying to convey is that we will display all records from the friends table and match with anything in the houses table that matches. If there are no matches, we still want to see the records from the friends table. This is the key point, it is less restrictive than the inner join.
left join

Awesome! Notice that our free spirited and homeless friends Heather and Sandy are now presented in the results. They were no where to be found when we were doing inner joins in the prior section!

Right Join

For every action, there is an equal and opposite reaction. In this case, for every left there is also a right. Just as we have a left join, there are also right joins. The visual for this guy looks like the following. In this case, it is the right side that takes precedence. In reality, these queries actually do pretty much the same thing. There are edge cases, but mostly they are interchangeable.

right join

The syntax gets switched around a bit, and looks like this:

Filtering Outer Joins

You might be wondering the use case for outer joins. Outer joins can be a bit more useful when we combine them with a where clause for filtering. Maybe we only want to see our world travelling homeless friends, since they seem the most interesting. We can do that like so:


This is not only the conclusion of this episode, but the conclusion of our ten part series on working with MySQL. It has been a lot of fun, and hopefully you picked up a nugget or two of useful MySQL information along the way.