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> 



If I do a regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT), then I get all records that match in the appropriate way in the two tables, and records in both 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)

SELECT product_tbl.id, product_tbl.product_code, product_tbl.product_name
FROM product_tbl
LEFT OUTER JOIN image_tbl
ON product_tbl.id = image_tbl.product_id
WHERE image_tbl.product_id IS NULL