协慌网

 登录 贡献 社区

“INNER JOIN”和 “OUTER JOIN” 有什么区别？

LEFT JOIN ， RIGHT JOIN和FULL JOIN适应？

答案

假设您正在加入没有重复的列，这是一种非常常见的情况：

  • A 和 B 的内连接给出了 A 交叉 B 的结果，即维恩图交叉的内部。

  • 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

维恩图并没有真正为我做这件事。

例如，它们没有显示交叉连接和内连接之间的任何区别，或者更一般地显示不同类型的连接谓词之间的任何区别，或者提供用于推理它们将如何操作的框架。

理解逻辑处理是无可替代的，无论如何都要相对简单。

  1. 想象一下交叉连接。
  2. 针对步骤 1 中的所有行计算on子句，并将谓词计算结果保留为true
  3. （仅适用于外部联接）在步骤 2 中丢失的任何外部行中添加。

（注意：在实践中，查询优化器可能会找到比上面的纯逻辑描述更有效的执行查询的方法，但最终结果必须相同）

我将从完整外连接的动画版本开始。进一步说明如下。

在此输入图像描述

说明

来源表

在此输入链接描述

首先从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')返回。

内心2

选择 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右栏。

LOJ

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值。

loj是null

SELECT A.Colour，B.Colour from A ROU OUTER JOIN B ON A.Colour = B.Colour

右外连接的作用类似于左外连接，除了它们保留右表中的非匹配行，并且 null 扩展左手列。

ROJ

选择 A.Colour，B.Colour 从一个完整的外部加入 B A.Colour = B.Colour

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

FOJ

选择 A.Colour，B.Colour FROM A FULL OUTER JOIN B ON 1 = 0

交叉连接中的任何行都不匹配1=0谓词。使用常规外部联接规则保留两侧的所有行，并在另一侧的表中使用 NULL。

FOJ 2

SELECT COALESCE（A.Colour，B.Colour）AS 颜色来自一个完整的外部连接 B ON 1 = 0

通过对前面的查询进行微小修改，可以模拟两个表中的UNION ALL 。

UNION ALL

SELECT A.Colour，B.Colour from a LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour ='Green'

请注意， WHERE子句（如果存在）在连接后逻辑运行。一个常见错误是执行左外连接，然后包含 WHERE 子句，右子表上的条件最终排除不匹配的行。以上结果执行外连接...

LOJ

...... 然后 “Where” 子句运行。 NULL= 'Green'不计算为 true，因此外连接保留的行最终被丢弃（连同蓝色连接），有效地将连接转换回内连接。

LOJtoInner

如果打算只包含 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 上的这些示例。

连接用于组合来自两个表的数据，结果是一个新的临时表。连接是基于称为谓词的东西执行的，谓词指定了用于执行连接的条件。内连接和外连接之间的区别在于内连接将仅返回基于连接谓词实际匹配的行。让我们考虑员工和位置表：

在此输入图像描述

内连接： -内连接通过基于连接谓词组合两个表（ Employee和Location ）的列值来创建新的结果表。该查询将Employee的每一行与Location 的每一行进行比较，以查找满足 join-predicate 的所有行对。当通过匹配非 NULL 值来满足连接谓词时， Employee和Location 的每个匹配行对的列值将合并到结果行中。以下是内连接的 SQL 的外观： 

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

现在，这是运行 SQL 的结果： 在此输入图像描述 在此输入图像描述

外连接： -外连接不要求两个连接表中的每个记录都有匹配的记录。即使没有其他匹配记录，联接表也会保留每条记录。外连接进一步细分为左外连接和右外连接，具体取决于保留哪个表的行（左或右）。

左外连接： -表的左外连接（或简称左连接）的结果Employee和Location始终包含 “左” 表（ Employee ）的所有记录，即使连接条件未找到任何匹配的记录 “右” 表（ 位置 ）。以下是使用上表的左外连接的 SQL 的样子： 

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

现在，运行此 SQL 的结果如下： 在此输入图像描述 在此输入图像描述

右外连接： -右外连接（或右连接）非常类似于左外连接，除非对表的处理相反。 “右” 表（ 位置 ）中的每一行将至少出现在连接表中一次。如果从 “左” 表（employee）没有匹配的行存在，NULL 就会出现在员工对于那些在位置不匹配的记录列。这就是 SQL 的样子： 

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

使用上面的表格，我们可以显示右外连接的结果集是什么样的：

在此输入图像描述 在此输入图像描述

完全外部联接： -完全外部联接或完全联接是通过在联接的结果中包含不匹配的行来保留不匹配的信息，使用完整的外部联接 。它包括来自两个表的所有行，无论另一个表是否具有匹配值。 在此输入图像描述

