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

还有LEFT JOINRIGHT JOINFULL 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 (又称笛卡尔积)开始。它没有ON子句,仅返回两个表中行的每个组合。

从交叉连接中选择 A. 颜色,B。颜色

在此处输入链接说明

内连接和外连接具有 “ON” 子句谓词。

  • 内部联接。为交叉连接结果中的所有行评估 “ON” 子句中的条件。如果为 true,则返回加入的行。否则将其丢弃。
  • 左外联接。与内部联接相同,然后对左表中任何不匹配的行输出这些,并为右表列输出 NULL 值。
  • 右外连接。与内部联接相同,然后在右表中与所有表都不匹配的任何行中,用左表列的 NULL 值输出这些行。
  • 完全外部联接。与内部联接相同,然后保留与左侧外部联接相同的左侧不匹配行,以及根据右侧外部联接的右侧不匹配行。

一些例子

从 A.Colour = B.Colour 的内部联接 B 中选择 A.Colour,B.Colour

上面是经典的等分连接。

内部联接

动画版

在此处输入图片说明

从 A.COLOUR NOT IN 的内部连接 B 中选择 A.Colour,B.Colour(“绿色”,“蓝色”)

内部联接条件不必一定是相等条件,也不必引用两个表(甚至其中一个表)的列。在A.Colour NOT IN ('Green','Blue')每一行上评估A.Colour NOT IN ('Green','Blue')

内部2

从 1 = 1 的内部联接 B 中选择 A. 颜色,B。颜色

对于交叉连接结果中的所有行,连接条件的评估结果均为 true,因此这与交叉连接相同。我将不再重复 16 行的图片。

从 A.Colour = B.Colour 的左外连接 B 选择 A.Colour,B.Colour

外部联接在逻辑上与内部联接的计算方式相同,除了如果左表中的行(用于左联接)根本不与右手表中的任何行联接,则将其保留在结果中,且值为NULL右边的列。

LOJ

从 A.COLOUR = B.COLOUR 的左外连接 B 中选择 A.Colour,B.Colour B.Colour 为 NULL

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

IS NULL测试选择一列不可为空或联接条件可确保排除任何NULL值,以使此模式正确运行,并避免仅带回恰好有一个行的行,这一点很重要。除未匹配的行外,该列的NULL值。

loj为空

从 A.Colour = B.Colour 的右外连接 B 中选择 A.Colour,B.Colour

右外部联接的行为与左外部联接类似,不同之处在于它们保留了来自右表的不匹配行,并且 null 扩展了左手列。

罗杰

从 A.Colour = B.Colour 的完整外部连接 B 中选择 A.Colour,B.Colour

完全外部联接将左右联接的行为结合在一起,并保留左右表中不匹配的行。

福建

从完全外部联接 B 上选择 A. 颜色,B。颜色 1 = 0

交叉联接中没有行与1=0谓词匹配。两侧的所有行均使用常规外部连接规则保留,另一侧的表的列中为 NULL。

FOJ 2

从完全外部连接 B 上选择颜色为 COALESCE(A.Colour,B.Colour)作为颜色 0

通过对前面的查询进行较小的修改,可以模拟两个表的UNION ALL

全联盟

从左外连接 B 上选择 A. 颜色,B。颜色 B. 颜色 = B. 颜色 WHERE B. 颜色 ='绿色'

请注意, WHERE子句(如果存在)在连接后逻辑上运行。一个常见的错误是执行左外部联接,然后在右表上包含带有条件的 WHERE 子句,该条件最终将排除不匹配的行。以上结束了执行外部联接...

LOJ

... 然后运行 “Where” 子句。 NULL= 'Green'不等于 true,因此外部联接保留的行最终被丢弃(与蓝色联接在一起),从而有效地将联接转换回内部联接。

LOJtoInner

