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.
mysql> show tables; +---------------------+ | Tables_in_addresses | +---------------------+ | friends | | houses | +---------------------+ 2 rows in set (0.00 sec) mysql> describe friends; +------------+----------------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------------------+------+-----+------------+-------+ | first_name | varchar(40) | NO | PRI | | | | last_name | varchar(40) | NO | PRI | | | | cell_phone | varchar(20) | NO | | | | | birthday | date | NO | PRI | 0000-00-00 | | | house_id | smallint(5) unsigned | YES | | NULL | | +------------+----------------------+------+-----+------------+-------+ 5 rows in set (0.03 sec) mysql> describe houses; +----------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+----------------------+------+-----+---------+----------------+ | house_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | address | varchar(120) | NO | UNI | | | | home_telephone | varchar(25) | YES | | NULL | | +----------------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.06 sec)
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.
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:
mysql> insert into friends set first_name='Sandy', last_name='Sholes'; Query OK, 1 row affected (0.00 sec) mysql> insert into friends set first_name='Heather', last_name='Jeffries'; Query OK, 1 row affected (0.00 sec)
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.
mysql> select first_name, last_name, address from friends
-> inner join houses using (house_id);
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
10 rows in set (0.03 sec)
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.
mysql> select first_name, last_name, address from friends
-> inner join houses on friends.house_id = houses.house_id;
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
10 rows in set (0.00 sec)
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:
mysql> select first_name, last_name, address from friends
-> join houses using (house_id);
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
10 rows in set (0.03 sec)
mysql> select first_name, last_name, address from friends
-> join houses on friends.house_id = houses.house_id;
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
10 rows in set (0.00 sec)
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.
mysql> select first_name, last_name, address from friends as f
-> inner join houses as h on f.house_id = h.house_id;
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
10 rows in set (0.00 sec)
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.
mysql> select * from friends as f
-> inner join houses as h on f.house_id = h.house_idG
*************************** 1. row ***************************
first_name: Jim
last_name: Jones
cell_phone: 423-1423
birthday: 1985-07-24
house_id: 1
house_id: 1
address: 54 Main Street, Conway NH
home_telephone: 153-3487
*************************** 2. row ***************************
first_name: Sofia
last_name: Vergara
cell_phone: 487-4612
birthday: 1972-07-10
house_id: 2
house_id: 2
address: 742 Applewild, San Francisco CA
home_telephone: 487-1235
*************************** 3. row ***************************
first_name: Emmet
last_name: Brickowski
cell_phone: 987-1244
birthday: 2013-07-15
house_id: 3
house_id: 3
address: 836 Shark Ave, Chatham MA
home_telephone: 876-4576
*************************** 4. row ***************************
first_name: Dave
last_name: Chappelle
cell_phone: 977-5555
birthday: 1973-08-24
house_id: 4
house_id: 4
address: 88 Bundy Drive, Stowe VT
home_telephone: 746-9487
*************************** 5. row ***************************
first_name: Brenda
last_name: Penblanc
cell_phone: 719-4521
birthday: 1970-02-15
house_id: 5
house_id: 5
address: 87 Highcrest St, Orlando FL
home_telephone: 198-4723
*************************** 6. row ***************************
first_name: Jack
last_name: Penblanc
cell_phone: 581-1841
birthday: 1967-04-23
house_id: 5
house_id: 5
address: 87 Highcrest St, Orlando FL
home_telephone: 198-4723
*************************** 7. row ***************************
first_name: Frankie
last_name: Farmington
cell_phone: 978-1235
birthday: 1977-04-17
house_id: 6
house_id: 6
address: 92 Buzzfeed Lane, Denver CO
home_telephone: 258-4723
*************************** 8. row ***************************
first_name: Sheila
last_name: Farmington
cell_phone: 591-8522
birthday: 1977-07-12
house_id: 6
house_id: 6
address: 92 Buzzfeed Lane, Denver CO
home_telephone: 258-4723
*************************** 9. row ***************************
first_name: Jacob
last_name: Olivio
cell_phone: 512-3411
birthday: 1972-10-01
house_id: 7
house_id: 7
address: 12 Dunpoto Street, Boston MA
home_telephone: NULL
*************************** 10. row ***************************
first_name: Mary
last_name: Olivio
cell_phone: 412-9823
birthday: 1972-11-11
house_id: 7
house_id: 7
address: 12 Dunpoto Street, Boston MA
home_telephone: NULL
10 rows in set (0.00 sec)
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.
mysql> select first_name, last_name, address from friends
-> left join houses using (house_id);
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Heather | Jeffries | NULL |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sandy | Sholes | NULL |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
12 rows in set (0.00 sec)
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.
The syntax gets switched around a bit, and looks like this:
mysql> select first_name, last_name, address from houses
-> right join friends using(house_id);
+------------+------------+---------------------------------+
| first_name | last_name | address |
+------------+------------+---------------------------------+
| Brenda | Penblanc | 87 Highcrest St, Orlando FL |
| Dave | Chappelle | 88 Bundy Drive, Stowe VT |
| Emmet | Brickowski | 836 Shark Ave, Chatham MA |
| Frankie | Farmington | 92 Buzzfeed Lane, Denver CO |
| Heather | Jeffries | NULL |
| Jack | Penblanc | 87 Highcrest St, Orlando FL |
| Jacob | Olivio | 12 Dunpoto Street, Boston MA |
| Jim | Jones | 54 Main Street, Conway NH |
| Mary | Olivio | 12 Dunpoto Street, Boston MA |
| Sandy | Sholes | NULL |
| Sheila | Farmington | 92 Buzzfeed Lane, Denver CO |
| Sofia | Vergara | 742 Applewild, San Francisco CA |
+------------+------------+---------------------------------+
12 rows in set (0.00 sec)
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:
mysql> select first_name, last_name, address from houses
-> right join friends using(house_id) where houses.address is null;
+------------+-----------+---------+
| first_name | last_name | address |
+------------+-----------+---------+
| Heather | Jeffries | NULL |
| Sandy | Sholes | NULL |
+------------+-----------+---------+
2 rows in set (0.00 sec)
Conclusion
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.
