Tuesday, February 2, 2010

Joins in mysql

There are different joins available in mysql to retrieve results from multiple tables.
Join, left join, right join, inner join, outer join

Here i explained each one by taking two tables as an example.

mysql>Products Table;
+----+--------------+--------------+
| 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)

Normal Join

mysql> SELECT buyers.buyer_name, buyers.quantity, products.product_name FROM buyer
s join products on buyers.pid=products.id;
(OR)
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)

The above query displays results only with matching pids in both tables.



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)

Explaination:
Mysql starts with the left table (buyers) as main source then For each row from the table buyers mysql scans the table products with pid (i.e id of the product in products table) and returns the product name. Then the product name is joined with the matching row to the table buyers. For unmatched rows it returns null.

The above query is same as below (except with null values)

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)

The above query displays results for only matching pids in both tables.

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)

Explaination:
Mysql starts with the Right table (products) as main source then For each row from the table products mysql scans the table buyers with id (i.e pid of the buyers table) and returns the product name. Then the product name is joined with the matching row to the table products. For unmatched rows it returns null. From my example above it returns NULL for DVD because no one bought DVD.

Inner join is same as join.

The word OUTER may be added after the word LEFT or RIGHT in the left/right joins - it's provided for ODBC compatibility and doesn't add an extra capabilities.

No comments: