blocks|key|1808467|text|您可以将自联接视为两个相同的表。但是在标准化中，您不能创建表的两个副本，因此您只能使用自联接来模拟两个表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1808468|假设您有两个表：|1808469|表emp1|offset|length|style|CODE|1808470|Id+Name+Boss_id++++++++++++
1+++ABC+++3+++++++++++++++++++
2+++DEF+++1+++++++++++++++++++
3+++XYZ+++2+++++++++++++++++++|code-block|syntax|javascript|1808471|表emp2|1808472|Id+Name+Boss_id++++++++++++
1+++ABC+++3+++++++++++++++++++
2+++DEF+++1+++++++++++++++++++
3+++XYZ+++2+++++++++++++++++++|1808473|现在，如果您想要获取每个员工的姓名和他或她的老板的姓名：|1808474|select+c1.Name+,+c2.Name+As+Boss
from+emp1+c1
++++inner+join+emp2+c2+on+c1.Boss_id+=+c2.Id|1808475|它将输出下表：|1808476|Name++Boss
ABC+++XYZ
DEF+++ABC
XYZ+++DEF|1808477|entityMap^0|0|0|1|4|0|0|1|4|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|14|8|@$F|15|G|16|H|I]]|9|@]|A|$]]|$1|J|3|K|5|L|7|17|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|18|8|@$F|19|G|1A|H|I]]|9|@]|A|$]]|$1|Q|3|R|5|L|7|1B|8|@]|9|@]|A|$M|N]]|$1|S|3|T|5|6|7|1C|8|@]|9|@]|A|$]]|$1|U|3|V|5|L|7|1D|8|@]|9|@]|A|$M|N]]|$1|W|3|X|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|L|7|1F|8|@]|9|@]|A|$M|N]]|$1|10|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|11|$]]

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:

<h3>Table <code>emp1</code></h3>

<pre><code>Id Name Boss_id 
1 ABC 3 
2 DEF 1 
3 XYZ 2 
</code></pre>

<h3>Table <code>emp2</code></h3>

<pre><code>Id Name Boss_id 
1 ABC 3 
2 DEF 1 
3 XYZ 2 
</code></pre>

Now, if you want to get the name of each employee with his or her boss' names:

<pre><code>select c1.Name , c2.Name As Boss
from emp1 c1
 inner join emp2 c2 on c1.Boss_id = c2.Id
</code></pre>

Which will output the following table:

<pre><code>Name Boss
ABC XYZ
DEF ABC
XYZ DEF
</code></pre>

blocks|key|2021937|text|当您有一个引用自身的表时，这是很常见的。示例:+employee表，其中每个员工都可以有一位经理，而您希望列出所有员工及其经理的姓名。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2021938|SELECT+e.name,+m.name
FROM+employees+e+LEFT+OUTER+JOIN+employees+m
ON+e.manager+=+m.id|code-block|syntax|javascript|2021939|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>SELECT e.name, m.name
FROM employees e LEFT OUTER JOIN employees m
ON e.manager = m.id
</code></pre>

blocks|key|1808408|text|假设有一个名为Employee的表，如下所述。所有员工都有一个经理，经理也是员工(也许首席执行官除外，他的manager_id将为空)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1808409|Table+(Employee):+

int+id,
varchar+name,
int+manager_id|code-block|syntax|javascript|1808410|然后，您可以使用以下select来查找所有员工及其经理：|1808411|select+e1.name,+e2.name+as+ManagerName
from+Employee+e1,+Employee+e2+where
where+e1.manager_id+=+e2.id|1808412|entityMap^0|7|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

Imagine a table called <code>Employee</code> as described below. All employees have a manager which is also an employee (maybe except for the CEO, whose manager_id would be null)

<pre><code>Table (Employee): 

int id,
varchar name,
int manager_id
</code></pre>

You could then use the following select to find all employees and their managers:

<pre><code>select e1.name, e2.name as ManagerName
from Employee e1, Employee e2 where
where e1.manager_id = e2.id
</code></pre>

blocks|key|2021941|text|如果您的表是自引用的，则它们很有用。例如，对于一个页面表格，每个页面可以有一个next和previous链接。这些将是同一个表中其他页面的ID。如果您希望获得三个连续的页面，那么可以在next和previous列上使用同一个表的id列进行两次自联接。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2021942|entityMap^0|13|4|18|8|2K|4|2P|8|36|2|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|G|$]]

They are useful if your table is self-referential. For example, for a table of pages, each page may have a <code>next</code> and <code>previous</code> link. 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 <code>next</code> and <code>previous</code> columns with the same table's <code>id</code> column.