图像来源

MySQL 8.0 参考手册 - 加入语法

Oracle Join 操作

假设您正在加入没有重复的列，这是一种非常常见的情况：

  • A 和 B 的内连接给出了 A 交叉 B 的结果，即维恩图交叉的内部。

  • 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

维恩图并没有真正为我做这件事。

例如，它们没有显示交叉连接和内连接之间的任何区别，或者更一般地显示不同类型的连接谓词之间的任何区别，或者提供用于推理它们将如何操作的框架。

理解逻辑处理是无可替代的，无论如何都要相对简单。

  1. 想象一下交叉连接。
  2. 针对步骤 1 中的所有行计算on子句，并将谓词计算结果保留为true
  3. （仅适用于外部联接）在步骤 2 中丢失的任何外部行中添加。

（注意：在实践中，查询优化器可能会找到比上面的纯逻辑描述更有效的执行查询的方法，但最终结果必须相同）

我将从完整外连接的动画版本开始。进一步说明如下。

在此输入图像描述

说明

来源表

在此输入链接描述

首先从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')返回。

内心2

选择 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右栏。

LOJ

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值。

loj是null

SELECT A.Colour，B.Colour from A ROU OUTER JOIN B ON A.Colour = B.Colour

右外连接的作用类似于左外连接，除了它们保留右表中的非匹配行，并且 null 扩展左手列。

ROJ

选择 A.Colour，B.Colour 从一个完整的外部加入 B A.Colour = B.Colour

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

FOJ

选择 A.Colour，B.Colour FROM A FULL OUTER JOIN B ON 1 = 0

交叉连接中的任何行都不匹配1=0谓词。使用常规外部联接规则保留两侧的所有行，并在另一侧的表中使用 NULL。

FOJ 2

SELECT COALESCE（A.Colour，B.Colour）AS 颜色来自一个完整的外部连接 B ON 1 = 0

通过对前面的查询进行微小修改，可以模拟两个表中的UNION ALL 。

UNION ALL

SELECT A.Colour，B.Colour from a LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour ='Green'

请注意， WHERE子句（如果存在）在连接后逻辑运行。一个常见错误是执行左外连接，然后包含 WHERE 子句，右子表上的条件最终排除不匹配的行。以上结果执行外连接...

LOJ

...... 然后 “Where” 子句运行。 NULL= 'Green'不计算为 true，因此外连接保留的行最终被丢弃（连同蓝色连接），有效地将连接转换回内连接。

LOJtoInner

如果打算只包含 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 上的这些示例。

连接用于组合来自两个表的数据，结果是一个新的临时表。连接是基于称为谓词的东西执行的，谓词指定了用于执行连接的条件。内连接和外连接之间的区别在于内连接将仅返回基于连接谓词实际匹配的行。让我们考虑员工和位置表：

在此输入图像描述

内连接： -内连接通过基于连接谓词组合两个表（ Employee和Location ）的列值来创建新的结果表。该查询将Employee的每一行与Location 的每一行进行比较，以查找满足 join-predicate 的所有行对。当通过匹配非 NULL 值来满足连接谓词时， Employee和Location 的每个匹配行对的列值将合并到结果行中。以下是内连接的 SQL 的外观： 

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

现在，这是运行 SQL 的结果： 在此输入图像描述 在此输入图像描述

外连接： -外连接不要求两个连接表中的每个记录都有匹配的记录。即使没有其他匹配记录，联接表也会保留每条记录。外连接进一步细分为左外连接和右外连接，具体取决于保留哪个表的行（左或右）。

左外连接： -表的左外连接（或简称左连接）的结果Employee和Location始终包含 “左” 表（ Employee ）的所有记录，即使连接条件未找到任何匹配的记录 “右” 表（ 位置 ）。以下是使用上表的左外连接的 SQL 的样子： 

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

现在，运行此 SQL 的结果如下： 在此输入图像描述 在此输入图像描述

右外连接： -右外连接（或右连接）非常类似于左外连接，除非对表的处理相反。 “右” 表（ 位置 ）中的每一行将至少出现在连接表中一次。如果从 “左” 表（employee）没有匹配的行存在，NULL 就会出现在员工对于那些在位置不匹配的记录列。这就是 SQL 的样子： 

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

使用上面的表格，我们可以显示右外连接的结果集是什么样的：

在此输入图像描述 在此输入图像描述

完全外部联接： -完全外部联接或完全联接是通过在联接的结果中包含不匹配的行来保留不匹配的信息，使用完整的外部联接 。它包括来自两个表的所有行，无论另一个表是否具有匹配值。 在此输入图像描述

图像来源

MySQL 8.0 参考手册 - 加入语法

Oracle Join 操作