Create The Internet… It's What I Do EVERY Day – Joseph Yancey

MySQL Joins

I hated joins as a young developer.  I just want the world to know that.  Once I found out exactly how they worked, I didn't hate them at all.  Joins can be your friend.

Joins simply relate rows in one table with rows in another table.

Take these tables for example:

Books
+---------+-----------+
| book_id | author    |
+---------+-----------+
|   1     |     joseph|
|   2     |     john  |
|   3     |      sue  |
|   4     |     NULL  |
|   5     |     john  |
|   6     |     sam   |
+---------+-----------+

Authors
+---------+-----------+
| author  | sex       |
+---------+-----------+
|   joseph|     male  |
|   john  |     male  |
|   sue   |     female|
|   lucy  |     female|
|   sam   |     NULL  | <- lets just pretend we don't know
+---------+-----------+

In MySQL, there are three important types of joins.  There are:

1. Inner Joins
2. Left Joins
3. Right Joins

Inner Joins

Inner joins combine rows that have matches in both tables.  There will be no nulls left in the resulting table at all.  This query would be written like:

• select * from Books, Authors where Books.author = Authors.author
• or
• select * from Books inner join Authors on (Books.author = Authors.author)

You will get back:

+---------+---------+-----------+
| book_id | author  | sex       |
+---------+---------+-----------+
|    1    |   joseph|     male  |
|    2    |   john  |     male  |
|    3    |   sue   |     female|
|    5    |   john  |     male  |
+---------+---------+-----------+

Left Joins

Left joins take all results from the left table and combine the matching results from the right table. This query would be written like:

• select * from Books left join Authors on (Books.author = Authors.author)

You will get back:

+---------+---------+-----------+
| book_id | author  | sex       |
+---------+---------+-----------+
|    1    |   joseph|     male  |
|    2    |   john  |     male  |
|    3    |   sue   |     female|
|    4    |   NULL  |     NULL  | <- because there isn't an author to match
|    5    |   john  |     male  |
|    6    |   sam   |     NULL  | <- because we don't know sam's sex
+---------+---------+-----------+

Right Joins

Right joins are exactly like left joins but they will give all results from the right table while matching the left table to it. This query would be written like:

• select * from Books right join Authors on (Books.author = Authors.author)

You will get back:

+---------+---------+-----------+
| book_id | author  | sex       |
+---------+---------+-----------+
|    1    |   joseph|     male  |
|    2    |   john  |     male  |
|    5    |   john  |     male  | <- john is here twice because there are 2 matches in the left table
|    3    |   sue   |     female|
|    NULL |   lucy  |     female| <- because this is a right join and lucy is in the right table
|    6    |   sam   |     NULL  |
+---------+---------+-----------+