|
Mysql join
There are several types of joins available in MySQL. Here is a short introduction to some of them with examples to illustrate. (note it is valid for MySQL 5, not sure what about later versions)
Let's first introduce the example which will illustrate our document.
Assume we have two tables: a table about authors and one about books:
| Author |
Books |
| id |
name |
| 1 |
Anna |
| 2 |
George |
| 3 |
Sean |
| 4 |
Mary |
|
| book_id |
author_id |
title |
| 1 |
1 |
book 1 |
| 2 |
1 |
book 2 |
| 3 |
2 |
book 3 |
| 4 |
3 |
book 4 |
|
The three joins we are going to see is the standard join and left join. There are a few more, but these are the ones that are used most frequently.
Join
So we will do a standard 2-way join on our two tables, combining them on the author_id. The mySQL query looks like this:
SELECT * FROM Author, Books WHERE Author.id = Books.author_id;
After the join, we will obtain the following table:
| id |
name |
book_id |
author_id |
title |
| 1 |
Anna |
1 |
1 |
book 1 |
| 1 |
Anna |
2 |
1 |
book 2 |
| 2 |
George |
3 |
2 |
book 3 |
| 3 |
Sean |
4 |
3 |
book 4 |
Left Join
The left join is takes the left table as it is and joins the right table whenever there is a correspondance. In the case of the previous example, the SQL code looks like this:
SELECT * FROM Author LEFT JOIN Books ON Author.id = Books.author_id;
From the view of the syntax, you can see that instead of writing the join condition after the "where" keyword, we use "on" this time.
After the join, the table will look like this:
| id |
name |
book_id |
author_id |
title |
| 1 |
Anna |
1 |
1 |
book 1 |
| 1 |
Anna |
2 |
1 |
book 2 |
| 2 |
George |
3 |
2 |
book 3 |
| 3 |
Sean |
4 |
3 |
book 4 |
| 4 |
Mary |
NULL |
NULL |
NULL |
Right join
The right join works in the same way as the left join, but takes the right table as first table and then joins the left table when there is a correspondance. The syntax is very similar to the left join syntax:
SELECT * FROM Author RIGHT JOIN Books ON Author.id = Books.author_id;
After the join, the table will look the same as the normal join, in this case.
| id |
name |
book_id |
author_id |
title |
| 1 |
Anna |
1 |
1 |
book 1 |
| 1 |
Anna |
2 |
1 |
book 2 |
| 2 |
George |
3 |
2 |
book 3 |
| 3 |
Sean |
4 |
3 |
book 4 |
|
