SQL 自联接的解释
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2458519/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Explanation of self-joins
提问by l--''''''---------''''''''''''
I don't understand the need for self-joins. Can someone please explain them to me?
我不明白自联接的必要性。有人可以向我解释一下吗？
A simple example would be very helpful.
一个简单的例子会很有帮助。
回答by pointlesspolitics
You can view self-join as two identical tables. But in normalization, you cannot create two copies of the table so you just simulate having two tables with self-join.
您可以将自联接视为两个相同的表。但是在规范化中，您不能创建表的两个副本，因此您只需模拟具有自联接的两个表。
Suppose you have two tables:
假设你有两个表：
Table emp1
桌子 emp1
Id Name Boss_id
1 ABC 3
2 DEF 1
3 XYZ 2
Table emp2
桌子 emp2
Id Name Boss_id
1 ABC 3
2 DEF 1
3 XYZ 2
Now, if you want to get the name of each employee with his or her boss' names:
现在，如果你想得到每个员工的名字和他或她老板的名字：
select c1.Name , c2.Name As Boss
from emp1 c1
inner join emp2 c2 on c1.Boss_id = c2.Id
Which will output the following table:
这将输出下表：
Name Boss
ABC XYZ
DEF ABC
XYZ DEF
回答by windyjonas
It's quite common when you have a table that references itself. Example: an employee table where every employee can have a manager, and you want to list all employees and the name of their manager.
当您有一个引用自身的表时，这很常见。示例：一个员工表，其中每个员工都可以有一个经理，并且您想列出所有员工及其经理的姓名。
SELECT e.name, m.name
FROM employees e LEFT OUTER JOIN employees m
ON e.manager = m.id
回答by mjv
A self join is a join of a table with itself.
自联接是表与自身的联接。
A common use case is when the table stores entities (records) which have a hierarchical relationship between them. For example a table containing person information (Name, DOB, Address...) and including a column where the ID of the Father (and/or of the mother) is included. Then with a small query like
一个常见的用例是当表存储实体（记录）时，它们之间具有层次关系。例如，一个包含个人信息（姓名、出生日期、地址...）的表，并包括一个列，其中包含父亲（和/或母亲）的 ID。然后用一个小查询，如
SELECT Child.ID, Child.Name, Child.PhoneNumber, Father.Name, Father.PhoneNumber
FROM myTableOfPersons As Child
LEFT OUTER JOIN myTableOfPersons As Father ON Child.FatherId = Father.ID
WHERE Child.City = 'Chicago' -- Or some other condition or none
we can get info about both child and father (and mother, with a second self join etc. and even grand parents etc...) in the same query.
我们可以在同一个查询中获取有关孩子和父亲（和母亲，第二个自我加入等，甚至祖父母等）的信息。
回答by ceejayoz
Let's say you have a table users, set up like so:
假设您有一个 table users，设置如下：
- user ID
- user name
- user's manager's ID
- 用户身份
- 用户名
- 用户经理的 ID
In this situation, if you wanted to pull out both the user's information andthe manager's information in one query, you might do this:
在这种情况下，如果您想在一个查询中同时提取用户信息和经理信息，您可以这样做：
SELECT users.user_id, users.user_name, managers.user_id AS manager_id, managers.user_name AS manager_name INNER JOIN users AS manager ON users.manager_id=manager.user_id
回答by Max Shawabkeh
They are useful if your table is self-referential. For example, for a table of pages, each page may have a nextand previouslink. These would be the IDs of other pages in the same table. If at some point you want to get a triple of successive pages, you'd do two self-joins on the nextand previouscolumns with the same table's idcolumn.
如果您的表是自引用的，它们将很有用。例如，对于一个页面表，每个页面可能有一个next和previous链接。这些将是同一表中其他页面的 ID。如果在某个时候您想要获得三个连续页面，您可以对具有相同表列的next和previous列进行两次自连接id。
回答by Klaus Byskov Pedersen
Imagine a table called Employeeas described below. All employees have a manager which is also an employee (maybe except for the CEO, whose manager_id would be null)
想象一个Employee如下所述的表。所有员工都有一个也是员工的经理（也许除了 CEO，他的 manager_id 将为空）
Table (Employee):
int id,
varchar name,
int manager_id
You could then use the following select to find all employees and their managers:
然后，您可以使用以下选择来查找所有员工及其经理：
select e1.name, e2.name as ManagerName
from Employee e1, Employee e2 where
where e1.manager_id = e2.id
回答by Mazhar MIK
Apart from the answers mentioned above (which are very well explained), I would like to add one example so that the use of Self Join can be easily shown. Suppose you have a table named CUSTOMERS which has the following attributes: CustomerID, CustomerName, ContactName, City, Country. Now you want to list all those who are from the "same city" . You will have to think of a replica of this table so that we can join them on the basis of CITY. The query below will clearly show what it means:
除了上面提到的答案（已经很好地解释了），我想添加一个示例，以便可以轻松展示 Self Join 的使用。假设您有一个名为 CUSTOMERS 的表，它具有以下属性：CustomerID、CustomerName、ContactName、City、Country。现在你想列出所有来自“同一个城市”的人。您将不得不考虑此表的副本，以便我们可以在 CITY 的基础上加入它们。下面的查询将清楚地显示它的含义：
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2,
A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;
回答by Eugene
Without the ability for a table to reference itself, we'd have to create as many tables for hierarchy levels as the number of layers in the hierarchy. But since that functionality is available, you join the table to itself and sql treats it as two separate tables, so everything is stored nicely in one place.
如果表无法引用自身，我们必须为层次结构级别创建与层次结构中的层数一样多的表。但是由于该功能可用，您可以将表与自身连接起来，sql 将其视为两个单独的表，因此所有内容都很好地存储在一个地方。
回答by BClaydon
There are many correct answers here, but there is a variation that is equally correct. You can place your join conditions in the join statement instead of the WHERE clause.
这里有很多正确的答案，但也有一个同样正确的变体。您可以将连接条件放在连接语句中，而不是放在 WHERE 子句中。
SELECT e1.emp_id AS 'Emp_ID'
, e1.emp_name AS 'Emp_Name'
, e2.emp_id AS 'Manager_ID'
, e2.emp_name AS 'Manager_Name'
FROM Employee e1 RIGHT JOIN Employee e2 ON e1.emp_id = e2.emp_id
Keep in mind sometimes you want e1.manager_id > e2.id
请记住，有时您需要 e1.manager_id > e2.id
The advantage to knowing both scenarios is sometimes you have a ton of WHERE or JOIN conditions and you want to place your self join conditions in the other clause to keep your code readable.
了解这两种情况的好处是有时您有大量的 WHERE 或 JOIN 条件，并且您希望将自连接条件放在另一个子句中以保持代码可读。
No one addressed what happens when an Employee does not have a manager. Huh? They are not included in the result set. What if you want to include employees that do not have managers but you don't want incorrect combinations returned?
没有人提到当员工没有经理时会发生什么。嗯？它们不包含在结果集中。如果您想包括没有经理但不希望返回错误组合的员工怎么办？
Try this puppy;
试试这只小狗；
SELECT e1.emp_id AS 'Emp_ID'
, e1.emp_name AS 'Emp_Name'
, e2.emp_id AS 'Manager_ID'
, e2.emp_name AS 'Manager_Name'
FROM Employee e1 LEFT JOIN Employee e2
ON e1.emp_id = e2.emp_id
AND e1.emp_name = e2.emp_name
AND e1.every_other_matching_column = e2.every_other_matching_column
回答by Steven Stewart-Gallus
One use case is checking for duplicate records in a database.
一个用例是检查数据库中的重复记录。
SELECT A.Id FROM My_Bookings A, My_Bookings B
WHERE A.Name = B.Name
AND A.Date = B.Date
AND A.Id != B.Id
