自然连接的维恩图 [英] Venn Diagram for Natural Join
问题描述
我一直试图完全理解sql join的概念，维恩图对我帮助很大.我发现它们适用于所有类型的联接，但不适用于自然联接.
I've been trying to understand the concept of sql joins fully, venn diagrams have helped me a lot to do that. I've found them for all kind of joins but not for natural joins.
用于自然连接的维恩图会是什么样子?
How would a venn diagram for a natural join look like?
推荐答案
Venn图对于理解自然联接或内部联接不是很有帮助.与Stack Overflow上的联接相关联的大多数Venn图都是伪造的毫无价值的错误陈述，即使在Venn图可能有用的情况下也是如此.
Venn diagrams are not very helpful for understanding natural join or inner join. Most Venn diagrams associated with joins on Stack Overflow are parroted worthless misrepresentations--even in cases where a Venn diagram could be useful.
以下是使用Venn图进行SQL自然连接的一些有效用法:
Here are some valid uses of Venn diagrams for SQL natural join:
如果忽略列顺序，我们可以使一个区域成为一个集合，其元素是关联表的列名.然后左&右圆圈的元素是左&右表的列名&组合的元素是结果的列名.
If you ignore column order, we can have an area be a set whose elements are an associated table's column names. Then the left & right circles' elements are the left & right tables' column names & the combined elements are the result's column names.
如果具有相同名称的输入表列具有相同的类型，则可以设置一个区域，该区域的元素是出现在表中某列的公共列的子行值.然后左&右圆圈的元素是左&右表的此类子行值&相交元素就是结果的此类子行值.
If input table columns with the same name have the same type then we can have an area be the set whose elements are the subrow values that appear somewhere in a table for the common columns. Then the left & right circles' elements are the left & right tables' such subrow values & the intersection elements are the result's such subrow values.
但是图和对都不告诉我们输出行是什么.
But neither diagram nor the pair tells us what the output rows are.
根据我在在SQL Server 2008中的交叉联接与内部联接的回答:
维恩图带有两个相交圆的维恩图可以说明输出行之间的差异，用于内部，左侧，右侧和右侧.相同输入的完整联接.当ON无条件为TRUE时，INNER JOIN结果与CROSS JOIN相同.它还可以说明输入&输出行，用于INTERSECT，UNION和除了.并且当两个输入具有相同的列时，INTERSECT结果与标准SQL NATURAL JOIN相同，而EXCEPT结果与某些涉及LEFT& amp;的惯用法相同.正确加入.但这并没有说明(INNER)JOIN的总体工作原理.乍一看似乎很合理.它可以为ON，PK(主键)，FK(外键)和/或SELECT的特殊情况识别输入和/或输出的部分.您要做的就是识别圆圈所代表的集合中的元素到底是什么. (请记住，那些混乱的演示文稿永远不会弄清楚.)(请记住，对于联接，输出行的标题与输入行的标题不同.)
Re Venn diagrams A Venn diagram with two intersecting circles can illustrate the difference between output rows for INNER, LEFT, RIGHT & FULL JOINs for the same input. And when the ON is unconditionally TRUE, the INNER JOIN result is the same as CROSS JOIN. Also it can illustrate the input & output rows for INTERSECT, UNION & EXCEPT. And when both inputs have the same columns, the INTERSECT result is the same as for standard SQL NATURAL JOIN, and the EXCEPT result is the same as for certain idioms involving LEFT & RIGHT JOIN. But it does not illustrate how (INNER) JOIN works in general. That just seems plausible at first glance. It can identify parts of input and/or output for special cases of ON, PKs (primary keys), FKs (foreign keys) and/or SELECT. All you have to do to see this is to identify what exactly are the elements of the sets represented by the circles. (Which muddled presentations never make clear.) (Remember that in general for joins output rows have different headings from input rows.)
我重点强调:
但是它不说明了(INNER)JOIN的总体工作原理.
But it does not illustrate how (INNER) JOIN works in general.
要做的就是识别圆圈所代表的集合中的元素到底是什么.
All you have to do to see this is to identify what exactly are the elements of the sets represented by the circles.
来自我的评论(使用密钥为其内部连接的维恩图图1":
From my comments (using "key" in the sense of "legend") on an answer re its Venn diagram "Figure 1" for inner join:
图1是常见的可怕解释JOIN的尝试.它的密钥也很复杂:仅适用于表集&仅等分&仅一个[列]；它也代表输入而不是输出.通常为JOIN编写 it .
Figure 1 is a common terrible attempt to explain JOIN. Its key is also complex: It's only for tables as sets & only equijoin & only one [column]; it also represents the input differently than the output. Write it for JOIN in general.
从我对:
Venn图显示集合中的元素.只需尝试准确地确定这些图中的集合是什么以及元素是什么.集不表和元素不它们的行.另外，任何两个表都可以连接，因此PK& FK是无关紧要的.所有伪造的.您正在做其他成千上万的工作-误以为是含糊的印象 假设是有意义的.
Venn diagrams show elements in sets. Just try to identify exactly what the sets are and what the elements are in these diagrams. The sets aren't the tables and the elements aren't their rows. Also any two tables can be joined, so PKs & FKs are irrelvant. All bogus. You are doing just what thousands of others have done--got a vague impression you (wrongly) assume makes sense.
Of the answers & comments & their references below only one actually explains how Venn diagrams represent the operators: The circle intersection area represents the set of rows in A JOIN B. The area unique to each circle represents the set of rows you get by taking its table's rows that don't participate in A JOIN B and adding the columns unique to the other table all set to NULL. (And most give a vague bogus correspondence of the circles to A and B.)
因此，维恩图与某些情况有关，在某些情况下，可以合理地认为表包含行值元素的集合. 但是在一般情况下，SQL表不包含行值元素集，而维恩图表示集.
So Venn diagrams are relevant for certain cases where tables can reasonably be considered to hold sets of row-valued elements. But in general SQL tables do not hold sets of row-valued elements, while Venn diagrams denote sets.
通过维恩图重新说明内部联接和外部联接:
Re illustrating inner vs outer joins via Venn diagrams:
来自我对SQL Server中的LEFT JOIN与LEFT OUTER JOIN的评论
关于维恩图:如果没有输入空值或重复的行，则我们可以将一个表作为一组行值&使用正常的数学=，那么维恩图就可以了-以圆圈按住左&右连接输出表/集.但是，如果输入空值或重复的行，那么很难解释什么圆圈是&的集合.这些集如何与输入&维恩图对输出表/袋没有帮助.
Re Venn diagrams: If no nulls or duplicate rows are input, so we can take a table to be a set of row-valued values & use normal math =, then the Venn diagrams are OK--taking circles to hold left & right join output tables/sets. But if nulls or duplicate rows are input then it is so difficult to explain just what the circles are sets of & how those sets relate to input & output tables/bags that Venn diagrams are not helpful.
在我对我的答案的评论中，"INNER JOIN"和"OUTER JOIN"之间有什么区别?
尽管我的措辞很快，但我必须承认这一点，因为SQL涉及bag& null和SQL文化没有通用的术语来命名&区分相关的概念，甚至清楚地说明输出输出为行"的维恩图的元素如何为1:1也是很重要的，更不用说输入行"了.或内部或外部联接的作用，更不用说它们之间的区别了. 值"可以包含NULL，也可以不包含NULL，行"可以是值与表值或变量&中的槽的列表. "="可能是SQL"=" vs相等.
I must admit that, despite my quick phrasing in comments, because SQL involves bags & nulls and SQL culture doesn't have common terminology to name & distinguish between relevant notions, it is non-trivial even to explain clearly how elements of a Venn diagram are 1:1 with output "rows", let alone input "rows". Or what inner or outer joins do, let alone their difference. "value" may or may not include NULL, "row" may be a list of values vs a slot in a table value or variable & "=" may be SQL "=" vs equality.
PS通常当它们确实是 Venn图. ://en.wikipedia.org/wiki/Euler_diagram"rel =" nofollow noreferrer>欧拉图.
PS Often diagrams are called Venn diagrams when they are really Euler diagrams.
这篇关于自然连接的维恩图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
