Self join is like a inner join where two or more instances of same table are joined together through a common data type column/field. Such join(inner join) gives the common rows as result, based on the joining condition.
The employee table contains three records. In this case,
employee as emp:
+-----+---------------+------------+
| id | employee | manager_id |
+-----+---------------+------------+
| 1 | Ola | NULL |
| 2 | Ahmed | 1 |
| 3 | Tove | 1 |
+----------+----------+------------+
employee as manager:
+-----+---------------+------------+
| id | employee | manager_id |
+-----+---------------+------------+
| 1 | Ola | NULL |
| 2 | Ahmed | 1 |
| 3 | Tove | 1 |
+----------+----------+------------+
Now First case: Lets try this to understand the difference:
SELECT emp.*
, manager.*
FROM employee as emp, employee as manager
WHERE emp.id = manager.manager_id
+-----+---------------+------------+-----+---------------+------------+
| id | employee | manager_id | id | employee | manager_id |
+-----+---------------+------------+-----+---------------+------------+
| 1 | Ola | NULL | 2 | Ahmed | 1 |
| 1 | Ola | NULL | 3 | Tove | 1 |
+----------+----------+------------+----------+----------+------------+
See, emp.id = manager.manager_id . Thus, emp.employee as NAME is giving rows of Ola from first table & manager.employee as MANAGER is giving rows of Ahmed & Tove from the second table.
Now Second case: Lets try this to understand the difference:
SELECT emp.*
, manager.*
FROM employee as emp, employee as manager
WHERE manager.id = emp.manager_id
+-----+---------------+------------+-----+---------------+------------+
| id | employee | manager_id | id | employee | manager_id |
+-----+---------------+------------+-----+---------------+------------+
| 2 | Ahmed | 1 | 1 | Ola | NULL |
| 3 | Tove | 1 | 1 | Ola | NULL |
+----------+----------+------------+----------+----------+------------+
See, manager.id = emp.manager_id . Thus, emp.employee as NAME is giving rows of Ahmed & Tove from first table & manager.employee as MANAGER is giving rows of Ola from the second table.