Master MySQL Joins

MySQL is such a mistreated language. It’s almost as if all we want to do is SELECT INSERT and UPDATE stuff. Ok thats what we want to do 99% of the time, but that other 1%, you can do some pretty cool things with (If you’re a geek).

Now I bet you was hoping I would teach you how to fight aliens with MySQL, but instead I’m going to talk about joins. Joins sure as hell aren’t amazing, or revolutionary, but I bet, whether you are a newbie or a seasoned pro, there will be something you can learn from this article.

To start things off, I’m going to create a very simple data set, I will then use this data when describing each type of MySQL join.

Sample Data

Table name: users
+---------+-------+
| user_id | name  |
+---------+-------+
|       1 | Sarah |
|       2 | Steve |
|       3 | Frank |
|       4 | Allan |
|       5 | Ellis |
|       6 | Kevin |
|       7 | Holly |
+---------+-------+

Table name: cars
+--------+---------------+
| car_id | manufacturer  |
+--------+---------------+
|      1 | Ford          |
|      2 | Vauxhall      |
|      3 | Aston Martin  |
|      4 | Hummer        |
|      5 | Nissan        |
|      6 | Cadillac      |
|      7 | Alfa          |
+--------+---------------+

Table name: car_owners
+--------------+---------+--------+
| car_owner_id | user_id | car_id |
+--------------+---------+--------+
|            1 |       1 |      1 |
|            2 |       1 |      2 |
|            3 |       1 |      3 |
|            4 |       2 |      4 |
|            5 |       2 |      3 |
|            6 |       3 |      6 |
|            7 |       3 |      7 |
|            8 |       4 |      1 |
|            9 |       5 |      2 |
|           10 |       5 |      3 |
|           11 |       5 |      4 |
|           12 |       7 |      4 |
|           13 |       7 |      6 |
|           14 |       7 |      7 |
+--------------+---------+--------+

Take Note: User ID 6 does not have a car and car ID 5 is owned by no one.

Left Join & Left Outer Join

The word OUTER is optional, some say it makes differentiation easier, however I find just LEFT JOIN a lot cleaner. LEFT JOIN will join 2 tables based on the data in the left most table in your query. So if a record is in the right table but not the left table, it will be omitted.

Query

  SELECT c.manufacturer, u.name FROM cars c LEFT JOIN car_owners o USING (car_id) LEFT JOIN users u USING (user_id);

Output

+---------------+-------+
| manufacturer  | name  |
+---------------+-------+
| Ford          | Sarah |
| Ford          | Allan |
| Vauxhall      | Sarah |
| Vauxhall      | Ellis |
| Aston Martin  | Sarah |
| Aston Martin  | Steve |
| Aston Martin  | Ellis |
| Hummer        | Steve |
| Hummer        | Ellis |
| Hummer        | Holly |
| Nissan        | NULL  |
| Cadillac      | Frank |
| Cadillac      | Holly |
| Alfa          | Frank |
| Alfa          | Holly |
+---------------+-------+

Take Note: Nissan is in the result, even though no one own one.

Right Join & Right Outer Join

Again the word OUTER is optional. RIGHT JOIN will join 2 tables based on the data in the rightmost table in your query. So if a record is in the left table but not the right table, it will be omitted.

Query

  SELECT c.manufacturer, u.name FROM cars c RIGHT JOIN car_owners o USING (car_id) RIGHT JOIN users u USING (user_id);

Output

+---------------+-------+
| manufacturer  | name  |
+---------------+-------+
| Ford          | Sarah |
| Vauxhall      | Sarah |
| Aston Martin  | Sarah |
| Hummer        | Steve |
| Aston Martin  | Steve |
| Cadillac      | Frank |
| Alfa          | Frank |
| Ford          | Allan |
| Vauxhall      | Ellis |
| Aston Martin  | Ellis |
| Hummer        | Ellis |
| NULL          | Kevin |
| Hummer        | Holly |
| Cadillac      | Holly |
| Alfa          | Holly |
+---------------+-------+

Take Note: Kevin is in the result set, even though he doesn’t own a car

Join & Inner Join & table1, table2

There are 3 ways you can use an INNER JOIN. They all return exactly the same data but have slightly different syntaxes. You can either use the clauses JOIN or INNER JOIN, or you can list the table names and detail how they join in the WHERE clause. This is sometimes called an equi-join. INNER JOIN’s will only return records which exists in both tables. So if a record is in the left table but not the right table, or vice versa, it will be omitted.

Queries

  SELECT c.manufacturer, u.name FROM cars c INNER JOIN car_owners o USING (car_id) INNER JOIN users u USING (user_id);

