SQL 自联接 vs 内联接

self join vs inner join

提问by Nilesh Kumar

what is difference between self join and inner join

自联接和内联接有什么区别

回答by John Carrell

I find it helpful to think of all of the tables in a SELECT statement as representing their own data sets.

我发现将 SELECT 语句中的所有表都视为代表它们自己的数据集会很有帮助。

Before you've applied any conditions you can think of each data set as being complete (the entire table, for instance).

在应用任何条件之前，您可以认为每个数据集都是完整的（例如，整个表）。

A join is just one of several ways to begin refining those data sets to find the information that you really want.

联接只是开始优化这些数据集以查找您真正需要的信息的几种方法之一。

Though a database schema may be designed with certain relationships in mind (Primary Key <-> Foreign Key) these relationships really only exist in the context of a particular query. The query writer can relate whatever they want to whatever they want. I'll give an example of this later...

尽管在设计数据库模式时可能会考虑到某些关系（主键 <-> 外键），但这些关系实际上只存在于特定查询的上下文中。查询编写者可以将他们想要的任何内容与他们想要的任何内容相关联。稍后我会举一个例子...

---

---

An INNER JOIN relates two tables to each other. There are often multiple JOIN operations in one query to chain together multiple tables. It can get as complicated as it needs to. For a simple example, consider the following three tables...

INNER JOIN 将两个表相互关联。在一个查询中通常有多个 JOIN 操作来将多个表链接在一起。它可以根据需要变得复杂。对于一个简单的例子，请考虑以下三个表......

STUDENT

| STUDENTID | LASTNAME | FIRSTNAME |
------------------------------------
      1     |  Smith   |   John
      2     |  Patel   |  Sanjay
      3     |   Lee    |  Kevin
      4     |  Hymanson |  Steven

---

---

ENROLLMENT

| STUDENTID | CLASSID |
-----------------------
      2     |    3
      3     |    1
      4     |    2

---

---

CLASS

| CLASSID | COURSE | PROFESSOR |
--------------------------------
     1    | CS 101 |   Smith
     2    | CS 201 |  Ghandi
     3    | CS 301 |  McDavid
     4    | CS 401 |  Martinez

The STUDENT table and the CLASS table were designed to relate to each other through the ENROLLMENT table. This kind of table is called a Junction Table.

STUDENT 表和 CLASS 表旨在通过 ENROLLMENT 表相互关联。这种表称为连接表。

To write a query to display all students and the classes in which they are enrolled one would use twoinner joins...

要编写一个查询来显示所有学生和他们注册的班级，将使用两个内部联接...

SELECT stud.LASTNAME, stud.FIRSTNAME, class.COURSE, class.PROFESSOR
FROM STUDENT stud
INNER JOIN ENROLLMENT enr
    ON stud.STUDENTID = enr.STUDENTID
INNER JOIN CLASS class
    ON class.CLASSID = enr.CLASSID;

Read the above closely and you should see what is happening. What you will get in return is the following data set...

仔细阅读以上内容，您应该会看到发生了什么。您将得到的回报是以下数据集...

 | LASTNAME | FIRSTNAME | COURSE | PROFESSOR |
 ---------------------------------------------
     Patel  |   Sanjay  | CS 301 |  McDavid
      Lee   |   Kevin   | CS 101 |   Smith
    Hymanson |  Steven   | CS 201 |  Ghandi

Using the JOIN clauses we've limited the data sets of all three tables to only those that match each other. The "matches" are defined using the ONclauses. Note that if you ran this query you would notsee the CLASSID 4 row from the CLASS table or the STUDENTID 1 row from the STUDENT table because those IDs don't exist in the matches (in this case the ENROLLMENT table). Look into "LEFT"/"RIGHT"/"FULL OUTER" JOINs for more reading on how to make that work a little differently.

使用 JOIN 子句，我们将所有三个表的数据集限制为仅相互匹配的数据集。“匹配”是使用ON子句定义的。请注意，如果你运行这个查询，你将不会看到从CLASS表或从学生表STUDENTID 1行中的CLASSID 4行，因为这些ID不会在比赛中存在（在这种情况下，报名表）。查看“LEFT”/“RIGHT”/“FULL OUTER” JOIN 以获得更多关于如何使其工作有所不同的阅读。

Please note, per my comments on "relationships" earlier, there is no reasonwhy you couldn't run a query relating the STUDENT table and the CLASS table directly on the LASTNAME and PROFESSOR columns. Those two columns match in data type and, well look at that! They even have a value in common! This would probably be a weird data set to get in return. My point is it can be done and you never know what needs you might have in the future for interesting connections in your data. Understand the design of the database but don't think of "relationships" as being rules that can't be ignored.