如果打算仅包含 B 的行(其中 Color 为绿色)和 A 的所有行(无论正确的语法是

从 A.Colour = B.Colour 和 B.Colour ='Green' 的左外连接 B 中选择 A.Colour,B.Colour

在此处输入图片说明

SQL 小提琴

请参阅这些示例, 在 SQLFiddle.com 上实时运行

联接用于合并两个表中的数据,结果是一个新的临时表。联接是基于称为谓词的事物执行的,谓词指定了执行联接所使用的条件。内部联接和外部联接之间的区别在于,内部联接将仅返回基于联接谓词实际匹配的行。例如 - 让我们考虑 Employee 和 Location 表:

在此处输入图片说明

内部联接:- 内部联接通过基于联接谓词组合两个表( EmployeeLocation )的列值来创建新的结果表。该查询将Employee的每一行与Location 的每一行进行比较,以找到满足 join 谓词的所有行对。当通过匹配非 NULL 值满足连接谓词时, EmployeeLocation 的每对匹配行对的列值将合并到结果行中。内部联接的 SQL 如下所示:

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

现在,运行该 SQL 的结果如下所示: 在此处输入图片说明 在此处输入图片说明

外部联接:- 外部联接不需要两个联接表中的每个记录都具有匹配的记录。联接的表将保留每个记录,即使不存在其他匹配的记录也是如此。外连接细分为左外连接和右外连接,具体取决于保留的表行(左还是右)。

左外部联接:-EmployeeLocation 的 左外部联接 (或简单地称为左联接)的结果始终包含 “left” 表( Employee )的所有记录,即使联接条件未在其中找到任何匹配的记录 “正确的” 表( Location )。使用上面的表,这是用于左外部联接的 SQL 的样子:

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

现在,运行此 SQL 的结果如下所示: 在此处输入图片说明 在此处输入图片说明

右外部联接:- 右外部联接 (或右联接 )与左外部联接非常相似,除了对表的处理相反。 “右” 表( Location )中的每一行将至少出现在联接表中一次。如果 “左” 表( Employee )中没有匹配的行存在,则Employee 的列中对于Location 中不匹配的记录将显示 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 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

参考文献

简单来说:

内部联接仅检索匹配的行。

外部联接从一个表中检索匹配的行,而从另一表中检索所有行.. 结果取决于您使用的是哪一个:

  • :右表中的匹配行以及左表中的所有行

  • :左表中匹配的行以及右表中的所有行或

  • 完整 :所有表中的所有行。是否有比赛无关紧要

内部联接仅在联接的另一侧(右侧)显示匹配记录时才显示行。

(左)外部联接在左侧显示每个记录的行,即使联接的另一(右侧)没有匹配的行。如果没有匹配的行,则另一(右侧)列将显示 NULL。

内部联接要求联接表中存在具有相关 ID 的记录。

即使右侧没有任何内容,外部联接也将返回左侧的记录。

例如,您有一个 Orders 和一个 OrderDetails 表。它们通过 “OrderID” 关联。

订单

  • 订单编号
  • 顾客姓名

订单详细信息

  • OrderDetailID
  • 订单编号
  • 产品名称
  • 数量
  • 价钱

要求

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

那么它将从 Orders 表中返回记录,即使它们没有 OrderDetails 记录也是如此。

通过添加 where 子句(如WHERE OrderDetails.OrderID IS NULL可以使用它来查找没有任何 OrderDetails 指示可能的孤立订单的 Order。

简单来说:

内部联接 -> 仅从父表和子表中获取公共记录,而父表的主键与子表中的外键匹配。

左联接 ->

伪码

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.
    }
  }

右连接 :与左连接正好相反。将表名放在 LEFT JOIN 右边的 LEFT JOIN 中,您将得到与 LEFT JOIN 相同的输出。

外连接 :显示两个表中的所有记录, No matter what 。如果基于主键,外键,左表中的记录与右表不匹配,请使用 NULL 值作为连接的结果。

范例:

例

现在假设有 2 张桌子

1.employees , 2.phone_numbers_employees

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id

在这里,employees 表是 Master 表,phone_numbers_employees 是子表(它包含emp_id作为外键,它连接employee.id它的子表。)

内部联接

仅当雇员表的主键(其 id)与子表 phone_numbers_employees(emp_id)的外键匹配时 ,才获取 2 个表的记录。

因此查询将是:

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;

图表上看起来像:

图表

这是加入的很好的解释

对于所有类型的联接,这都是一个很好的图解说明

来源: http : //ssiddique.info/understanding-sql-joins-in-easy-way.html

您可以使用INNER JOIN从两个匹配的表中返回所有行。即在结果表中,所有行和列都将具有值。

OUTER JOIN ,结果表可能具有空列。外部连接可以是LEFTRIGHT

LEFT OUTER JOIN返回第一个表中的所有行,即使第二个表中没有匹配项也是如此。

RIGHT OUTER JOIN返回第二个表中的所有行,即使第一个表中没有匹配项也是如此。