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;
Code: Select all
+----+--------------+--------------+
| id | product_name | manufacturer |
+----+--------------+--------------+
| 1 | Shoes | Company1 |
| 2 | Laptop | Company2 |
| 3 | Monitor | Company3 |
| 4 | DVD | Company4 |
+----+--------------+--------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM buyers;
Code: Select all
+----+------+------------+----------+
| 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)
mysql> SELECT buyer_name, quantity, product_name FROM buyers LEFT JOIN products ON
buyers.pid=products.id;
Code: Select all
+------------+----------+--------------+
| 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)
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;
Code: Select all
+------------+----------+--------------+
| buyer_name | quantity | product_name |
+------------+----------+--------------+
| Steve | 2 | Shoes |
| John | 1 | Laptop |
| Larry | 1 | Monitor |
| Michael | 5 | Monitor |
+------------+----------+--------------+
4 rows in set (0.00 sec)
mysql> SELECT buyer_name, quantity, product_name FROM buyers RIGHT JOIN products ON
buyers.pid=products.id;
Code: Select all
+------------+----------+--------------+
| 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)