请注意，根据我之前对“关系”的评论，没有理由不能直接在 LASTNAME 和 PROFESSOR 列上运行与 STUDENT 表和 CLASS 表相关的查询。这两列在数据类型上匹配，看看吧！他们甚至有一个共同的价值！这可能是一个奇怪的数据集作为回报。我的观点是它是可以做到的，而且您永远不知道将来可能需要什么才能在数据中建立有趣的连接。了解数据库的设计，但不要将“关系”视为不可忽视的规则。

In the meantime... SELF JOINS!

与此同时……自我加入！

---

---

Consider the following table...

考虑下表...

PERSON

| PERSONID | FAMILYID |  NAME  |
--------------------------------
      1    |     1    |  John
      2    |     1    | Brynn
      3    |     2    | Arpan
      4    |     2    | Steve
      5    |     2    |  Tim
      6    |     3    | Becca

If you felt so inclined as to make a database of all the people you know and which ones are in the same family this might be what it looks like.

如果你很想建立一个你认识的所有人的数据库，哪些人属于同一个家庭，这可能就是它的样子。

If you wanted to return one person, PERSONID 4, for instance, you would write...

例如，如果您想返回一个人，例如 PERSONID 4，您会写...

SELECT * FROM PERSON WHERE PERSONID = 4;

You would learn that he is in the family with FAMILYID 2. Then to find allof the PERSONs in his family you would write...

您会了解到他与 FAMILYID 2 同在一个家庭中。然后要找到他家中的所有PERSON，您会写...

SELECT * FROM PERSON WHERE FAMILYID = 2;

Done and done! SQL, of course, can accomplish this in one query using, you guessed it, a SELF JOIN.

大功告成！当然，SQL 可以在一个查询中使用（您猜对了）SELF JOIN 来完成此操作。

What really triggers the need for a SELF JOINhere is that the table contains a unique column (PERSONID) and a column that serves as sort of a "Category" (FAMILYID). This concept is called Cardinalityand in this case represents a one to many or 1:Mrelationship. There is only oneof each PERSONbut there are manyPERSONsin a FAMILY.

真正触发这里需要SELF JOIN 的是该表包含一个唯一列 (PERSONID) 和一个充当“类别”(FAMILYID) 的列。这个概念称为基数，在这种情况下代表一对多或 1:M 的关系。只有一个每PERSON但也有很多人在家庭。

So, what we want to return is allof the members of a family if onemember of the family's PERSONID is known...

所以，如果知道一个家庭的 PERSONID 成员，我们想要返回的是一个家庭的所有成员......

SELECT fam.*
FROM PERSON per
JOIN PERSON fam
    ON per.FamilyID = fam.FamilyID
WHERE per.PERSONID = 4;

Here's what you would get...

这就是你会得到的......

| PERSONID | FAMILYID |  NAME  |
--------------------------------
      3    |     2    | Arpan
      4    |     2    | Steve
      5    |     2    |  Tim

Let's note a couple of things. The words SELF JOINdon't occur anywhere. That's because a SELF JOINis just a concept. The word JOINin the query above could have been a LEFT JOINinstead and different things would have happened. The point of a SELF JOINis that you are using the same table twice.

让我们注意几件事。SELF JOIN这个词不会出现在任何地方。那是因为SELF JOIN只是一个概念。上面查询中的JOIN一词可能是一个LEFT JOIN而不是会发生不同的事情。SELF JOIN的要点是您两次使用同一个表。

Consider my soapbox from before on data sets. Here we have started with the data set from the PERSON table twice. Neither instanceof the data set affects the other one unless we say it does.

在数据集上考虑我以前的肥皂盒。在这里，我们从 PERSON 表中的数据集开始了两次。数据集的任何一个实例都不会影响另一个实例，除非我们说它会影响。

Let's start at the bottom of the query. The perdata set is being limited to only those rows where PERSONID = 4. Knowing the table we know that will return exactly one row. The FAMILYID column in that row has a value of 2.

让我们从查询的底部开始。在每个数据集被仅限于那些行，其中PERSONID = 4，知道上表中我们知道，将返回一行。该行中的 FAMILYID 列的值为 2。

In the ON clause we are limiting the famdata set (which at this point is still the entire PERSON table) to only those rows where the value of FAMILYID matches one or moreof the FAMILYIDs of the perdata set. As we discussed we know the perdata set only has one row, therefore one FAMILYID value. Therefore the famdata set now contains only rows where FAMILYID = 2.

在ON子句我们限制FAM数据集（其在这一点上仍是整个PERSON表），只有那些行，其中FAMILYID的值相匹配的一个或多个所述的FAMILYIDs的每数据集。正如我们所讨论的，我们知道每个数据集只有一行，因此有一个 FAMILYID 值。因此，fam数据集现在只包含 FAMILYID = 2 的行。

