logo标签列表

SQL连接作为维恩图

mysqlsqlvisualizationset-theory
87

我在理解SQL中的连接操作时遇到了一些问题，后来发现了这张图片，我想这可能会有所帮助。但问题是我并没有完全理解它。例如，图片右上角的连接，它将整个B圆圈染成红色，但只有与A重叠的部分也被染成红色。图片让人感觉B圆圈是SQL语句的主要焦点，但SQL语句本身却通过以A开始（从A选择，连接B）传达相反的印象，即A将是SQL语句的焦点。

同样，下面的图片仅包含来自B圆圈的数据，那么为什么还需要在连接语句中加入A呢？

问题：顺时针从右上方开始并结束于中心，请有人提供有关每个SQL图像表示的更多信息，解释：

a) 为什么每种情况都需要连接（例如，在没有从A或B获取任何数据的情况下，即仅对A或B进行染色的情况）

b) 以及任何其他细节，可以澄清为什么该图像是SQL的良好表示方式

sql join diagram

8
这个图示可能更有帮助 [连接操作的视觉解释] (http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html)，其中包括表格数据和每种连接类型的描述。 - Taryn
在右上图中重要的是它是一个RIGHT JOIN，所以TableB的所有数据（即在JOIN语句右侧的表）都将被包含，只有在TableA中与TableB中的键匹配的数据才会被包含。 - Melanie
2
Venn图问题在于，一旦你理解了连接，它们 起初似乎说明了 什么，但如果你真的试图解释这样一个图表的含义，那么你会发现它是复杂的，涉及到一个未被提及的ON条件，涉及到不在任何操作数中的行，并且对于内部连接和外部连接有不同的解释。（特别地，尽管它们看起来像 Venn 图，但它们并没有像 Venn 图那样被解释），也就是说，它们不说明，更不用说解释了。 - philipxy
请查看我的许多评论，链接为 What is the difference between "INNER JOIN" and "OUTER JOIN"?。有一个与左连接、右连接、全连接和内部连接相关的特定的维恩图可用于说明。 - philipxy
2
抛弃维恩图，更好地解释JOIN操作 - Alex Jones
显示剩余2条评论
5个回答
82

我同意Cade关于Venn图的局限性。更适当的视觉表现可能是下面这个。

表格

Tables

SELECT A.Colour, B.Colour FROM A CROSS JOIN B SQL Fiddle

交叉连接（或笛卡尔积）产生一个由两个表中每个行的所有组合构成的结果。每个表都有4行，因此结果中会有16行。

Cross Join

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour SQL Fiddle

内连接逻辑上返回与连接条件匹配的交叉连接的所有行。在这种情况下，有五个行匹配。

Inner Join

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN ('Green','Blue') SQL Fiddle

内连接条件不一定是相等条件，也不一定引用来自两个（甚至任何一个）表的列。在交叉连接的每一行上计算A.Colour NOT IN ('Green','Blue')会返回以下结果。

inner 2

1=1作为内连接条件，将对交叉连接中的每一行求值为真，因此两者是等价的 (SQL Fiddle)。

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour SQL Fiddle

外部连接的逻辑评估方式与内部连接相同，除非左表格（对于左连接）中的某行根本没有与右手表格中的任何行连接起来，否则它将在结果中保留，并且带有右手列的NULL值。

LOJ

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL SQL Fiddle

这个查询只是将前一个结果限制为仅返回B.Colour IS NULL的行。在这种特殊情况下，这些将是保留的行，因为它们在右手表格中没有匹配，并且查询返回在表B中没有匹配的单个红色行。这被称为反半连接。

选择用于IS NULL测试的列非常重要，它必须是不可为空的列，或者连接条件必须确保排除任何NULL值，以使此模式能够正确工作并避免只将具有该列的NULL值和未匹配的行带回。

loj is null

从A表和B表中选择颜色列，通过右外连接(RIGHT OUTER JOIN) ON条件为A表的颜色列等于B表的颜色列 SQL Fiddle

与左外连接类似，右外连接可以保留右表中不匹配的行，并将左边的列扩展为空值。

ROJ

