A MySQL Primer on Joins, Part Two

Previously we discussed the three basic types of joins in MySQL. We explained how all other joins are merely syntactical variations of join, left join, and right join. Here we will look at two other joins, natural and cross joins, and follow up with MySQL’s best friend: the ‘using’ clause.

If you have been following along we will be using the same tables and data from the previous article for some examples. If you would like to follow along, just grab the MySQL statements in the ‘Follow Along’ section in the previous article.

Natural Join

A natural join is pretty handy (though perhaps not as handy as the ‘using’ clause). Instead of explicitly stating the join condition, only the join field needs specified. For example, the previous query can be written as:

mysql> SELECT * FROM people NATURAL RIGHT JOIN bodies body_id;

This makes writing queries much easier. Unfortunately, this does not work for a regular join, hence the ‘using’ clause is perhaps more useful.

Cross Join

A cross join is rather useless since it is syntactically the same as a left join, and the query is even longer when using it. It’s best to show with an example that I’ll use from MySQL’s site:

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

is equivalent to:

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

The Using Clause

The ‘using’ clause is perhaps your best friend when writing MySQL joins. It does essentially the same thing as the natural join, but better. The clause allows one to not have to explicitly state the join condition. For example, let’s say we want to know each person’s name and their list of body parts, we can do:

mysql> SELECT people.name ,parts.type FROM people JOIN bodies USING (body_id) JOIN parts USING (part_id);
+------+-------+
| name | type  |
+------+-------+
| Bob  | head  |
| Bob  | torso |
| Bob  | arms  |
| Bob  | legs  |
| Jim  | head  |
| Jim  | torso |
| Jim  | arms  |
| Jim  | legs  |
+------+-------+
8 rows in set (0.00 sec)

As you can see, the ‘using’ clause can significantly ease writing queries. MySQL joins are an essential tool for any developer, and hopefully this primer can help you understand how they work. For a visual reference, SQLAuthority has an article that explains with Venn diagrams. Feel free to share with us any other tips or tricks you have in comments below!

Published
Categorized as Database

By Joe Purcell

Joe Purcell is a technology virtuoso, cyberspace frontiersman, and connoisseur of Linux, Mac, and Windows alike.

Leave a comment