怎么办呢 LEFT JOIN， RIGHT JOIN 和 FULL JOIN 适合？
问题 “INNER JOIN”和“OUTER JOIN”有什么区别？
5772
2017-09-01 22:36
起源
下面的答案和评论及其参考文献 只有一个 实际上解释了维恩图如何表示运算符：圆圈交叉区域表示A JOIN B中的行集。每个圆圈唯一的区域表示通过获取其不参与A JOIN B的表格行所获得的行集合并将另一个表唯一的列添加到NULL。 （而且大多数人给出A和B的圆圈模糊的虚假对应关系。） - philipxy
已经提供了很多答案，但我还没有看到这个教程。如果您了解维恩图，这是一个很棒的教程： blog.codinghorror.com/a-visual-explanation-of-sql-joins 对我来说，它简洁到足以快速阅读，但仍然掌握整个概念，并很好地处理所有情况。如果您不知道维恩图是什么 - 学习它们。花5到10分钟这样做，并且无论何时您需要可视化使用集合和管理集合上的操作，它们都会有所帮助。 - DanteTheSmith
@DanteTheSmith不，这个问题与图中的问题相同。请参阅上面的评论以及以下问题以及以下博客文章：“杰夫在评论中拒绝了他的博客几页”。维恩图显示了集合中的元素。只是尝试确定这些图中的集合是什么以及元素是什么。集合 不 表格和元素 不 他们的行。也可以连接任何两个表，因此PK和FK是不重要的。所有 虚假。 你正在做的成千上万的人已经做了 - 得到了 模糊的印象 你（错误的） 承担 说得通。 - philipxy
有人可以参考时间序列索引数据来回答这个问题 - 当涉及到仅仅毫秒不同的时间戳时，内部和外部联接通常不会在人/维恩意义上起作用。 - yeliabsalohcin
@yeliabsalohcin阅读本页的评论（以及我的回答）。如果没有重复的行，那么维恩图就说明了这一点 区别 在外部和内部连接之间，所以它们说明外部连接 就......而言 内连接，反之亦然 如果你知道他们做了什么。但维恩图 别 说明外连接或内连接返回的内容 就他们的投入而言 除非输入没有重复的行且具有相同的列。 （如果您有不同的问题，请将其作为问题发布。应用程序无关紧要。“匹配”很重要，这取决于数据类型和模式。） - philipxy
答案:
假设您正在加入没有重复的列，这是一种非常常见的情况：
A和B的内连接给出A交叉B的结果，即a的内部 维恩图 路口。
A和B的外连接给出了A联合B的结果，即维恩图联合的外部部分。
例子
假设您有两个表，每个表都有一个列，数据如下：
A B
- -
1 3
2 4
3 5
4 6
注意，（1,2）对于A是唯一的，（3,4）是常见的，并且（5,6）对于B是唯一的。
内部联接
使用任一等价查询的内部联接给出了两个表的交集，即它们共有的两行。
select * from a INNER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4
左外连接
左外连接将给出A中的所有行，以及B中的所有公共行。
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a = b.b(+);
a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4
右外连接
右外连接将给出B中的所有行，以及A中的任何常见行。
select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a(+) = b.b;
a | b
-----+----
3 | 3
4 | 4
null | 5
null | 6
全外连接
完整的外连接将为您提供A和B的并集，即A中的所有行和B中的所有行。如果A中的某些内容在B中没有相应的数据，那么B部分为空，而副反之亦然。
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5
5428
2017-09-01 22:59
通过在表B中添加值为4的另一行来扩充示例将是很好的。这将显示内部联接不需要在行的相等数量上。 - softveda
一个很好的解释，但是这句话： A和B的外连接给出了A联合B的结果，即维恩图联合的外部部分。 没有准确的措辞。除了下列之一外，外连接将给出A交叉B的结果：全部A（左连接），全部B（右连接）或全部A和全部B（完全连接）。只有这最后一个场景真的是一个联盟B.但是，一个写得很好的解释。 - Thomas
亲的答案！喜欢它。顺便说一下：我仍然找不到LEFT JOIN和LEFT OUTER JOIN等之间的差异。如果这不是问题的一部分，我可以发布一个新的:) - YvesR
我是对的，FULL JOIN是FULL OUTER JOIN的别名，LEFT JOIN是LEFT OUTER JOIN的别名吗？ - Damian
@Ameer，谢谢。 Join不保证订单，您需要添加ORDER BY子句。 - Mark Harrison
2443
2018-05-16 23:03
注意：MySQL中没有FULL OUTER JOIN。 stackoverflow.com/questions/12473210/... - Michael Ozeryansky
我认为这个图表假设没有重复 Key，意思 Key 是独特的。如果 Key 不是唯一的，我认为结果将是一个十字架，返回计数将远远高于A的大小。 - AlikElzin-kilaka
在排除了交集部分的FULL OUTER JOIN示例中，不会使用'A.Key IS NULL'排除A中的所有行，因为如果Key为NULL，那么根据定义该行不存在？我没有看到该机制如何实现所示VENN图的结果。 - Kelly S. French
@ KellyS.French请参阅我对该问题的评论以及解释如何阅读维恩图的其他答案。这些区域不包含表A和B中的行。它们包含通过配对A中的行和B中的行组成的某些行。这些行也不是SELECT语句输出的偶数行。维恩图的答案都很差。 - philipxy
A代表所有 潜在 来自tableA的行，B代表所有 潜在 来自tableB的行。图中的红色区域标记这些行中的哪一行实际上将包含在结果集中。请注意，可能（通常是）有很多甚至更多 潜在 来自表的行比该表中的实际行多。这是因为tableA中的任何行都是 可能 对于引用它的tableB中的每一行发生一次。这被称为 笛卡尔积。 - Stijn de Witt
591
2017-08-30 11:52
这个图对这个概念有点误导。阅读帖子中的评论。 - softveda
@ ya23：全外连接是什么意思？ - ursitesion
除了杰夫 否定 他的博客在评论中写了几页：“评论者指出图表在多重或重复结果的情况下分解，是绝对正确的。我实际上是在考虑连接主键，这往往是唯一的定义，虽然这些例子没有这样表达。就像笛卡儿或十字架产品一样，任何导致比你最初开始的行更多的行都绝对会破坏整个维恩图的概念。所以请记住这一点。 - philipxy
@philipxy是的，维恩图类比仅在忽略NULL后才能用于主键 - 这通常没有说明。维恩图无法解释结果集中的其他列如何填充NULL。 - Galax
@Galax不幸的是，连接是否在键上（更不用说主键）无助于图表有意义。 （尽管我引用Jeff说他认为它确实如此。）（我不明白你的“忽略NULL后主键的工作”或“结果集中的其他列填充了NULL”。我知道更多或者少了 分类 你试图说的但是任何信件，但我对这个问题的评论中的那个是 复杂 等等 明确 说明。虽然我会欢迎一个。究竟 什么 正在分组 怎么样 圈子？ - philipxy
维恩图并没有真正为我做这件事。
例如，它们没有显示交叉连接和内连接之间的任何区别，或者更一般地显示不同类型的连接谓词之间的任何区别，或者提供用于推理它们将如何操作的框架。
理解逻辑处理是无可替代的，无论如何都要相对简单。
- 想象一下交叉连接。
- 评估
on对来自步骤1的所有行保留谓词计算结果的子句true - （仅适用于外部联接）在步骤2中丢失的任何外部行中添加回来。
（注意：在实践中，查询优化器可能会找到比上面的纯逻辑描述更有效的执行查询的方法，但最终结果必须相同）
我将从一个动画版开始 全外连接。进一步说明如下。
说明
来源表
首先从a开始 CROSS JOIN （AKA笛卡尔积）。这没有 ON 子句并简单地返回两个表中的每个行组合。
SELECT A.Colour，B.Colour from A CROSS JOIN B
内部和外部联接具有“ON”子句谓词。
- 内部联接。 为交叉连接结果中的所有行评估“ON”子句中的条件。如果为true则返回连接的行。否则丢弃它。
- 左外连接。 与内部联接相同，然后对于左表中任何不匹配的行，将这些行输出为右表列的NULL值。
- 正确的外部加入。 与内部联接相同，然后对于右表中任何不匹配的行，将这些行输出为左表列的NULL值。
- 完全外部加入。 与内部联接相同，然后保留左侧外部联接中的左侧非匹配行，按右侧外部联接保留右侧非匹配行。
一些例子
SELECT A.Colour，B.Colour from A INNER JOIN B ON A.Colour = B.Colour
以上是经典的equi join。
动画版
SELECT A.Colour，B.Colour from A INNER JOIN B ON A.Colour NOT IN（'Green'，'Blue'）
内连接条件不一定是相等条件，也不需要引用来自两个（或甚至任何一个）表的列。评估 A.Colour NOT IN ('Green','Blue') 在交叉连接的每一行返回。
选择A.Colour，B.Colour from INNER JOIN B ON 1 = 1
对于交叉连接结果中的所有行，连接条件的计算结果为true，因此这与交叉连接相同。我不会再重复16行的图片了。
SELECT A.Colour，B.Colour from a LEFT OUTER JOIN B ON A.Colour = B.Colour
外连接的逻辑评估方式与内连接的方式相同，只是如果左表中的一行（左连接）不与右表中的任何行连接，它将保留在结果中 NULL 右栏的值。
SELECT A.Colour，B.Colour from a LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL
这只是将前一个结果限制为只返回行所在的位置 B.Colour IS NULL。在这种特殊情况下，这些将是保留的行，因为它们在右侧表中没有匹配，并且查询返回表中不匹配的单个红色行 B。这被称为反半连接。
选择一列是很重要的 IS NULL 测试不是可空的，或者连接条件确保任何 NULL 将排除这些值以使此模式正常工作，并避免只返回碰巧有的行 NULL 除了未匹配的行之外，该列的值。
SELECT A.Colour，B.Colour from A ROU OUTER JOIN B ON A.Colour = B.Colour
右外连接的作用类似于左外连接，除了它们保留右表中的非匹配行，并且null扩展左侧列。
选择A.Colour，B.Colour从一个完整的外部加入B A.Colour = B.Colour
完全外连接组合了左连接和右连接的行为，并保留左表和右表的不匹配行。
选择A.Colour，B.Colour from A FULL OUTER JOIN B ON 1 = 0
交叉连接中的任何行都不匹配 1=0 谓词。使用常规外部联接规则保留两侧的所有行，并在另一侧的表中使用NULL。
SELECT COALESCE（A.Colour，B.Colour）AS颜色来自一个完整的外部连接B ON 1 = 0
通过对前面的查询的一个小修改，可以模拟a UNION ALL 这两张桌子。
SELECT A.Colour，B.Colour from a LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour ='Green'
请注意 WHERE 子句（如果存在）在连接后逻辑运行。一个常见错误是执行左外连接，然后在右表上包含一个WHERE子句，该子句最终排除不匹配的行。以上结果执行外连接...
...然后“Where”子句运行。 NULL= 'Green' 不评估为true，因此外部联接保留的行最终被丢弃（与蓝色一起），有效地将联接转换回内部联接。
如果意图仅包括B中的行，其中Color为绿色，而所有来自A的行都不包括正确的语法
SELECT A.Colour，B.Colour from a LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour ='Green'
SQL小提琴
见这些例子 在SQLFiddle.com上运行。
529
2017-12-13 11:58
我会说虽然这对我来说几乎和维恩图一样不合适，但我欣赏人们的不同和学习方式不同，这是一个非常好的解释，不像我以前见过的那样，所以我支持@ypercube in奖励积分。同样很好的工作解释了在JOIN子句和WHERE子句中添加附加条件的区别。感谢你，马丁史密斯。 - Old Pro
@OldPro我认为维恩图是可以的，但是他们对如何表示交叉连接或区分一种连接谓词（例如equi join）与另一种连接谓词没有关系。在交叉连接结果的每一行上评估连接谓词的心智模型，如果外部连接并最终评估哪个位置对我更有效，则将其添加回不匹配的行。 - Martin Smith
维恩图表适用于表示联盟和交叉点和差异但不能加入。它们对于非常简单的连接具有一些较小的教育价值，即连接条件在唯一列上的连接。 - ypercubeᵀᴹ
@Arth - 没错，你错了。 SQL小提琴 sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/5155 这是维恩图无法说明的。 - Martin Smith
@MartinSmith哇，我同意，我完全错了！太习惯与一对一工作..感谢纠正。 - Arth
以下摘自文章“MySQL - LEFT JOIN和RIGHT JOIN，INNER JOIN和OUTER JOIN“格雷厄姆埃利斯在他的博客马口上。
在像MySQL这样的数据库中，数据被分成许多表，然后连接（Joined）一起来 JOIN 在 SELECT 用于从多个表中读取记录的命令。阅读此示例以了解其工作原理。
首先，一些样本数据：
people
mysql> select * from people;
+------------+--------------+------+
| name | phone | pid |
+------------+--------------+------+
| Mr Brown | 01225 708225 | 1 |
| Miss Smith | 01225 899360 | 2 |
| Mr Pullen | 01380 724040 | 3 |
+------------+--------------+------+
3 rows in set (0.00 sec)
property
mysql> select * from property;
+------+------+----------------------+
| pid | spid | selling |
+------+------+----------------------+
| 1 | 1 | Old House Farm |
| 3 | 2 | The Willows |
| 3 | 3 | Tall Trees |
| 3 | 4 | The Melksham Florist |
| 4 | 5 | Dun Roamin |
+------+------+----------------------+
5 rows in set (0.00 sec)
定期加入
如果我们进行常规JOIN（没有关键字INNER，OUTER，LEFT或RIGHT），那么我们将获得在两个表中以适当方式匹配的所有记录，并且不报告两个不匹配的传入表中的记录：
mysql> select name, phone, selling
from people join property
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name | phone | selling |
+-----------+--------------+----------------------+
| Mr Brown | 01225 708225 | Old House Farm |
| Mr Pullen | 01380 724040 | The Willows |
| Mr Pullen | 01380 724040 | Tall Trees |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+
4 rows in set (0.01 sec)
LEFT JOIN
如果我们进行LEFT JOIN，我们会以相同的方式获得匹配的所有记录，并且在IN ADDITION中我们为连接的左表中的每个不匹配的记录获得额外的记录 - 从而确保（在此示例中）每个PERSON都被提及：
mysql> select name, phone, selling
from people left join property
on people.pid = property.pid;
+------------+--------------+----------------------+
| name | phone | selling |
+------------+--------------+----------------------+
| Mr Brown | 01225 708225 | Old House Farm |
| Miss Smith | 01225 899360 | NULL <<-- unmatch |
| Mr Pullen | 01380 724040 | The Willows |
| Mr Pullen | 01380 724040 | Tall Trees |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+------------+--------------+----------------------+
5 rows in set (0.00 sec)
正确的加入
如果我们进行RIGHT JOIN，我们会获得匹配的所有记录和IN ADDITION为连接右表中每个不匹配记录的额外记录 - 在我的示例中，这意味着即使我们不这样做，每个属性也会被提及有卖家详情：
mysql> select name, phone, selling
from people right join property
on people.pid = property.pid;
+-----------+--------------+----------------------+
| name | phone | selling |
+-----------+--------------+----------------------+
| Mr Brown | 01225 708225 | Old House Farm |
| Mr Pullen | 01380 724040 | The Willows |
| Mr Pullen | 01380 724040 | Tall Trees |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL | NULL | Dun Roamin |
+-----------+--------------+----------------------+
5 rows in set (0.00 sec)
INNER JOIN执行完全连接，就像第一个示例一样，在最后两个示例中，可以在LEFT或RIGHT之后添加单词OUTER - 它提供了ODBC兼容性，并且不添加额外的功能。
291
2018-02-14 05:53
REGULAR JOIN 和 INNER JOIN 是一回事。格雷厄姆·埃利斯想说的话 REGULAR 是INNER JOIN是“默认”JOIN，当没有时 LEFT 要么 RIGHT 关键字已指定。如果你阅读整篇文章，最后他说“一个INNER JOIN做了一个完整的连接，就像第一个例子一样，并且在最后两个例子中的LEFT或RIGHT之后可以添加OUTER这个词 - 它是为ODBC提供的兼容性并没有增加额外的功能。“ - vegatripy
对于那些困惑的人 pid 和 spid， pid 代表“人id”，和 spid 代表“销售物业ID”。 - Andrew Grimm
如果我们只想选择Mr.BROWN，史密斯小姐和普伦先生，该怎么办？ - Fortune
@Fortune Select（有行的行） name 等于其中一个）来自什么？这与内外连接的区别有什么关系？ - philipxy
许多答案不要考虑一个 - 多样性的情况，这会误导某人，你的答案更好！ - rocket1037
内部联接
仅检索匹配的行，即 A intersect B。
SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
ON S.Advisor_ID = A.Advisor_ID
左外连接
选择第一个表中的所有记录，以及第二个表中的所有记录 与连接键匹配的表。
SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
ON S.Advisor_ID = A.Advisor_ID
完全外部加入
选择第二个表中的所有记录以及第一个表中的所有记录 与连接键匹配的表。
SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
ON S.Advisor_ID = A.Advisor_ID
参考
114
2018-01-27 12:16
工具的名称是什么？我发现它很有趣，因为它显示了行数和维恩图 - Grijesh Chauhan
@GrijeshChauhan Datamartist :) - Tushar Gupta
@Trushar :(它不适用于Linux系统.. - Grijesh Chauhan
@GrijeshChauhan是的但你可以尝试使用它来运行它 红酒 。 - Tushar Gupta
喔！是的我..我使用SQLyog使用葡萄酒..还有 PlayOnLinux - Grijesh Chauhan
加盟 用于组合来自两个表的数据，结果是一个新的临时表。连接基于谓词谓词执行，谓词用于执行连接。内连接和外连接之间的区别在于内连接将仅返回基于连接谓词实际匹配的行。 让我们考虑员工和位置表：
内部联接：- 内连接通过组合两个表的列值来创建新的结果表（雇员 和 位置）基于连接谓词。查询比较每一行 雇员 与每行 位置 找到满足连接谓词的所有行对。当通过匹配非NULL值来满足连接谓词时，每个匹配的行对的列值 雇员 和 位置 被合并到一个结果行中。 以下是内连接的SQL的外观：
select * from employee inner join location on employee.empID = location.empID
OR
select * from employee, location where employee.empID = location.empID
现在，这是运行SQL的结果如下：
外部加入： - 外连接不要求两个连接表中的每个记录都具有匹配的记录。即使没有其他匹配记录，联接表也会保留每条记录。外连接进一步细分为左外连接和右外连接，具体取决于保留哪个表的行（左或右）。
左外连接： - 表的左外连接（或简称左连接）的结果 雇员 和 位置 始终包含“左”表的所有记录（雇员），即使连接条件在“右”表中找不到任何匹配的记录（位置）。 以下是使用上表的左外连接的SQL的样子：
select * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional
现在，运行此SQL的结果如下：
正确的外部加入： - 右外连接（或右连接）非常类似于左外连接，除了对表的处理进行了反转。 “右”表中的每一行（位置）将至少出现在联接表中一次。如果“左”表中没有匹配的行（雇员）存在，NULL将出现在列中 雇员 对于那些没有匹配的记录 位置。 这就是SQL的样子：
select * from employee right outer join location on employee.empID = location.empID;
//Use of outer keyword is optional
使用上面的表格，我们可以显示右外连接的结果集是什么样的：
完整的外部联接： -
完全外连接或完全连接是通过在连接的结果中包含不匹配的行来保留不匹配的信息，使用完全外连接。它包括来自两个表的所有行，无论另一个表是否具有匹配值。
108
2017-12-18 06:54
简单来说：
一个 内部联接 仅检索匹配的行。
而a 外连接 从一个表和其他表中的所有行检索匹配的行....结果取决于您使用的是哪一行：
剩下：右表中匹配的行和左表中的所有行
对：左表中匹配的行和右表中的所有行或
充分：所有表中的所有行。是否有匹配并不重要
101
2018-01-12 11:07
@nomen不是这个答案解决了它，但INNER JOIN是一个交集，而FULL OUTER JOIN是相应的UNION 如果 左右设置/圆圈包含（分别）LEFT和RIGHT连接的行。 PS这个答案不清楚输入与输出中的行。它将“在左/右表中”与“在左/右有左/右部分”混淆，并且它使用“匹配行”与“所有”来表示从其他表到空行的行扩展的行。 - philipxy
如果在连接的另一侧（右侧）存在匹配记录，则内部联接仅显示行。
（左）外连接显示左侧每条记录的行，即使连接的另一侧（右侧）没有匹配的行。如果没有匹配的行，则另一侧（右侧）的列将显示NULL。
92
2017-09-01 22:38