MYSQL 自联接如何工作？

How does a MYSQL Self-Join Work?

提问by Nick Woodhams

I recently asked a question about Self-Joins and I got a great answer.

我最近问了一个关于自联接的问题，我得到了一个很好的答案。

The query is meant to find the ID, Start Date, and Price of Event2, Following Event1 by 1 Day.

该查询旨在查找事件 2 的 ID、开始日期和价格，在事件 1 之后 1 天。

The code WORKS fine. But I don't understand HOW.

代码工作正常。但我不明白如何。

Could someone explain as thoroughly as you can- what the different parts of the query are and what they do?

有人可以尽可能彻底地解释一下 - 查询的不同部分是什么以及它们做什么？

SELECT event2.id, event2.startdate, event2.price
FROM mm_eventlist_dates event1
JOIN mm_eventlist_dates event2 
ON event2.startdate = date_add(event1.enddate, INTERVAL 1 DAY)
WHERE event1.id=$id

I really appreciate your help, for whatever reason I'm having a really hard time wrapping my head around this.

我真的很感谢您的帮助，无论出于何种原因，我都很难解决这个问题。

回答by djna

The way I'd try to understand this is to write out two lists on piece one labelled event1 and one event2. Then list a few records in each list (the lists will be identical) now start at the WHERE in the description below.

我试图理解这一点的方法是在标记为 event1 和 event2 的一块上写出两个列表。然后在每个列表中列出一些记录（列表将是相同的）现在从下面描述中的 WHERE 开始。

We're taking data from two tables (OK the same table used twice, but try to ignore that for the moment)

我们从两个表中获取数据（好吧，同一个表使用了两次，但暂时忽略它）

FROM mm_eventlist_dates event1
JOIN mm_eventlist_dates event2 

It probably helps to read the rest from the bottom up.

自下而上阅读其余部分可能会有所帮助。

  WHERE event1.id=$id

So we want the record from event1 that has the specified record id. Presumably that's exactly one record. Now we figure out the day after that event ended.

所以我们想要来自 event1 的具有指定记录 id 的记录。据推测，这恰好是一项记录。现在我们弄清楚该事件结束后的第二天。

 date_add(event1.enddate, INTERVAL 1 DAY)

Now that tells us the records from event2, they need to start on that date,

现在告诉我们 event2 的记录，它们需要从那个日期开始，

ON event2.startdate = date_add(event1.enddate, INTERVAL 1 DAY)

We now have two records identified, what fields do we want?

我们现在已经确定了两条记录，我们想要哪些字段？

SELECT event2.id, event2.startdate, event2.price

Oh, just the fields from the one whose start date we figured out.

哦，只是我们计算出开始日期的字段。

回答by Macros

When you create a join in a query you are literally doing that - joining 2 tables together. This can be 2 different or 2 of the same tables, it doesn't matter. When specifying a join, creating an alias for the table (a name that refers to it in the rest of the query) is useful if the tables are different, and essential if they are the same. Your query is taking table 1 (event1) which has the columns:

当您在查询中创建连接时，您实际上就是在这样做 - 将 2 个表连接在一起。这可以是 2 个不同的或 2 个相同的表，没关系。指定连接时，如果表不同，则为表创建别名（在查询的其余部分中引用它的名称）很有用，如果它们相同则必不可少。您的查询采用表 1 (event1)，其中包含以下列：

event1.id, event1.startdate, event1.price

and joining table 2 (event2):

并加入表2（事件2）：

event2.id, event2.startdate, event2.price

which leaves you with the result set:

这给你留下了结果集：

event1.id, event1.startdate, event1.price, event2.id, event2.startdate, event2.price

The criteria for the join is specified as:

连接的条件指定为：

ON event2.startdate = date_add(event1.enddate, INTERVAL 1 DAY)

which is saying 'For each row in event1 join the row(s) in event2 that has a startdate of 1 day after the startdate in event1'

这是说“对于 event1 中的每一行，加入 event2 中的行，这些行的 startdate 在 event1 中的 startdate 之后 1 天”

Then as you are only interested in the data for one event the where clause limits the result set.

然后，由于您只对一个事件的数据感兴趣，where 子句限制了结果集。

WHERE event1.id=$id