blocks|key|2022009|text|如果没有表引用自身的能力，我们将不得不为层次结构级别创建与层次结构中的层数一样多的表。但是由于该功能是可用的，所以您将表连接到自身，并且sql将其视为两个单独的表，因此所有内容都可以很好地存储在一个位置。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2022010|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|2022126|text|除了上面提到的答案(这些答案已经解释得很好了)，我还想加一个例子，这样就可以很容易地展示Self+Join的用法。假设您有一个名为CUSTOMERS的表，它具有以下属性:+CustomerID、CustomerName、ContactName、城市和国家。现在，您想要列出所有来自“同一城市”的人。你必须想出一个这个表的复制品，这样我们就可以根据城市来加入他们。下面的查询将清楚地显示它的含义：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2022127|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;|code-block|syntax|javascript|2022128|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, 
A.City
FROM Customers A, Customers B
WHERE A.CustomerID &lt;&gt; B.CustomerID
AND A.City = B.City 
ORDER BY A.City;
</code></pre>

blocks|key|1808517|text|这里有许多正确的答案，但也有一个同样正确的变体。可以将联接条件放在join语句中，而不是WHERE子句中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1808518|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|code-block|syntax|javascript|1808519|请记住，有时您需要e1.manager_id+>+e2.id|1808520|了解这两种情况的好处是，有时您有大量的WHERE或JOIN条件，并且希望将自连接条件放在other子句中，以保持代码的可读性。|1808521|没有人提到当一个员工没有经理时会发生什么。哈?它们不包括在结果集中。如果您希望包括没有经理的员工，但又不希望返回错误的组合，该怎么办？|1808522|试试这只小狗；|1808523|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|1808524|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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.

<pre><code>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
</code></pre>

Keep in mind sometimes you want 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.

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;

<pre><code>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
</code></pre>

blocks|key|2022097|text|一种用例是检查数据库中的重复记录。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2022098|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|code-block|syntax|javascript|2022099|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

One use case is checking for duplicate records in a database.

<pre><code>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
</code></pre>

blocks|key|2022259|text|当您必须对表的数据本身求值时，自联接非常有用。这意味着它将关联同一个表中的行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2022260|Syntax:++SELECT+*+FROM++TABLE+t1,+TABLE+t2+WHERE+t1.columnName+=+t2.columnName|offset|length|style|CODE|2022261|例如，我们想要查找初始指定等于当前指定的员工的姓名。我们可以通过以下方式使用self+join来解决这个问题。|2022262|SELECT+NAME+FROM+Employee+e1,+Employee+e2+WHERE+e1.intialDesignationId+=+e2.currentDesignationId|code-block|syntax|javascript|2022263|entityMap^0|0|0|26|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|V|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Self-join is useful when you have to evaluate the data of the table with itself. Which means it'll correlate the rows from the same table.

<code>Syntax: SELECT * FROM TABLE t1, TABLE t2 WHERE t1.columnName = t2.columnName</code>

For example, we want to find the names of the employees whose Initial Designation equals to current designation. We can solve this using self join in following way.

<pre><code>SELECT NAME FROM Employee e1, Employee e2 WHERE e1.intialDesignationId = e2.currentDesignationId
</code></pre>

blocks|key|1808418|text|它相当于数据库中的链表/树，其中一行包含对另一行的引用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1808419|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

It's the database equivalent of a linked list/tree, where a row contains a reference in some capacity to another row.

blocks|key|1808505|text|下面是对self+join的外行解释。自连接不是一种不同类型的连接。如果您了解其他类型的联接(内部联接、外部联接和交叉联接)，则自联接应该是直接的。在内部连接、外部连接和交叉连接中，您可以连接2个或更多不同的表。然而，在self+join中，您使用itslef连接同一个表。在这里，我们没有两个不同的表，但使用表别名将同一个表视为不同的表。如果这一点仍然不清楚，我建议你看看下面的youtube视频。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1808506|Self+Join+with+an+example|offset|length|1808507|entityMap|0|LINK|mutability|MUTABLE|url|http://www.youtube.com/watch?v=qnYSN_7qwgg^0|0|0|P|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

Here is the exaplanation of self join in layman terms. Self join is not a different type of join. If you have understood other types of joins (Inner, Outer, and Cross Joins), then self join should be straight forward. In INNER, OUTER and CROSS JOINS, you join 2 or more different tables. However, in self join you join the same table with itslef. Here, we don't have 2 different tables, but treat the same table as a different table using table aliases. If this is still not clear, I would recomend to watch the following youtube videos.

<a href="http://www.youtube.com/watch?v=qnYSN_7qwgg" rel="nofollow">Self Join with an example</a>

I don't understand the need for self-joins. Can someone please explain them to me?

A simple example would be very helpful.

Explanation of self-joins

数据库

我不明白为什么需要自连接。有人能给我解释一下吗？举一个简单的例子会很有帮助。

问自连接的解释
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问自连接的解释EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问自连接的解释
EN