Miru
Miru

Reputation: 158

Understanding Self Join

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?

Upvotes: 3

Views: 189

Answers (4)

Suraj Kumar
Suraj Kumar

Reputation: 574

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.

Upvotes: 2

Beel
Beel

Reputation: 1030

For each row, manager_id refers to the employee's manager. This is described by your second query. Here you match emp.manager_id to the employee in the joined table. The asserts that the relationship exists because of the id column -- in other words, Ola's manager is anyone with a manager_id of 1. In this case, both Ahmed and Tove have a manager_id of 1, so they both match. HTH.

Upvotes: 1

Mihai
Mihai

Reputation: 26784

manager.employee will give you the correct name only if you JOIN on manager.id which only happens in the second query.

The alias of the table which uses id in the ON condition MUST be the same which uses employee column,so manager.id->manager.employee.

Upvotes: 1

Odin
Odin

Reputation: 3358

Writing WHERE emp.id = manager.manager_id does not make much sense, because manager (or the row you want to display as a manager) does NOT have a manager_id. I.e you must start with emp.manager_id because you want to list employees and to each manager_id of that employee you want to list the corresponding manager.

Upvotes: 1

Related Questions