The Tricky.net

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


Comments
Search RSS
good example for join
ajit (111.92.93.xxx) 29-05-2013 10:40:46

Nice blog
Tushar (59.184.180.xxx) 14-05-2012 08:42:58

you have explained all this tutorials very simplified manner..
Please add some
more topics..
lai lai bhari
marathi (115.115.82.xxx) 14-12-2011 13:03:25

lai bahri
Arjit (124.247.229.xxx) 16-04-2010 10:29:47

gr8.....though I had some gud knowledge, JOIN always confused me.I often use
2-way join..but never left/right join. Thanx to this article, I'll start to use
it, gr8 article, hope to see such articles often in this site
Only registered users can write comments!

3.23 Copyright (C) 2007 Alain Georgette / Copyright (C) 2006 Frantisek Hliva. All rights reserved."