Understanding Self Join

Question

I was practicing self join and here's a thing I do not understand in writing query.

I have a table 'employee'

The employee table contains three records.

+-----+---------------+------------+
| id  | employee      | manager_id |
+-----+---------------+------------+
| 1   | Ola           |   NULL     |
| 2   | Ahmed         |    1       |
| 3   | Tove          |    1       |
+----------+----------+------------+

Last column manager_id refers to the first column id making Ola manager of Ahmed and Tove.

If I write the query like

SELECT emp.employee as NAME, manager.employee as MANAGER
FROM employee as emp, employee as manager
WHERE emp.id = manager.manager_id

Result makes Ahmed and Tove Manager. Whereas

SELECT emp.employee as NAME, manager.employee as MANAGER
FROM employee as emp, employee as manager
WHERE manager.id = emp.manager_id

Makes it correct, could anyone please explain?

Answer 1

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.