or

  SELECT c.manufacturer, u.name FROM cars c, car_owners o, users u WHERE o.car_id=c.car_id AND o.user_id=u.user_id;

Output

+---------------+-------+
| manufacturer  | name  |
+---------------+-------+
| Ford          | Sarah |
| Vauxhall      | Sarah |
| Aston Martin  | Sarah |
| Hummer        | Steve |
| Aston Martin  | Steve |
| Cadillac      | Frank |
| Alfa          | Frank |
| Ford          | Allan |
| Vauxhall      | Ellis |
| Aston Martin  | Ellis |
| Hummer        | Ellis |
| Hummer        | Holly |
| Cadillac      | Holly |
| Alfa          | Holly |
+---------------+-------+

Take Note: Records only appear when they are in both the users and the cars table

Natural Join

NATURAL JOIN is like a clever INNER JOIN but also has one major drawback. What NATURAL JOIN will do is, join tables without you even having to tell MySQL which columns to use. MySQL does so by looking for matching column names in the tables. This is all well and good, but if later down the line you accidently add another column name to both tables, your query will quite possibly fall to its knees and plea for a quick death. For this reason I would never recommend using NATURAL JOIN.

Query

  SELECT c.manufacturer, u.name FROM cars c NATURAL JOIN car_owners o NATURAL JOIN users u;

Output

+---------------+-------+
| manufacturer  | name  |
+---------------+-------+
| Ford          | Sarah |
| Vauxhall      | Sarah |
| Aston Martin  | Sarah |
| Hummer        | Steve |
| Aston Martin  | Steve |
| Cadillac      | Frank |
| Alfa          | Frank |
| Ford          | Allan |
| Vauxhall      | Ellis |
| Aston Martin  | Ellis |
| Hummer        | Ellis |
| Hummer        | Holly |
| Cadillac      | Holly |
| Alfa          | Holly |
+---------------+-------+

Natural Left Join & Natural Right Join

NATURAL LEFT JOIN & NATURAL RIGHT JOIN can also contain the optional OUTER clause. So you could write NATURAL LEFT OUTER JOIN or NATURAL RIGHT OUTER JOIN. These work exactly like LEFT JOIN and RIGHT JOIN, just with the power of NATURAL JOIN, so you do not have to define the join columns.

Query

  SELECT c.manufacturer, u.name FROM cars c NATURAL LEFT JOIN car_owners o NATURAL LEFT JOIN users u;

Output

+---------------+-------+
| manufacturer  | name  |
+---------------+-------+
| Ford          | Sarah |
| Ford          | Allan |
| Vauxhall      | Sarah |
| Vauxhall      | Ellis |
| Aston Martin  | Sarah |
| Aston Martin  | Steve |
| Aston Martin  | Ellis |
| Hummer        | Steve |
| Hummer        | Ellis |
| Hummer        | Holly |
| Nissan        | NULL  |
| Cadillac      | Frank |
| Cadillac      | Holly |
| Alfa          | Frank |
| Alfa          | Holly |
+---------------+-------+

Query

  SELECT c.manufacturer, u.name FROM cars c NATURAL RIGHT JOIN car_owners o NATURAL RIGHT JOIN users u;

Output

+---------------+-------+
| manufacturer  | name  |
+---------------+-------+
| Ford          | Sarah |
| Vauxhall      | Sarah |
| Aston Martin  | Sarah |
| Hummer        | Steve |
| Aston Martin  | Steve |
| Cadillac      | Frank |
| Alfa          | Frank |
| Ford          | Allan |
| Vauxhall      | Ellis |
| Aston Martin  | Ellis |
| Hummer        | Ellis |
| NULL          | Kevin |
| Hummer        | Holly |
| Cadillac      | Holly |
| Alfa          | Holly |
+---------------+-------+

Straight Join

STRAIGHT_JOIN works exactly like INNER JOIN, with a twist…. MySQL comes with a built in Join optimiser, it works some times by optimising your queries so data is read from the correct table first. However most of the time MySQL actually gets it wrong. When you use STRAIGHT_JOIN, data will always be read from the leftmost table in your query first.

There is no set in stone criteria, as to when to use STRAIGHT_JOIN, but when you have a semi complex query that is using INNER JOIN, it is worth trying STRAIGHT_JOIN to see if it makes your query faster.

Take Note: The _ in STRAIGHT_JOIN and the inability to use the USING clause. You have to join columns using the ON clause.