Finally, at the top of the query we are SELECTing all of the rows in the famdata set.

最后，在查询的顶部，我们选择了fam数据集中的所有行。

Voila! Two queries in one.

瞧！两个查询合二为一。

---

---

In conclusion, an INNER JOINis one of several kinds of JOIN operations. I would stronglysuggest reading further into LEFT, RIGHT and FULL OUTER JOINs (which are, collectively, called OUTER JOINs). I personally missed a job opportunity for having a weak knowledge of OUTER JOINs once and won't let it happen again!

总之，INNER JOIN是几种 JOIN 操作之一。我强烈建议进一步阅读 LEFT、RIGHT 和 FULL OUTER JOINs（统称为OUTER JOINs）。我个人曾经因为对 OUTER JOIN 的了解不足而错过了一个工作机会，并且不会让它再次发生！

A SELF JOINis simply any JOIN operation where you are relating a table to itself. The way you choose to JOIN that table to itself can use an INNER JOINor an OUTER JOIN.Note that with a SELF JOIN, so as not to confuse your SQL engine you mustuse table aliases (fam and per from above. Make up whatever makes sense for your query) or there is no way to differentiate the different versionsof the same table.

一个自联接简直是任何连接，你是与一个表与自身操作。您选择将该表与其自身 JOIN 的方式可以使用INNER JOIN或OUTER JOIN。请注意，使用SELF JOIN 时，为了不混淆您的 SQL 引擎，您必须使用表别名（上面的 fam 和 per。弥补对您的查询有意义的任何内容），否则无法区分同一表的不同版本.

Now that you understand the difference open your mind nice and wide and realize that one single query could contain all different kinds of JOINs at once. It's just a matter of what data you want and how you have to twist and bend your query to get it. If you find yourself running one query and taking the result of that query and using it as the input of another query then you can probably use a JOINto make it one query instead.

既然您了解了差异，就会大开眼界，并意识到一个查询可以同时包含所有不同类型的 JOIN。这只是您想要什么数据以及您必须如何扭曲和弯曲查询以获取它的问题。如果您发现自己正在运行一个查询并获取该查询的结果并将其用作另一个查询的输入，那么您可能可以使用JOIN来使其成为一个查询。

To play around with SQL try visiting W3Schools.comThere is a locally stored database there with a bunch of tables that are designed to relate to each other in various ways and it's filled with data! You can CREATE, DROP, INSERT, UPDATE and SELECT all you want and return the database back to its default at any time. Try all sorts of SQL out to experiment with different tricks. I've learned a lot there, myself.

要使用 SQL，请尝试访问W3Schools.com那里有一个本地存储的数据库，里面有一堆表，这些表旨在以各种方式相互关联，并且里面充满了数据！您可以根据需要创建、删除、插入、更新和选择所有内容，并随时将数据库恢复为默认值。尝试各种 SQL 以试验不同的技巧。我自己在那里学到了很多东西。

Sorry if this was a little wordy but I personally struggled with the concept of JOINs when I was starting to learn SQL and explaining a concept by using a bunch of other complex concepts bogged me down. Best to start at the bottom sometimes.

对不起，如果这有点罗嗦，但当我开始学习 SQL 并通过使用一堆其他复杂概念来解释一个概念时，我个人对 JOIN 的概念感到困惑。有时最好从底部开始。

I hope it helps. If you can put JOINs in your back pocket you can work magic with SQL!

我希望它有帮助。如果你能把 JOIN 放在你的后口袋里，你就可以用 SQL 施展魔法！

Happy querying!

祝查询愉快！

回答by Justin Cave

A self join joins a table to itself. The employeetable might be joined to itself in order to show the manager name and the employee name in the same row.

自联接将表与自身联接。employee为了在同一行中显示经理姓名和员工姓名，该表可能会连接到自身。

An inner join joins any two tables and returns rows where the key exists in both tables. A self join can be an inner join (most joins are inner joins and most self joins are inner joins). An inner join can be a self join but most inner joins involve joining two different tables (generally a parent table and a child table).

内连接连接任意两个表并返回键存在于两个表中的行。自联接可以是内联接（大多数联接是内联接，大多数自联接是内联接）。内连接可以是自连接，但大多数内连接涉及连接两个不同的表（通常是父表和子表）。

回答by Nikhil Khanna

An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.

内部连接（有时称为简单连接）是两个或多个表的连接，只返回满足连接条件的行。

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition.

自联接是表与自身的联接。此表在 FROM 子句中出现两次，后跟限定连接条件中列名的表别名。为了执行自联接，Oracle 数据库组合并返回满足联接条件的表行。