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
