了解自我加入 [英] Understanding Self Join

问题描述

我正在练习自我加入，这是我在编写查询时不了解的事情.

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

我有一张桌子'employee'

employee表包含三个记录.

The employee table contains three records.

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

最后一列manager_id是指使艾哈迈德和托夫成为Ola经理的第一列ID.

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

如果我编写类似

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

结果使Ahmed和Tove Manager成为可能. 而

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?

推荐答案

自我联接就像内部联接，其中同一表的两个或更多实例通过公共数据类型的列/字段联接在一起.这种连接(内部连接)根据连接条件给出公共行.

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.

employee表包含三个记录.在这种情况下，

The employee table contains three records. In this case,

雇员为emp:

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

员工为经理

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

现在第一种情况:让我们尝试一下以了解不同之处:

选择emp.*，manager.* 从员工为雇员，从员工为经理 哪里emp.id = manager.manager_id

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       |
+----------+----------+------------+----------+----------+------------+

请参阅emp.id = manager.manager_id.因此，emp.employee作为NAME从第一张表&经理(manager.employee)作为经理向艾哈迈德(Ahmed& amp;)提供行从第二张桌子上翻过来.

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.

现在第二种情况:让我们尝试一下以了解区别:

选择emp.*，manager.* 从员工为雇员，从员工为经理 WHERE manager.id = emp.manager_id

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     |
+----------+----------+------------+----------+----------+------------+

请参阅manager.id = emp.manager_id.因此，以emp.employee作为NAME的行为会给Ahmed&从第一张桌子& manager.employee作为MANAGER从第二张表中给出Ola行.

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.

这篇关于了解自我加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！