[MySQL] Inner Join, Left Join, Right Join

Often, common joins such as Inner Join, Left Join and Right Join confused people so I will have little demonstration to compare the difference between the three.

Sample Tables:

User
+------+---------+----------+
| id   | name    | location |
+------+---------+----------+
|    1 | erin    |        1 |
|    2 | roran   |        2 |
|    3 | vincent |        1 |
|    4 | travis  |        0 |
+------+---------+----------+

Location
+------+------------+
| id   | place      |
+------+------------+
|    1 | california |
|    2 | new york   |
|    3 | tenessee   |
|    4 | hawaii     |
|    5 | italy      |
+------+------------+

Inner Join:
INNER JOIN displays only the fields from User table who have pairs on Location table

SELECT User.id, User.name, Location.place
FROM User
INNER JOIN Location on User.location=Location.id;

+------+---------+------------+
| id   | name    | place      |
+------+---------+------------+
|    1 | erin    | california |
|    2 | roran   | new york   |
|    3 | vincent | california |
+------+---------+------------+

Left Join:
LEFT JOIN displays the all fields from User table then display place field its paired of

SELECT User.id, User.name, Location.place
FROM User
LEFT JOIN Location on User.location=Location.id;

+------+---------+------------+
| id   | name    | place      |
+------+---------+------------+
|    1 | erin    | california |
|    2 | roran   | new york   |
|    3 | vincent | california |
|    4 | travis  | NULL       |
+------+---------+------------+

Right Join:
RIGHT JOIN displays the all fields from Location table then display id and name fields its paired of

SELECT User.id, User.name, Location.place
FROM User
RIGHT JOIN Location on User.location=Location.id;

+------+---------+------------+
| id   | name    | place      |
+------+---------+------------+
| NULL | NULL    | tenessee   |
| NULL | NULL    | hawaii     |
| NULL | NULL    | italy      |
|    1 | erin    | california |
|    2 | roran   | new york   |
|    3 | vincent | california |
+------+---------+------------+

Note: In the example above, User table was referred as the left table while Location table was referred to as Right table