HVTA [cnttbachkhoa]

First, some sample data:
         Mr Brown, Person number 1, has a phone number 01225 708225
         Miss Smith, Person number 2, has a phone number 01225 899360
         Mr Pullen, Person number 3, has a phone number 01380 724040
and also:
         Person number 1 is selling property number 1 - Old House Farm
         Person number 3 is selling property number 2 - The Willows
         Person number 3 is (also) selling property number 3 - Tall Trees
         Person number 3 is (also) selling property number 4 - The  Melksham Florist
         Person number 4 is selling property number 5 - Dun Roamin.

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)

mysql> 

An INNER JOIN does a full join, just like the first example, and the  word OUTER may be added after the word LEFT or RIGHT in the last two  examples - it's provided for ODBC compatibility and doesn't add an extra  capabilities.
Source: Well House