MySQL LEFT, RIGHT JOIN tutorial

MySQL joins are hard for beginners. At least for me when I was beginner.
I will try to explain the joins in the simplest possible way.

Join in MySQL is a query where you can join one or more tables.

For example we have two tables: products and buyers with the following structures.
Table products:

mysql> SELECT * FROM products;
+----+--------------+--------------+
| id | product_name | manufacturer |
+----+--------------+--------------+
|  1 | Shoes        | Company1     |
|  2 | Laptop       | Company2     |
|  3 | Monitor      | Company3     |
|  4 | DVD          | Company4     |
+----+--------------+--------------+
4 rows in set (0.00 sec)

Table buyers:
mysql> SELECT * FROM buyers;

+----+------+------------+----------+
| id | pid  | buyer_name | quantity |
+----+------+------------+----------+
|  1 |    1 | Steve      |        2 |
|  2 |    2 | John       |        1 |
|  3 |    3 | Larry      |        1 |
|  4 |    3 | Michael    |        5 |
|  5 | NULL | Steven     |     NULL |
+----+------+------------+----------+
5 rows in set (0.00 sec)

Left Join

mysql> SELECT buyer_name, quantity, product_name FROM buyers LEFT JOIN products ON
 buyers.pid=products.id;
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve      |        2 | Shoes        |
| John       |        1 | Laptop       |
| Larry      |        1 | Monitor      |
| Michael    |        5 | Monitor      |
| Steven     |     NULL | NULL         |
+------------+----------+--------------+
5 rows in set (0.00 sec)

What happened?
Mysql starts with the left table (buyers). For each row from the table buyers mysql scans the table products, finds the id of the product and returns the product name. Then the product name is joined with the matching row from the table buyers. For unmatched rows it returns null.
To make it simpler, the above query is same as (except the unmatched rows are not returned):

mysql> SELECT buyers.buyer_name, buyers.quantity, products.product_name FROM buyer
s,products WHERE buyers.pid=products.id;
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve      |        2 | Shoes        |
| John       |        1 | Laptop       |
| Larry      |        1 | Monitor      |
| Michael    |        5 | Monitor      |
+------------+----------+--------------+
4 rows in set (0.00 sec)

Right Join

mysql> SELECT buyer_name, quantity, product_name FROM buyers RIGHT JOIN products ON 
buyers.pid=products.id;
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve      |        2 | Shoes        |
| John       |        1 | Laptop       |
| Larry      |        1 | Monitor      |
| Michael    |        5 | Monitor      |
| NULL       |     NULL | DVD          |
+------------+----------+--------------+
5 rows in set (0.00 sec)

What happens here is Mysql starts with the Right table (products). For each id from the table products MySQL scans the left table - buyers to find the matching pid. When it finds the matching pid it returns the buyer_name and the quantity. For unmatched rows it returns null. From my example above it returns NULL for DVD because no one bought DVD.

If you have questions visit the forum or post a comment.

Comments

RAM

Thank u very much

Good comments

It is very good tutorial i was very confused about the joins but got a clear idea from this.Really i am very impressed from this.

A bit ambiguous...

I would like to see examples comparing LEFT and RIGHT JOINs where they do not come up with nearly identical results. The text states, "MySQL starts with the left table (buyers)". Where is it determined that 'buyers' is the left table? Because its columns are mentioned first in the SELECT clause? It's never really made clear.

It is the first table

It is the first table considered as left and the second as right table.

mysql join

its great tutorial for mysql joininig for begginers...
i was totally confused about these points...before i see this one

Funtastic Answer

From this page I learn very quickly what is mean JOIN and How we use in our application.
Thank u very much.