MySQL part 1: JOINs in a nutshell.

Recently I decided to go more deep in the topic of  MySQL optimization. However, to start working on this issue I needed to organize my expertise in this field. This way this short “memo” is created. Maybe it will be useful for somebody…

So, here we go.

First of all, we need some sample database. I love The Lost series, so I thought up a database for storing pictures of the main characters.

The information we want to save in this database are as follows:

• Every picture has a file name and a date when it was taken.
• Every picture may be associated a type and a place where it was taken.
• Additionally every character can be tagged on several pictures, and every picture can be tagged with several characters (many-to-many)

Here you can find the SQL script for this DB.

To warm up let’s start with something simple…

We have to choose pictures and the types related to them (only if the picture has a type). We can do it in two ways:

using WHERE:

select filename, name from pictures, types where pictures.type_id=types.id;

or using JOIN:

select filename, name from pictures join types on (pictures.type_id = types.id);

In both cases the result set will be:

picture1.jpg	funny
picture5.jpg	funny
picture4.jpg	landscape
picture2.jpg	misc

In this case WHERE and JOIN work well, however they work like intersection.

LEFT JOIN

Imagine situation when you need all pictures. Doesn’t matter if they have a type. In such case you can use LEFT JOIN (what means, that the query returns all rows from the left table, even if there are no matches in the right table).

select filename, name from pictures left join types on (pictures.type_id = types.id);

picture1.jpg	funny
picture2.jpg	misc
picture3.jpg
picture4.jpg	landscape
picture5.jpg	funny

RIGHT JOIN

We can also do something opposite: chose all types and join the pictures that belongs to them:

select filename, name from pictures right join types on (pictures.type_id = types.id);

This time we used RIGHT JOIN (the query returns all rows from the right table, even if there are no matches in the left table).

picture1.jpg	funny
picture5.jpg	funny
picture4.jpg	landscape
picture2.jpg	misc
	portrait

In most cases we can use LEFT or RIGHT JOIN to get the same results (anyway it is recommended using LEFT JOIN).

select filename, name from types left join pictures on (pictures.type_id = types.id);

picture1.jpg	funny
picture5.jpg	funny
picture4.jpg	landscape
picture2.jpg	misc
	portrait

NATURAL JOIN

Another type of join is NATURAL JOIN. We can use it in case when we want to join two (or more) tables using columns with the same names. NATURAL JOIN works without any join condition. (You must be careful: if you have a habit to name primary key column “id”, MySQL may use this column to create the conjunction.)

select filename, name from pictures natural join places;

picture1.jpg	Los Angeles
picture2.jpg	Sydney
picture4.jpg	Sydney
picture3.jpg	The Island

In this case both tables have a column named “place_id” and this column is used to create relationship.

At the end it is worth mentioning INNER and CROSS JOIN. In MySQL they are equivalent to each other and produce exactly the same result as JOIN.

You can find more about JOINs here:

• http://dev.mysql.com/doc/refman/5.1/en/join.html
• http://www.w3schools.com/sql/sql_join.asp
• http://mysql-join.com/reference/mysql-supported-joins/

Related pots:

• MySQL part 2: a bit more advanced JOINs.
• MySQL part 3: optimization hints.