Query

  SELECT c.manufacturer, u.name FROM cars c STRAIGHT_JOIN car_owners o ON c.car_id=o.car_id STRAIGHT_JOIN users u ON o.user_id=u.user_id;
+---------------+-------+
| manufacturer  | name  |
+---------------+-------+
| Ford          | Sarah |
| Vauxhall      | Sarah |
| Aston Martin  | Sarah |
| Hummer        | Steve |
| Aston Martin  | Steve |
| Cadillac      | Frank |
| Alfa          | Frank |
| Ford          | Allan |
| Vauxhall      | Ellis |
| Aston Martin  | Ellis |
| Hummer        | Ellis |
| Hummer        | Holly |
| Cadillac      | Holly |
| Alfa          | Holly |
+---------------+-------+

Self Join

A self join is basically just joining a table to itself. This is very handy when trying to retrieve child or parent rows. In MySQL there is no such thing as a SELF JOIN, instead you just have to use an INNER JOIN.

Sample Data

Table name: people
+----+-----------+----------+
| id | parent_id | name     |
+----+-----------+----------+
|  1 |         0 | Daddy    |
|  2 |         1 | Daughter |
|  3 |         0 | Mummy    |
|  4 |         3 | Son      |
+----+-----------+----------+

Query

  SELECT c.name, p.name parent_name FROM people c INNER JOIN people p ON c.parent_id=p.id;

Output

+----------+-------------+
| name     | parent_name |
+----------+-------------+
| Daughter | Daddy       |
| Son      | Mummy       |
+----------+-------------+

Cross Join

CROSS JOIN, the most pointless join of them all, what this does is join every record in one table with every record in another table. I cannot think of a single scenario when this would be useful. It would always make sense to perform these kind of operations in your chosen programming language. However here is an example of how you would list every day in the months of July and August. As you have probably already worked out, as soon as you start adding the other 10 months, this example would also become obsolete, due to not all months containing 31 days.

Sample Data

Table name: months
+----+--------+
| id | month  |
+----+--------+
|  1 | July   |
|  2 | August |
+----+--------+

Table name: days
+----+------+
| id | day  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
|  6 |    6 |
|  7 |    7 |
|  8 |    8 |
|  9 |    9 |
| 10 |   10 |
| 11 |   11 |
| 12 |   12 |
| 13 |   13 |
| 14 |   14 |
| 15 |   15 |
| 16 |   16 |
| 17 |   17 |
| 18 |   18 |
| 19 |   19 |
| 20 |   20 |
| 21 |   21 |
| 22 |   22 |
| 23 |   23 |
| 24 |   24 |
| 25 |   25 |
| 26 |   26 |
| 27 |   27 |
| 28 |   28 |
| 29 |   29 |
| 30 |   30 |
| 31 |   31 |
+----+------+

Query

  SELECT d.day, m.month FROM days d CROSS JOIN months m;

Output

+------+--------+
| day  | month  |
+------+--------+
|    1 | July   |
|    1 | August |
|    2 | July   |
|    2 | August |
|    3 | July   |
|    3 | August |
|    4 | July   |
|    4 | August |
|    5 | July   |
|    5 | August |
|    6 | July   |
|    6 | August |
|    7 | July   |
|    7 | August |
|    8 | July   |
|    8 | August |
|    9 | July   |
|    9 | August |
|   10 | July   |
|   10 | August |
|   11 | July   |
|   11 | August |
|   12 | July   |
|   12 | August |
|   13 | July   |
|   13 | August |
|   14 | July   |
|   14 | August |
|   15 | July   |
|   15 | August |
|   16 | July   |
|   16 | August |
|   17 | July   |
|   17 | August |
|   18 | July   |
|   18 | August |
|   19 | July   |
|   19 | August |
|   20 | July   |
|   20 | August |
|   21 | July   |
|   21 | August |
|   22 | July   |
|   22 | August |
|   23 | July   |
|   23 | August |
|   24 | July   |
|   24 | August |
|   25 | July   |
|   25 | August |
|   26 | July   |
|   26 | August |
|   27 | July   |
|   27 | August |
|   28 | July   |
|   28 | August |
|   29 | July   |
|   29 | August |
|   30 | July   |
|   30 | August |
|   31 | July   |
|   31 | August |
+------+--------+

Natural Straight Outer Left Join

Only joking…. not only would a join name this long be ridiculous, it also doesn’t make any sense having a LEFT JOIN that is also STRAIGHT_JOIN. Phewwwww

Shutdown

That is all for MySQL joins, I hope I’ve taught you something and if not, atleast refreshed your memory a little.

Thanks for reading

1 Loves This

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload the CAPTCHA.