MySql Joins – LEFT JOIN, RIGHT JOIN, INNER and OUTER JOIN

In a database such as MySQL, data is divided into a series of tables which are then connected together in SELECT commands to generate the output required. People often get confused between all the join flavours. Let me give you an example to see how it works.

mysql> select * from demo_people;

+------------+--------------+------+
| name       | phone        | pid  |

+------------+--------------+------+
| Mr Brown   | 01225 708225 |    1 |
| Miss Smith | 01225 899360 |    2 |
| Mr Pullen  | 01380 724040 |    3 |

+------------+--------------+------+
	
3 rows in set (0.00 sec)

mysql> select * from demo_property;

+------+------+----------------------+
	
| pid  | spid | selling              |
+------+------+----------------------+

|    1 |    1 | Old House Farm       |
|    3 |    2 | The Willows          |
|    3 |    3 | Tall Trees           |
|    3 |    4 | The Melksham Florist |
|    4 |    5 | Dun Roamin           |
+------+------+----------------------+

5 rows in set (0.00 sec) mysql> 

Regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT). I get all records that match in the appropriate way in the two different tables. Both tables records incoming tables that do not match are not reported.

mysql> select name, phone, selling 
from demo_people join demo_property 

on demo_people.pid = demo_property.pid;

+-----------+--------------+----------------------+
	
| name      | phone        | selling              |

+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
	
| Mr Pullen | 01380 724040 | The Willows          |

| Mr Pullen | 01380 724040 | Tall Trees           |
	
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+

4 rows in set (0.01 sec)

mysql> 

If I do a LEFT JOIN, I get all records that match in the same way and IN ADDITION. I get an extra record for each unmatched record in the left table of the join. Thus ensuring (in my example) that every PERSON gets a mention:

mysql> select name, phone, selling 
from demo_people left join demo_property 
on demo_people.pid = demo_property.pid; 
+------------+--------------+----------------------+
	
| name       | phone        | selling              |
	
+------------+--------------+----------------------+
| Mr Brown   | 01225 708225 | Old House Farm       |
| Miss Smith | 01225 899360 | NULL                 |
| Mr Pullen  | 01380 724040 | The Willows          |
| Mr Pullen  | 01380 724040 | Tall Trees           |
| Mr Pullen  | 01380 724040 | The Melksham Florist |
	
+------------+--------------+----------------------+

5 rows in set (0.00 sec)
mysql> 

If I do a RIGHT JOIN, I get all the records that match and IN ADDITION I get an extra record for each unmatched record in the right table of the join – in my example, that means that each property gets a mention even if we don’t have seller details:

mysql> select name, phone, selling 

from demo_people right join demo_property 

on demo_people.pid = demo_property.pid;
	
+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+

| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
	
| NULL      | NULL         | Dun Roamin           |
+-----------+--------------+----------------------+

5 rows in set (0.00 sec)