The following site contains great tutorials about mysql joins and other mysql features, here is the link:
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.shtml

JOINS

There are many type of JOINS and also many names to refer to the same concept:

- Cross-Join
- Equi-Join or Inner Join
- Left Join
- Right Join
- Self Joins

Cross-Joins

A Cross Join is the basic form of a join. If we have 2 tables, it takes each row of table1 and append it to each row of table2. So if 'table1' has 4 rows and 'table2' has 3 rows we will end with 12 rows (all possible combinations).

Ex:

select * from TABLE1, TABLE2

Of course we can have more than 2 tables, which add more complexity to the resulting rows.

Equi-Join or Inner Join

Here You can specify a condition in the where section. This condition will match only those rows whose column's values match:

Ex:

select P.name, C.name
from Product as P , Category as C
where P.category_id = C.id

This type of joins can get very complicated, like this:

select P.name, C.name , S.name, M.name
from Product as P , Category as C, Style as S,, Manufacturer as M
where P.category_id = C.id and P.manufacturer_id = M.id and
            P.style_id = S.id

Left Join

We can use this to join tables based on the matching of some column. One difference of an Equi-Join and a Left Join is that the former use the where clause to declare the table-matching conditions while the latter puts the condition in a LEFT JOIN clause.
Another difference is that the latter will show a row even if the right table(s) do(es) not match the value, because it only cares about the left table.

To rewrite the previous equi-join we would do:

select P.name, C.name , S.name, M.name
from Product as P
left join Category as C on C.id = P.category_id
left join Manufacturer as M on M.id = P.manufacturer_id
left join Style as S on S.id = P.style_id

So, the query seems better organized and easier to read. Note that we can put other conditions on the where clause!!.

Right Join

It'ss an analogous concept to a left join. The difference is that the table to the right it's displayed even if the left table doesn't have rows to match.