SQL 什么是 SELF JOIN，什么时候使用它？

What is SELF JOIN and when would you use it?

提问by Imran

What is self join and when would you use it? I don't understand self joins so a layman explanation with an example would be great.

什么是自联接，什么时候使用它？我不明白自我加入，所以一个外行的例子解释会很棒。

回答by RedFilter

You use a self join when a table references data in itself.

当表本身引用数据时，您可以使用自联接。

E.g., an Employeetable may have a SupervisorIDcolumn that points to the employee that is the boss of the current employee.

例如，一个Employee表可能有一SupervisorID列指向作为当前雇员的老板的雇员。

To query the data and get information for both people in one row, you could self join like this:

要查询数据并获取一行中两个人的信息，您可以像这样自我加入：

select e1.EmployeeID, 
    e1.FirstName, 
    e1.LastName,
    e1.SupervisorID, 
    e2.FirstName as SupervisorFirstName, 
    e2.LastName as SupervisorLastName
from Employee e1
left outer join Employee e2 on e1.SupervisorID = e2.EmployeeID

回答by paxdiablo

Well, one classic example is where you wanted to get a list of employees and their immediate managers:

嗯，一个经典的例子是你想要获得一份员工及其直接经理的列表：

select e.employee as employee, b.employee as boss
from emptable e, emptable b
where e.manager_id = b.empolyee_id
order by 1

It's basically used where there is any relationship between rows stored in the same table.

它基本上用于存储在同一个表中的行之间存在任何关系的情况。

• employees.
• multi-level marketing.
• machine parts.

• 雇员。
• 多层次营销。
• 机械零件。

And so on...

等等...

回答by Mark Byers

A self join is simply when you join a table with itself. There is no SELF JOINkeyword, you just write an ordinary join where both tables involved in the join are the same table. One thing to notice is that when you are self joining it is necessary to use an alias for the table otherwise the table name would be ambiguous.

自联接只是将表与自身联接。没有SELF JOIN关键字，您只需编写一个普通连接，其中连接中涉及的两个表是同一个表。需要注意的一件事是，当您自联接时，必须为表使用别名，否则表名将不明确。

It is useful when you want to correlate pairs of rows from the same table, for example a parent - child relationship. The following query returns the names of all immediate subcategories of the category 'Kitchen'.

当您想要关联同一个表中的行对时，它很有用，例如父子关系。以下查询返回类别“厨房”的所有直接子类别的名称。

SELECT T2.name
FROM category T1
JOIN category T2
ON T2.parent = T1.id
WHERE T1.name = 'Kitchen'

回答by Michael Pakhantsov

SQL self-join simply is a normal join which is used to join a table to itself.

SQL 自联接只是一种普通联接，用于将表与其自身联接。

Example:

例子：

Select *
FROM Table t1, Table t2
WHERE t1.Id = t2.ID

回答by Will A

You'd use a self-join on a table that "refers" to itself - e.g. a table of employees where managerid is a foreign-key to employeeid on that same table.

您将在“引用”自身的表上使用自联接 - 例如，一个员工表，其中 managerid 是同一表上员工 ID 的外键。

Example:

例子：

SELECT E.name, ME.name AS manager
FROM dbo.Employees E
LEFT JOIN dbo.Employees ME
ON ME.employeeid = E.managerid