Finally, as you don't need the information from event1 about the original event your select statement simply selects the event2 columns from the resultset:

最后，由于您不需要 event1 中有关原始事件的信息，因此您的 select 语句只需从结果集中选择 event2 列：

SELECT event2.id, event2.startdate, event2.price

回答by Scott

A self join works by referencing the same table twice using different selection criteria. Think of each reference to the table as a different "Virtual Table" created by filtering the original table. Generally, one of the tables is "filtered" using the WHERE clause and the second is "filtered" in the join clause. This is the most efficient way to do it, it is also possible to "filter" both in the join clause.

自联接的工作原理是使用不同的选择标准两次引用同一个表。将对该表的每个引用视为通过过滤原始表创建的不同“虚拟表”。通常，其中一个表使用 WHERE 子句“过滤”，第二个表在连接子句中“过滤”。这是最有效的方法，也可以在连接子句中“过滤”两者。

So we have two virtual tables based on data in the same underlying table and they are joined together as though they were two totally separate tables.

所以我们有两个基于同一个底层表中数据的虚拟表，它们被连接在一起，就好像它们是两个完全独立的表一样。

The crux of it is that you store data in one table that takes on slightly different meaning based on context.

关键在于您将数据存储在一个表中，该表根据上下文具有略有不同的含义。

Consider a table of people, each with a unique id, and a column for father

考虑一张人表，每个人都有一个唯一的 id 和一个父亲的列

    id   name    fatherID
    1    Joseph  [null]
    2    Greg     1

    SELECT child.name as childName, father.name as fatherName
        FROM people as child
        INNER JOIN people as father on (child.fatherID = father.id)  

Would yield 1 row

将产生 1 行

    childName   fatherName
    Greg       Joseph

回答by Vinko Vrsalovic

A join combines two tables based on a certain criteria. The criteria is what follows the ON clause.

联接根据特定条件组合两个表。标准是遵循 ON 子句的内容。

If you join the a table with itself, it effectively is the same as creating a copy of the table, rename it and perform the join with that copy.

如果您将 a 表与其自身连接，它实际上与创建表的副本、重命名并使用该副本执行连接相同。

For example

例如

Table foo         Table bar
+---+---+---+     +---+---+---+
| a | b | c |     | a | d | e |
+---+---+---+     +---+---+---+
| 1 | 2 | 3 |     | 1 | 0 | 0 |
+---+---+---+     +---+---+---+
| 1 | 3 | 4 |     | 2 | 9 | 3 |
+---+---+---+     +---+---+---+
| 1 | 3 | 5 |
+---+---+---+
| 2 | 4 | 6 |
+---+---+---+

If we do

如果我们这样做

select * from foo join bar on (foo.a = bar.a and foo.c > 4)

we end up with

我们最终得到

foo join bar on (foo.a = bar.a and foo.c > 4)
+---+---+---+---+---+
| a | b | c | d | e |
+---+---+---+---+---+
| 1 | 3 | 5 | 0 | 0 |
+---+---+---+---+---+
| 2 | 4 | 6 | 9 | 3 |
+---+---+---+---+---+

Now,

现在，

SELECT event2.id, event2.startdate, event2.price 
FROM mm_eventlist_dates event1                     
JOIN mm_eventlist_dates event2 
ON event2.startdate = date_add(event1.enddate, INTERVAL 1 DAY)
WHERE event1.id=$id

that query follows the same principle, but with two instances of the table mm_eventlist_dates, one aliased as event1 and the other as event2. We can think of this like having two tables and perform the join just as in the real two tables scenario.

该查询遵循相同的原则，但具有表 mm_eventlist_dates 的两个实例，一个别名为 event1，另一个别名为 event2。我们可以把这想象成有两个表并执行连接，就像在真正的两个表场景中一样。

The join criteria in this case is that for table event2, the startdate matches the enddate plus one day of table event1.

这种情况下的连接条件是，对于表 event2，startdate 匹配 enddate 加上表 event1 的一天。

The where clause restricts over what is the join performed, in this case it performs the join only over the rows of event1 table having the supplied id.

where 子句限制了所执行的连接，在这种情况下，它仅对具有提供的 id 的 event1 表的行执行连接。