此外，如何使用 LEFT OUTER JOIN, RIGHT OUTER JOIN, 和 FULL OUTER JOIN？
“INNER JOIN”和“OUTER JOIN”的区别是什么？
5210
- cdv
4
89以下答案、评论和它们所提到的参考链接中，仅有一个解释了Venn图如何表示运算符：圆形交集区域表示A JOIN B中的行集合。每个圆形独特的区域代表着取其表的行不参与A JOIN B，并添加另一个表的唯一列且值为NULL所得的行集合（大多数给出了模糊错误的圆圈对应于A和B的说法）。 - philipxy
22@DanteTheSmith 不行，那跟这里的图表一样存在问题。请看我上面关于问题的评论和下面关于那篇博客文章的评论：“Jeff在几页下面的评论中驳斥了他的博客”。文氏图展示集合中的元素。只需尝试准确地确定这些图表中的集合和元素是什么。集合不是表格，元素也不是它们的行。此外，任意两个表格都可以连接，因此主键和外键是无关紧要的。全是虚假的。你正在做成千上万其他人所做的事情——得到一个模糊的印象，并错误地认为它有意义。 - philipxy
2我的前面的评论是关于Jeff Atwood博客文章中存在混淆和否认的问题。 - philipxy
2我的第一个评论链接是外部的，但是 i.stack.imgur.com 上有它的输出（而不是输入）的插图的永久副本，用于 inner、left 和 full 连接操作（在绿色区域）。 - philipxy
28个回答
6676
假设您在没有重复列的情况下进行连接，这是非常普遍的情况：
示例
假设您有两个表，每个表只有一列数据，如下所示：
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部分为null，反之亦然。
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
- Mark Harrison
13
67在B表中添加另一行值为4将有助于丰富示例，这将显示内连接不需要在相等行数上进行。 - softveda
535这段话的意思是：这是一篇很好的解释，但其中一句话：“A和B的外连接会给出A和B的并集结果，即Venn图中联合部分的外部”，表达不准确。外连接会给出A和B相交的结果，以及以下情况之一：A的全部内容（左连接）、B的全部内容（右连接）或A和B的全部内容（全连接）。只有最后一种情况才真正表示A和B的并集。尽管如此，这篇解释写得很好。 - Thomas
11@Ameer，感谢你。JOIN 操作不能保证顺序，你需要添加一个 ORDER BY 子句。 - Mark Harrison
10是的，OUTER JOIN和FULL OUTER JOIN是等价的。同样，LEFT/RIGHT JOIN与LEFT/RIGHT OUTER JOIN是等价的，就像INNER JOIN等价于简单的JOIN。 - Chris
13我已经将这个回答点踩了，因为它是错误的。请考虑删除该答案，否则会误导一代又一代被大量点赞所迷惑的计算机科学学生。维恩图并不能解释连接操作，连接的内部部分也不是交集。 - Colm Bhandal
显示剩余8条评论
953
对我来说，文氏图并不能很好地展示不同类型的连接谓词之间的区别，例如交叉连接和内连接，更普遍地说，并没有展示任何连接谓词的区别或提供用于推理它们将如何操作的框架。
没有什么能替代理解逻辑处理，而且相对容易掌握。
- 设想一个交叉连接。
- 根据第1步中的所有行评估
on子句，并保留谓词评估为true的行。 - (仅限于外连接)添加在步骤2中丢失的任何外部行。
(注：实际上，查询优化器可能会找到比上述纯逻辑描述更高效的执行查询的方法，但最终结果必须相同)
我首先从一个完全外连接的动画版本开始。随后是更详细的说明。
说明
源表
首先开始一个CROSS JOIN（也称笛卡尔积）。它没有ON子句，仅返回两个表中行的每种组合。
SELECT A.Colour, B.Colour FROM A CROSS JOIN B
内部和外部连接有一个“ON”子句谓词。
- 内连接。评估交叉连接结果中所有行的条件。如果为真，则返回连接的行。否则丢弃它。
- 左外连接。与内部连接相同，然后对于左表中没有匹配到任何内容的行，输出这些行并将右表列的值设置为空。
- 右外连接。与内部连接相同，然后对于右表中没有匹配到任何内容的行，输出这些行并将左表列的值设置为空。
- 完全外连接。与内联相同，然后像左外连接那样保留左侧未匹配的行，像右外连接那样保留右侧未匹配的行。
一些示例
SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour
以上是经典的等值连接。
动画版本
从A表和B表选择颜色字段，条件为A表的颜色字段不在('Green','Blue')中，并且与B表的颜色字段进行内连接
内连接条件可以不是相等条件，也可以不引用任何一个或两个表中的列。对于交叉连接的每一行，评估A.Colour NOT IN ('Green','Blue')返回结果。
从A表和B表选择颜色字段，条件为1=1，并与B表进行内连接
连接条件对交叉连接结果中的所有行均返回true，因此这与交叉连接相同。我不会再重复16行的图片了。
从A表和B表选择颜色字段，条件为A表的颜色字段等于B表的颜色字段，并进行左外连接
外连接逻辑上与内连接的计算方式相同，只是如果左表（对于左连接）的某一行根本没有与右表的任何行连接，它将以NULL值保留在结果中的右列。
从A表和B表选择颜色字段，条件为A表的颜色字段等于B表的颜色字段，并进行左外连接，同时限制结果只返回B.Colour IS NULL的行
这仅将之前的结果限制为仅返回B.Colour IS NULL的行。在这种特定情况下，这些是未匹配到表B中的单个红行，并且查询返回该行。这被称为反半连接。
重要的是选择一列进行IS NULL测试，该列既不可为空，也确保联接条件排除了任何NULL值，以使此模式正确工作并避免仅返回具有该列的NULL值和未匹配的行。
从A表和B表选择颜色字段，条件为A表的颜色字段等于B表的颜色字段，并进行右外连接
右外连接与左外连接类似，但它保留来自右表的非匹配行，并扩展左侧列的NULL值。
从A表和B表选择颜色字段，条件为A表的颜色字段等于B表的颜色字段，并进行全外连接
全外连接结合了左连接和右连接的行为，并保留了左表和右表中的非匹配行。
SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0
在交叉连接中没有行与1=0谓词匹配。使用普通外部连接规则保留来自两侧的所有行，并在来自另一侧表的列中使用NULL。
SELECT COALESCE(A.Colour, B.Colour) AS Colour FROM A FULL OUTER JOIN B ON 1 = 0
通过对上一个查询进行微小修改，可以模拟两个表的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'不会被评估为真，因此由外部连接保留的行最终被丢弃（以及蓝色行），有效地将连接转换回内连接。
如果意图是仅包括B中颜色为绿色的行，而所有来自A的行都是正确的语法，则应该是
SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour = 'Green'
SQL Fiddle
查看这些示例在SQLFiddle.com上实时运行。
- Martin Smith
18
68我要说的是，虽然这对我来说并不像Venn图那样有效，但我赞赏人们的差异性和不同的学习方式，这是一个非常好的解释，并且与我以前看到的任何解释都不同。因此，我支持@ypercube在奖励额外积分方面做出的决定。另外，你很好地解释了将附加条件放入JOIN子句与WHERE子句的区别。向你表示赞扬，Martin Smith。 - Old Pro
32@OldPro，我认为Venn图还可以，但它们并没有说明如何表示交叉连接，也没有区分一种连接谓词（如等值连接）和另一种。对于我来说，通过在每一行交叉连接结果中评估连接谓词，然后添加未匹配的行（如果是外连接），最后再评估WHERE子句的心理模型更好用。 - Martin Smith
23维恩图适用于表示并集、交集和差集，但不适合表示连接操作。对于非常简单的连接操作（即连接条件在唯一列上的情况），它们具有一些微小的教育价值。 - ypercubeᵀᴹ
17@Arth - 不对，你错了。SQL Fiddle http://sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/5155 这是维恩图无法说明的内容。 - Martin Smith
8你是如何制作这些动画的？非常好的回答，唯一让我不喜欢的是你谦虚地说维恩图无法胜任。事实上，它们不能很好地模拟正在发生的事情，这个信息很重要，需要告诉别人，以免产生错误的想法。 - Git Gud
显示剩余13条评论
250
连接（Joins）用于将来自两个表的数据组合在一起，结果是一个新的临时表。连接是基于谓词（predicate）执行的，谓词指定了要使用的条件以执行连接。内连接和外连接之间的区别在于，内连接只会返回根据连接谓词实际匹配的行。
例如- 让我们考虑员工和位置表：
现在，运行该SQL的结果如下：
外连接：
外连接不要求两个连接表中的每条记录都有相应的匹配记录。即使没有其他匹配记录，连接表也会保留每个记录。外连接进一步分为左外连接和右外连接，具体取决于哪个表的行被保留（左侧或右侧）。
现在，运行这个 SQL 的结果如下：
请注意，虽然 Johnson 在员工位置表中没有条目，但他仍然包含在结果中，但位置字段为空。
使用上述表格，我们可以展示右外连接的结果集会是什么样子：
请注意，虽然没有员工在班加罗尔工作，但它仍然包含在结果中，员工字段被置为空。
| 员工编号 | 员工所在地 |
|---|---|
| 13 | 圣何塞 |
| 8 | 洛杉矶 |
| 3 | 印度浦那 |
| 17 | 印度金奈 |
| 39 | 印度班加罗尔 |
内连接： 内连接通过基于连接谓词，将两个表(员工和所在地)的列值组合成一个新的结果表。查询将员工的每一行与所在地的每一行进行比较，以找到满足连接谓词的所有行对。当匹配非空值时，将员工和所在地的每个匹配行的列值组合成一个结果行。 下面是内连接的 SQL 语句：
select * from employee inner join location on employee.empID = location.empID
OR
select * from employee, location where employee.empID = location.empID
现在，运行该SQL的结果如下：
| Employee.EmpId | Employee.EmpName | Location.EmpId | Location.EmpLoc |
|---|---|---|---|
| 13 | Jason | 13 | San Jose |
| 8 | Alex | 8 | Los Angeles |
| 3 | Ram | 3 | Pune, India |
| 17 | Babu | 17 | Chennai, India |
左外连接： 对于表Employee和Location的左外连接（或简称为左连接），其结果始终包含“左”表(Employee)的所有记录，即使连接条件在“右”表(Location)中找不到任何匹配的记录。 下面是使用上述表进行左外连接的SQL语句：
select * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional
现在，运行这个 SQL 的结果如下：
| Employee.EmpId | Employee.EmpName | Location.EmpId | Location.EmpLoc |
|---|---|---|---|
| 13 | Jason | 13 | San Jose |
| 8 | Alex | 8 | Los Angeles |
| 3 | Ram | 3 | Pune, India |
| 17 | Babu | 17 | Chennai, India |
| 25 | Johnson | NULL | NULL |
右外连接： 右外连接（或右连接）与左外连接非常相似，只是表的处理方式相反。来自“右”表（位置）的每一行都将至少出现在连接表中。如果“左”表（员工）中没有匹配的行，则对于那些在位置中没有匹配的记录，员工的列中将出现NULL。 这就是SQL的样子：
select * from employee right outer join location on employee.empID = location.empID;
//Use of outer keyword is optional
使用上述表格，我们可以展示右外连接的结果集会是什么样子：
| Employee.EmpId | Employee.EmpName | Location.EmpId | Location.EmpLoc |
|---|---|---|---|
| 13 | Jason | 13 | San Jose |
| 8 | Alex | 8 | Los Angeles |
| 3 | Ram | 3 | Pune, India |
| 17 | Babu | 17 | Chennai, India |
| NULL | NULL | 39 | Bangalore, India |
全外连接： 全外连接或全连接是通过在联接结果中包含不匹配的行来保留非匹配信息的，使用全外连接。它包括来自两个表的所有行，无论另一个表是否具有匹配值。
| 员工编号 | 员工姓名 | 所在地.员工编号 | 所在地.员工位置 |
|---|---|---|---|
| 13 | Jason | 13 | 圣何塞 |
| 8 | Alex | 8 | 洛杉矶 |
| 3 | Ram | 3 | 印度浦那 |
| 17 | Babu | 17 | 印度钦奈 |
| 25 | Johnson | NULL | NULL |
| NULL | NULL | 39 | 印度班加罗尔 |
- ajitksharma
2
1Venn 图表的标签有误。请查看我的问题和其他答案上的评论。此外，大部分语言都很差。例如：“当连接谓词通过匹配非 NULL 值得到满足时，将 Employee 和 Location 的每个匹配行对应的列值合并为一个结果行。”不，“当连接谓词通过匹配非 NULL 值得到满足时”。行中的值除了整体条件为真或假之外并不重要。一些值在条件为真时可能是 NULL。 - philipxy
@Persistence 所需的是表格初始化代码文本，格式为列，适合复制、粘贴和运行。 - philipxy
174
内连接
仅检索匹配的行，即A 交 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
参考资料
- Tushar Gupta - curioustushar
6
16这个工具叫什么？我发现它很有趣，因为它可以显示行数和维恩图。 - Grijesh Chauhan
3您的文字含糊不清且有误。"仅匹配行"是来自A和B的交叉连接的行，而检索到的内容（A内连接B）不是A与B的交集，而是（A左连接B）交集（A右连接B）。所选的行不来自A和B，而是来自A和B的交叉连接，以及从A和B的行的null扩展值中选择的行。 - philipxy
1@TusharGupta-curioustushar，你应该包含“用于SQL示例的表” - Manuel Jordan
1仅检索匹配的行，即 A 交 B。这是错误的。 - Colm Bhandal
131
简单来说：
内连接只检索匹配的行。
而外连接会检索一个表中匹配的行以及另一个表中的所有行，结果取决于您使用的哪种类型：
左连接：在左侧表中匹配的行和右侧表中的所有行。
右连接：在右侧表中匹配的行和左侧表中的所有行。
完全连接：所有表中的所有行。是否有匹配都无所谓。
- vidyadhar
1
2@nomen 这个回答并没有解决问题，但INNER JOIN是交集，FULL OUTER JOIN是相应的UNION 如果左右集/圆包含(分别)LEFT和RIGHT连接的行。PS：这个回答对输入输出的行不清晰。它混淆了“在左/右表中”的概念和“在左/右部分”，同时使用“匹配的行”和“全部”来表示扩展自其他表的行与通过nulls进行扩展的行。 - philipxy
127
内连接（inner join）仅在与另一侧（右侧）的表具有匹配记录时显示行。
左外连接（left outer join）为左侧每个记录显示行，即使在连接的另一侧（右侧）没有匹配的行也如此。如果没有匹配的行，则显示其他（右侧）侧的列为NULL。
- 1800 INFORMATION
101
内连接要求加入表中存在具有相关ID的记录。
外连接会返回左侧的记录，即使右侧没有对应的记录。
例如，您有一个订单和一个订单详细信息表，它们由“OrderID”相关联。
订单
- OrderID
- CustomerName
订单详细信息
- OrderDetailID
- OrderID
- ProductName
- Qty
- Price
请求内容：
SELECT Orders.OrderID, Orders.CustomerName
FROM Orders
INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID
只会返回同时在OrderDetails表中也有对应内容的订单。
如果将其改为OUTER LEFT JOIN
SELECT Orders.OrderID, Orders.CustomerName
FROM Orders
LEFT JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID
如果这样做，它将返回来自订单表的记录，即使它们没有订单详细信息。
您可以通过添加像 WHERE OrderDetails.OrderID IS NULL 这样的where子句来使用它查找没有任何OrderDetails的订单，从而指示可能的孤立订单。
- briankb
1
1我很欣赏这个简单而又实际的例子。我成功地将一个请求从
SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c, categories_description cd WHERE c.id = cd.categories_id AND c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC（MySQL）改为了 SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c INNER JOIN categories_description cd ON c.id = cd.categories_id WHERE c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC。我不确定关于额外的条件，它们混合得很好... - PhiLho84
简单来说：
内连接 -> 仅从父表和子表中取出主键与外键匹配的共同记录。
左连接 ->
pseudo code
1.Take All records from left Table
2.for(each record in right table,) {
if(Records from left & right table matching on primary & foreign key){
use their values as it is as result of join at the right side for 2nd table.
} else {
put value NULL values in that particular record as result of join at the right side for 2nd table.
}
}
1.员工，2.员工电话号码employees : id , name
phone_numbers_employees : id , phone_num , emp_id
这里，employees表是主表，phone_numbers_employees是子表（包含外键emp_id，连接到employee.id作为其子表）。
内连接
仅获取两个表中的记录当员工表的主键（即id）与子表phone_numbers_employees的外键（即emp_id）匹配时。
因此查询语句应该是：
SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;
根据上述解释，只选择主键=外键的匹配行。不匹配的主键=外键的行在连接结果中被跳过。
左连接：
左连接保留左表的所有行，无论右表是否存在匹配的行。
SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;
外连接：
SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;
它在图示上看起来像：
- Pratik Joshi
2
5这个结果与主键/唯一键和外键本质上没有关系，行为可以并且应该在没有提及它们的情况下描述。首先计算一个交叉连接，然后过滤掉不符合ON条件的行；此外，对于外连接，过滤/不匹配的行会通过NULL进行扩展（根据LEFT/RIGHT/FULL）。 - philipxy
1SQL连接总是匹配主键/外键的假设导致了Venn图的误用。请相应地修改您的答案。 - Colm Bhandal
69
您可以使用
在
内连接 (INNER JOIN)来返回两个表中匹配的所有行。也就是说，在结果表中，所有行和列都将具有值。在
外连接 (OUTER JOIN)中，结果表可能会有空列。外连接可以是LEFT或者RIGHT。
左外连接 (LEFT OUTER JOIN)将返回第一个表中的所有行，即使第二个表中没有匹配项。
右外连接 (RIGHT OUTER JOIN)将返回第二个表中的所有行，即使第一个表中没有匹配项。- vijikumar
68
INNER JOIN需要在两个表之间至少有一个匹配。例如，表A和表B，意味着 A ٨ B（即A与B的交集）。
LEFT OUTER JOIN和LEFT JOIN是相同的。它会返回两个表中匹配的所有记录以及左表中的所有可能性。
同样地，RIGHT OUTER JOIN和RIGHT JOIN是相同的。它会返回两个表中匹配的所有记录以及右表中的所有可能性。
FULL JOIN是 LEFT OUTER JOIN和RIGHT OUTER JOIN的组合，没有重复项。
- naga
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
- 相关问题
- 3 INNER JOIN和IN的区别
- 6 INNER JOIN和WHERE的区别是什么？
- 6 INNER JOIN和COMMA的区别
- 9 SQL INNER JOIN和LEFT JOIN带WHERE的区别
- 7 'LEFT JOIN'与'LEFT OUTER JOIN'的区别
- 1414 JOIN 和 INNER JOIN 的区别
- 7 INNER JOIN 和 LEFT/RIGHT OUTER JOIN 的问题
- 123 INNER JOIN和LEFT SEMI JOIN的区别
- 70 FULL JOIN和INNER JOIN的区别
- 4 SQL Server中INNER JOIN和cartesian join的区别