[转]self-join

原文：https://www.cnblogs.com/javafun/archive/2008/04/02/1133793.html

 

自联接就是您自己加入表格时的情况。没有SELF JOIN关键字，您只需编写一个普通连接，其中连接中涉及的两个表都是同一个表。需要注意的一点是，当您自行加入时，必须为表使用别名，否则表名称将不明确。当您想要关联同一个表中的行对（例如父子关系）时，它非常有用。以下查询返回“Kitchen”类别的所有直接子类别的名称。SELECT T2.nameFROM category T1JOIN category T2ON T2.parent = T1.idWHERE T1.name = 'Kitchen'

________________________________

Ref: http://www.udel.edu/evelyn/SQL-Class3/SQL3_self.html

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table.  One practical use for self-joins:  obtaining running counts and running totals in an SQL query.

To write the query, select from the same table listed twice with different aliases, set up the comparison, and eliminate cases where a particular value would be equal to itself.

Example

Which customers are located in the same state (column name is Region)?  Type this statement in the SQL window:

SELECT DISTINCT c1.ContactName, c1.Address, c1.City, c1.Region

FROM Customers AS c1, Customers AS c2

WHERE c1.Region = c2.Region

AND c1.ContactName <> c2.ContactName

ORDER BY c1.Region, c1.ContactName;

The result should look like this:

Exercise