从A表和B表中选择颜色列，通过全外连接(FULL OUTER JOIN) ON条件为A表的颜色列等于B表的颜色列 SQL Fiddle

全外连接结合了左连接和右连接的行为，并且保留了左表和右表中不匹配的行。

FOJ

6
感谢你提供的贴切的视觉表现，以及帮助我丰富词汇量。 - Smandoli
1
@MartinSmith 这是一个很好的解释，但我在使用SQL fiddle时遇到了问题。模式中没有列出任何内容。我以前从未使用过SQL fiddle。 - onebree
1
@onebree 看起来 SQL Fiddle 不能无限期地保存脚本 :-( - Martin Smith
3
最好的解释，谢谢！应该有一些法律禁止使用维恩图来解释连接 - 我被它们欺骗了很多时间！ - Tertium
Venn图对于新手来说非常混乱...在解释SQL JOIN时完全没有用。他们只是将整个过程视为集合 - 如交集、并集、补集等，但他们绝对不会详细说明或暗示实现细节（将细节留给学生的想象力）。- 这些带有颜色的图表消除了所有的困惑，非常直观。我认为所有教授的内容都应该如此清晰明了。 - enthusiasticgeek
这篇文章很好，在解释JOIN时请不要使用Venn图。 - Alex Jones
15
我认为你主要的混淆在于，当（例如）只有A被标记为红色时，你误认为“查询仅返回来自A的数据”，但实际上它的意思是“查询仅返回那些A有记录的情况下的数据”。查询可能仍包含来自B的数据。（对于没有记录的B的情况，查询将替换为NULL。）
类似地，下面的图像仅包括来自B圆的数据，那么为什么A还会出现在连接语句中呢？
如果您是指那张图像 - A完全是白色的图像，并且对于不与A重叠的B部分有一个红色新月形，则A出现在查询中的原因是，A是如何找到需要排除的B记录的。(如果查询中没有出现A，那么Venn图将不会显示A，它只会显示B，而且没有办法区分所需记录和不需要的记录。)
图像使得B圆似乎是SQL语句的主要焦点，但是通过以A开始（从A选择，加入B），SQL语句本身传达了相反的印象，即A将成为SQL语句的焦点。
因此，RIGHT JOIN相对较少使用；虽然使用LEFT JOIN的查询几乎总是可以重新排列以改用RIGHT JOIN（反之亦然），但通常人们会使用LEFT JOIN编写他们的查询，而不是使用RIGHT JOIN。
13

Venn图表适用于表示集合操作，例如UNION、INTERSECT、EXCEPT等。

只有在那些像使用LEFT JOIN WHERE rhs.KEY is NULL这样的方式来模拟EXCEPT等集合操作时，该图表才是准确的。否则它就是误导性的。例如，任何连接都可能导致行数倍增，如果连接条件不是1:1的话。但是集合只允许包含不同的成员，因此这些不能被表示为集合操作。

还有交叉连接或INNER JOIN ON 1 = 1-这既不类比于此图表中显示的INNER JOIN，也无法用Venn图表真正描述所产生的集合。更不用说所有其他可能的三角连接、自连接和反连接了：

lhs INNER JOIN rhs ON rhs.VALUE < lhs.VALUE (triangular)
或者
SELF self1
INNER JOIN SELF self2
    ON self2.key <> self1.key
    AND self1.type = self2.type

（使用自交叉和反连接查找除自己以外的所有相似家庭成员 - self1和self2是相同的集合，结果是真子集）

仅在键上进行连接可能在教程的前几分钟内还可以，但这会导致学习连接相关内容的不良路径。 我认为这就是您发现的问题所在。

需要抛弃这种Venn图可以通常代表JOINs的想法。

4
我一直非常讨厌这种使用维恩图的方法；我觉得它在直观上代表了连接方面已经很简单的部分，但对于那些真正让人困惑的连接方面却毫无帮助。 - ruakh
还有一个适当的维恩图，说明内部/左侧/右侧/完全连接结果之间的区别。请参见我在问题上的评论。 - philipxy
-1

当你进行连接操作时，很可能你的两个表不完全匹配。具体来说，A 中可能有一些行与 B 中的任何内容都不匹配，或者 A 中有重复的行与 B 中的单个行匹配，反之亦然。

当出现这种情况时，你可以选择：

  1. 对于每个 A，如果有一个可用的 B，则选择一个（左上）
  2. 选择完全匹配的每一对（丢弃任何缺少 A 或 B 的对 - 中间）
  3. 对于每个 B，如果有一个可用的 A，则选择一个（右上）
  4. 选择 EVERYTHING（全部选择 - 左下）

中间的左右是技术上的连接，但是没有意义；它们可能可以更有效地编写为 SELECT <select_list> FROM TableA A WHERE A.Key NOT IN (SELECT B.Key FROM TableB B)（或相反）。

作为对你困惑的直接回答，RIGHT JOIN 表示 "以下表达式是此查询的重点"。 右下角相当奇怪，我不明白你为什么要这样做。它返回两个外部中间查询的结果，混合在一起，并在相反表格的所有列中带有 NULL。
回复：“中心左右技术上是连接，但是没有意义；它们可能可以更有效地编写为SELECT <select_list> FROM TableA A WHERE A.Key NOT IN (SELECT B.Key FROM TableB B)（或相反）”：我不认为这是正确的。有一些DBMSes它们是等效的，有一些DMBSes其中“LEFT JOIN”方法更有效（例如，MySQL直到5.5或5.6左右）；但我不知道任何DBMSes其中“NOT IN”方法可靠地更有效，特别是如果“B”很大。此外，“NOT IN”方法仅在单个字段上连接时才起作用。 - ruakh
有趣；我本以为逐行检查会更有效率，而不是连接它然后检查连接是否成功。但我完全可以看出用多个键可能更快。 - zebediah49
@ruakh - 在SQL Server中，对于非空列，“NOT IN”和“NOT EXISTS”通常具有相同的计划，并且此计划可以比“LOJ ... NULL”更有效（有时会在连接后过滤掉那些匹配“IS NULL”的分离过滤器）。虽然“NOT IN”与NULL的语义或从未是我想要的，但我总是更喜欢使用“NOT EXISTS”，因为如果可为空，则更有效 ，而且“NOT IN”的语义。 - Martin Smith
我忘记了 NOT EXISTS--那才是我真正寻找的，但我却用 NOT IN 来笨拙地模仿它。 - zebediah49
-1
对于右连接，是的语法可能会让人感到困惑，但它确实是看起来的样子。当你说“TableA RIGHT JOIN TableB”时，确实是在说TableB是你要引用的主表，而TableA只是挂在那里，有匹配记录的地方。这在查询中确实很奇怪，因为TableA排在第一位，所以你的大脑自动将更多的优先级分配给它，即使TableB实际上是查询中更重要的表。因此，出于这个原因，在实际代码中很少看到右连接。
所以，我们不使用A和B，而是使用两个容易跟踪的东西。假设我们有两个人员信息表，ShoeSize和IQ。你有一些人的鞋码信息，一些人的智商信息。并且在两个表上都有一个PersonID可以连接。
从右上角开始顺时针（即使这里开始有一些更复杂和牵强的情况）：
  • ShoeSize RIGHT JOIN IQ -> 给我所有的IQ信息。如果我们有这些人的鞋码信息，请包括在内。
  • ShoeSize RIGHT JOIN IQ WHERE ShowSize.PersonID = NULL -> 给我所有的IQ信息，但仅限于没有任何鞋码信息的人
  • ShoeSize FULL OUTER JOIN IQ WHERE ShoeSize.PersonID = NULL AND IQ.PersonID = NULL -> 为那些没有IQ信息的人提供鞋码信息，以及为那些没有鞋码信息的人提供IQ信息
  • ShoeSize FULL OUTER JOIN IQ -> 给我所有的东西，所有的鞋码和所有的IQ数据。如果任何鞋码和IQ记录具有相同的PersonID，请将它们包含在一行中。
  • ShoeSize LEFT JOIN IQ WHERE IQ.PersonID = NULL -> 给我所有的鞋码信息，但仅限于没有IQ信息的人
  • ShoeSize LEFT JOIN IQ -> 给我所有的鞋码信息。如果我们有这些人的IQ信息，请包括在内。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接