自连接的解释

Question

自连接的解释

sqlself-join

98

我不理解自连接的必要性。有人能给我解释一下吗？

一个简单的例子会非常有帮助。

- Alex Gordon

自连接是用来做什么的？ - philipxy

14个回答

23

当你有一个表格引用自身时，这是非常常见的情况。例如：员工表中每个员工都可以有一个经理，并且您想列出所有员工及其经理的名称。

SELECT e.name, m.name
FROM employees e LEFT OUTER JOIN employees m
ON e.manager = m.id

- windyjonas

21

自连接是将一个表与其本身进行连接的操作。

常见的使用情况是当表存储实体（记录）之间存在层次关系时。例如，一个包含个人信息（姓名、出生日期、地址等）的表，其中包括一列包含父亲（和/或母亲）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

我们可以在同一查询中获取关于子代、父亲（和母亲，在第二个自连接等）甚至祖父母等的信息。

- mjv

5

假设你有一个表users，设置如下：

用户ID
用户名
用户经理的ID

在这种情况下，如果您想要在一个查询中提取用户信息和管理者信息，您可以这样做：

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

- ceejayoz

5

想象一张名为Employee的表格，如下所述。所有员工都有一个经理，这个经理也是一个员工（也许除了CEO，他的manager_id可能为空）。

Table (Employee): 

int id,
varchar name,
int manager_id

您可以使用以下语句查询所有雇员及其经理：

select e1.name, e2.name as ManagerName
from Employee e1, Employee e2 where
where e1.manager_id = e2.id

- Klaus Byskov Pedersen

4

除了上面提到的答案（已经很好地解释了），我想再举一个例子，以便可以轻松展示自连接的使用。假设您有一个名为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;

- Mazhar MIK

3

这个回答非常重要，因为在SO上有很多SQL问题的答案是“使用自连接”，当人们没有明确的（分层）自我参考时，他们往往看不到这一点。 - JimmyB

2

尽管这是从w3schools复制的，但我认为上面的答案没有解释自连接而是内连接，两者是不同的。 - George K

4

如果您的表是自引用的，则它们非常有用。例如，对于一个页面表，每个页面可能都有一个next和previous链接。这些将是相同表中其他页面的ID。如果您想获取连续三个页面的三元组，您需要使用相同表的id列在next和previous列上进行两次自连接。

- Max Shawabkeh

4

如果表不能引用自身，我们就必须为层次结构中的每个级别创建同样数量的表。但是由于这种功能可用，您可以将表连接到自身，并且sql将其视为两个单独的表，因此所有内容都存储在一个地方。

- Eugene

但是现在你（希望）明白了如果没有自我引用会发生什么。 - Eugene

3

这里有许多正确的答案，但同样正确的一种变体是：您可以将连接条件放在联接语句中而不是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

记住有时候你想要 e1.manager_id > e2.id。

知道这两种情况的优点是，有时你有很多 WHERE 或 JOIN 条件，想把自己连接条件放在其他子句中，以保持代码的可读性。

没有人提到当员工没有经理时会发生什么。嗯？他们不会被包括在结果集中。如果你想包括没有经理的员工，但又不想返回不正确的组合，该怎么办？

试试这个吧;

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

- BClaydon

1

嗯，在这个程序中，为什么你使用“大于”而不是“等于”来进行连接呢？ - Marcel

1

嗨。我看到一些示例使用“FROM xxx，yyy WHERE”，而其他一些使用“FROM xxx JOIN yyy WHERE”。你能解释一下区别吗？ - skan

@Skan 这是一个非常好的问题。简短的答案是，这是旧的速记方法，将被弃用。我在十多年前的学校里使用过它，但很少在实践中看到它。这是我能找到的最简洁的描述：http://www.bidn.com/blogs/KathiKellenberger/sql-server/2875/inner-join-vs-comma-join - BClaydon

2

自连接在你需要评估表格数据本身时非常有用。这意味着它将关联来自同一表格的行。

语法：SELECT * FROM TABLE t1, TABLE t2 WHERE t1.columnName = t2.columnName

例如，我们想要找到其初始职称等于当前职称的员工姓名。我们可以使用以下方式通过自连接解决此问题。

"最初的回答"

SELECT NAME FROM Employee e1, Employee e2 WHERE e1.intialDesignationId = e2.currentDesignationId

- Sumanth Varada

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- pointlesspolitics · Accepted Answer

您可以将自连接视为两个相同的表。但在规范化中，您不能创建表的两个副本，因此您只能使用自连接模拟具有两个表的情况。

假设您有两个表：

表`emp1`

Id Name Boss_id            
1   ABC   3                   
2   DEF   1                   
3   XYZ   2

表格`emp2`

Id Name Boss_id            
1   ABC   3                   
2   DEF   1                   
3   XYZ   2

现在，如果你想获取每个员工的姓名以及他或她的上级名称：

select c1.Name , c2.Name As Boss
from emp1 c1
    inner join emp2 c2 on c1.Boss_id = c2.Id

将输出以下表格：

Name  Boss
ABC   XYZ
DEF   ABC
XYZ   DEF

自连接的解释

表emp1

表格emp2

表`emp1`

表格`emp2`