自我加入vs内部加入 [英] self join vs inner join
问题描述
自我联接和内部联接有什么区别
what is difference between self join and inner join
推荐答案
我认为将SELECT语句中的所有表视为代表自己的数据集很有帮助.
I find it helpful to think of all of the tables in a SELECT statement as representing their own data sets.
在应用任何条件之前，您可以认为每个数据集都是完整的(例如整个表).
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...
INNER JOIN将两个表相互关联.一个查询中经常有多个JOIN操作将多个表链接在一起.它可能会变得非常复杂.对于一个简单的示例，请考虑以下三个表...
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...
STUDENT
| STUDENTID | LASTNAME | FIRSTNAME |
------------------------------------
1 | Smith | John
2 | Patel | Sanjay
3 | Lee | Kevin
4 | Jackson | 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
STUDENT表和CLASS表设计为通过ENROLLMENT表相互关联.这种表称为 Junction Table (连接表).
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.
要编写一个查询以显示所有学生及其注册的班级，将使用两个内部联接...
To write a query to display all students and the classes in which they are enrolled one would use two inner 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
Jackson | Steven | CS 201 | Ghandi
使用JOIN子句，我们将所有三个表的数据集限制为仅彼此匹配的数据集. 匹配"是使用 ON 子句定义的.请注意，如果您运行此查询，您将不看到CLASS表中的CLASSID 4行或STUDENT表中的STUDENTID 1行，因为这些ID在匹配项中不存在(在这种情况下，登记表).查看向左"/向右"/外部全部"连接，以获取更多有关如何使工作方式有所不同的信息.
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 ON clauses. Note that if you ran this query you would not see 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.
请注意，根据我之前对关系"的评论，没有没有理由，为什么您不能直接在LASTNAME和PROFESSOR列上运行与STUDENT表和CLASS表相关的查询.这两列的数据类型相匹配，请看一下！它们甚至具有共同的价值！这可能是一个奇怪的数据集，以作为回报.我的观点是可以做到的，而且您永远都不知道将来对于数据中有趣的连接可能会有什么需求.理解数据库的设计，但不要将关系"视为不可忽视的规则.
Please note, per my comments on "relationships" earlier, there is no reason why 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.
与此同时...自我加入！
In the meantime... SELF JOINS!
请考虑下表...
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.
如果您想返回一个人，例如PERSONID 4，您应该写...
If you wanted to return one person, PERSONID 4, for instance, you would write...
SELECT * FROM PERSON WHERE PERSONID = 4;
您会发现他与家族2息息相关.然后查找他的家人中的所有所有，您会写......
You would learn that he is in the family with FAMILYID 2. Then to find all of the PERSONs in his family you would write...
SELECT * FROM PERSON WHERE FAMILYID = 2;
完成并完成！当然，SQL可以使用一个SELF JOIN在一个查询中完成此操作.
Done and done! SQL, of course, can accomplish this in one query using, you guessed it, a SELF JOIN.
真正触发需要 SELF JOIN 的真正原因是该表包含一个唯一列(PERSONID)和一个用作类别"(FAMILYID)的列.这个概念称为基数，在这种情况下代表一对多或1:M 关系.每个 PERSON 中只有一个，但是家庭中有很多 PERSONs
What really triggers the need for a SELF JOIN here is that the table contains a unique column (PERSONID) and a column that serves as sort of a "Category" (FAMILYID). This concept is called Cardinality and in this case represents a one to many or 1:M relationship. There is only one of each PERSON but there are many PERSONs in a FAMILY.
因此，如果已知该家庭成员的一个成员，我们将返回该家庭成员的全部 ...
So, what we want to return is all of the members of a family if one member of the family's PERSONID is known...
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
让我们注意几件事. SELF JOIN 一词在任何地方都不会出现.这是因为 SELF JOIN 只是一个概念.上面的查询中的单词 JOIN 可能是 LEFT JOIN ，因此会发生不同的情况. SELF JOIN 的要点是您使用同一张表两次.
Let's note a couple of things. The words SELF JOIN don't occur anywhere. That's because a SELF JOIN is just a concept. The word JOIN in the query above could have been a LEFT JOIN instead and different things would have happened. The point of a SELF JOIN is that you are using the same table twice.
在数据集上考虑一下我的肥皂盒.在这里，我们从PERSON表中的数据集开始了两次.除非我们说，数据集的实例都不会影响另一个.
Consider my soapbox from before on data sets. Here we have started with the data set from the PERSON table twice. Neither instance of the data set affects the other one unless we say it does.
让我们从查询的底部开始. 每数据集仅限于PERSONID = 4的那些行.知道该表后，我们知道该表将仅返回一行.该行中的FAMILYID列的值为2.
Let's start at the bottom of the query. The per data 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.
在ON子句中，我们将 fam 数据集(目前仍然是整个PERSON表)限制为仅FAMILYID值与一个或多个<数据集的FAMILYID.正如我们所讨论的，我们知道 per 数据集只有一行，因此只有一个FAMILYID值.因此，家族数据集现在仅包含FAMILYID = 2的行.
In the ON clause we are limiting the fam data set (which at this point is still the entire PERSON table) to only those rows where the value of FAMILYID matches one or more of the FAMILYIDs of the per data set. As we discussed we know the per data set only has one row, therefore one FAMILYID value. Therefore the fam data set now contains only rows where FAMILYID = 2.
最后，在查询的顶部，我们选择 fam 数据集中的所有行.
Finally, at the top of the query we are SELECTing all of the rows in the fam data set.
Voila！两个查询合而为一.
Voila! Two queries in one.
最后， INNER JOIN 是几种JOIN操作之一.我会强烈建议进一步阅读"LEFT"，"RIGHT"和"FULL OUTER JOIN"(统称为" OUTER JOINs ").我个人因为一次对OUTER JOIN的了解不足而错过了一个工作机会，并且不会再让它再次发生！
In conclusion, an INNER JOIN is one of several kinds of JOIN operations. I would strongly suggest 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!
SELF JOIN 就是将表与其自身相关联的任何JOIN操作.您选择将表联接到自身的方式可以使用 INNER JOIN 或外部联接.请注意，使用 SELF JOIN 不要混淆您的SQL引擎，您必须使用表别名(fam和per上面的内容.对您的查询进行有意义的补充)，或者无法区分不同的版本 >在同一张桌子上.
A SELF JOIN is 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 JOIN or an OUTER JOIN. Note that with a SELF JOIN, so as not to confuse your SQL engine you must use table aliases (fam and per from above. Make up whatever makes sense for your query) or there is no way to differentiate the different versions of the same table.
现在，您了解了这种差异，从而使您的思想开阔了，并意识到一个查询可以一次包含所有不同种类的JOIN.这仅取决于您想要什么数据，以及如何扭曲和弯曲查询才能获取它.如果您发现自己正在运行一个查询并将该查询的结果用作另一个查询的输入，则可以使用 JOIN 使其成为一个查询.
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 JOIN to make it one query instead.
要尝试使用SQL，请尝试访问 W3Schools.com 是一个本地存储的数据库，其中有一堆表，这些表旨在以各种方式相互关联，并且其中充满了数据！您可以随意创建，删除，插入，更新和选择所有内容，并随时将数据库恢复为默认状态.尝试各种SQL尝试不同的技巧.我自己在那儿学到了很多东西.
To play around with SQL try visiting W3Schools.com There 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.
抱歉，这有点罗word，但是当我开始学习SQL并通过使用许多其他复杂概念来解释一个概念时，我个人为JOINs的概念而苦恼.有时最好从底部开始.
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.
我希望它会有所帮助.如果您可以将JOIN放在后面的口袋里，则可以使用SQL魔术！
I hope it helps. If you can put JOINs in your back pocket you can work magic with SQL!
查询愉快！
这篇关于自我加入vs内部加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
