join - INNER 和OUTER的区别

  显示原文与译文双语对照的内容

INNER JOIN ( 打印) 和 OUTER JOIN ( 打印) 之间的区别是什么?

LEFT JOINRIGHT JOINFULL JOIN 如何适应?

时间:

假设你要加入没有重复的列,这是一个非常常见的情况:

  • A 和B的内部联接给出了相交B的结果,换句话说,图交集的内部部分。

  • 外连接的a和b的结果给工会b, 换句话说,外的部分维恩图解联盟。

英镑示例

假设你有两个表,每个表有一个列,数据如下:


A B
- -
1 3
2 4
3 5
4 6

注意,( 1,2 ) 是唯一的,( 3,4 ) 是通用的,( 5,6 ) 对于是唯一的。

内部联接

使用任意一个等价查询的内部联接给出两个表的交集,换句话说,表示它们共有的两行。


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

LEFT OUTER 加入

LEFT OUTER 连接将给所有行加上中的所有公共行。


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 中没有对应的数据,则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

你也可以考虑以下不同的连接类型的架构;

visual explanation of joins

来源: Visual-Representation-of-SQL-Joins 详细解释由 C.L 。 Moffatt

我推荐文章的博客。 我看到的最好的描述,还有一个可视化的e.g.:

内部联接:

enter image description here

完全外部联接:

enter image description here

以下摘自 Graham Ellis在他的博客Mouth的文章" MySQL - LEFT-JOIN 和右联接,内部联接和外部联接"。

在数据库mysql等数据分为很多表,然后连接( Joined ) 一起在 SELECTJOIN 命令从多个表读取记录。 阅读这里示例以了解它的工作方式。

首先,一些示例数据:


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)

磅的常规联接

如果我们定期加入( 没有关键字内,外,左或者右),那么在两个表中都会得到匹配的所有记录,并记录两个不匹配的传入表:


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, 我们以同样的方式得到相匹配的所有记录,除了得到一个额外的记录为每一个无与伦比的记录在左表的加入,从而确保( 在本例中) 每个人提到:


 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)

英镑联接

如果我们做了正确的加入,我们得到所有匹配的记录,另外一个额外的记录为每一个无与伦比的记录在正确的表连接——在我的例子中,这意味着每个属性被提及,即使我们没有卖方细节:


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)

内部联接执行完全联接,就像第一个示例一样,可以在最后两个示例中添加单词外,也可以在最后两个示例中添加该单词,而不添加额外的功能。

维恩图并不是真正为我做的。

它们不显示交叉联接和内部联接之间的任何区别,例如或者更一般地显示不同类型的联接谓词之间的区别,或者提供一个框架来推断它们如何操作。

没有替代逻辑处理的替代方法,它相对简单。

源表

enter link description here

首先以 CROSS JOIN ( AKA笛卡尔乘积) 开始。 这没有 ON 子句,只返回两个表中的每一行行。

选择一个。色彩,从交叉连接b B.Colour

enter link description here

内部和外部联接有一个"上"子句谓词。

  • 内部联接。 。评估交叉联接结果中所有行的"上"子句中的条件。 如果真返回联接的行。 否则放弃它。
  • LEFT OUTER 加入。一样内连接然后左表中的行数不匹配任何输出这些正确的表列的null值。
  • 右外连接。一样内连接然后右表中的行数不匹配任何与零输出这些值左表列。
  • 完全外部联接。内部联接一样然后保存左非匹配行和 LEFT OUTER 联接和右非匹配的行,按右向外联接。

一些例子

选择一个。色彩,从内连接 B.Colour B A.Colour = B.Colour

上面是经典的设备联接。

Inner Join

选择一个。色彩,从内连接 B.Colour B A.Colour 不是('绿色','蓝色的)

内部联接条件不必是相等条件,它不需要引用来自两个表的列的列。 正在评估 A.Colour NOT IN ('Green','Blue') 在交叉联接的每一行上返回。

inner 2

选择一个。色彩,从内连接 B.Colour B 1 =1

对于交叉联接结果中的所有行,联接条件的计算结果为真,因此这与交叉联接相同。 我将不再重复 16行的图片。

选择一个。颜色, B.Colour LEFT OUTER 加入b A.Colour = B.Colour

外部连接在逻辑上是相同的方式评估内部连接除了,如果一行从左表( 对于 LEFT JOIN ) 不加入任何从右手表行与 NULL 值保存在结果右边列。

LOJ

选择一个。颜色, B.Colour LEFT OUTER 加入b A.Colour = B.Colour B.Colour 为空的地方

这只将前面的结果限制为只返回 B.Colour IS NULL的行。 在这种情况下,这些行将是保留的行,因为它们在右侧表中没有匹配,查询返回的是表 B 中不匹配的单个红色行。 这称为反半联接。

重要的是要选择一列作为 IS NULL的测试,它可以是不可为空值或者为它的连接条件可以保证任何 NULL 值将被排除在这个模式才能正常工作,避免只是,引入了新的订单行,恰好有了 NULL 除了联合国该列的值匹配的行。

loj is null

选择一个。颜色,从右外连接b B.Colour A.Colour = B.Colour

右侧的外部联接类似于 LEFT OUTER 连接,除了它们保留了不匹配的行,并且向左扩展了左边的列。

ROJ

选择一个。色彩,从全外连接b B.Colour A.Colour = B.Colour

完整的外部联接组合了左右联接的行为,并保留了左侧和右侧表中不匹配的行。

FOJ

选择一个。色彩,从全外连接b B.Colour 1 = 0

交叉联接中没有行与 1=0 谓词匹配。 在另一侧的列中,使用普通外部联接规则保留两侧的所有行。

FOJ 2

对上面的查询稍作修改 SELECT COALESCE(A.Colour, B.Colour) AS Colour FROM A FULL OUTER JOIN B ON 1 = 0 将模拟两个表的UNION ALL

选择一个。颜色, B.Colour LEFT OUTER 加入b A.Colour = B.Colour B.Colour ='绿色''

注意 WHERE 子句( 如果存在的话) 在加入后逻辑上运行。 一个常见的错误是执行一个 LEFT OUTER join,然后在右表中包含一个带有条件的where子句,该条件将排除不匹配的行。 上结果将执行外部联接。。

LOJ

然后"位置"子句运行。 NULL='Green' 不计算为真,因此由外部联接保留的行最终放弃了( 还有蓝色的),有效地将联接转换为内部的。

LOJtoInner

如果目的是只包括行从b,颜色是绿色和所有行不管正确的语法

选择一个。颜色, B.Colour LEFT OUTER 加入b A.Colour = B.Colour B.Colour ='绿色''

enter image description here

内部联接

只检索匹配的行,即 A intersect B

Enter image description here


SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
 ON S.Advisor_ID = A.Advisor_ID


左外部联接

从第一个表中选择所有记录,以及第二个表中的所有记录,与连接的键相匹配。

Enter image description here


SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
 ON S.Advisor_ID = A.Advisor_ID


完全外部联接

从第二个表中选择所有记录,并选择第一个表中与已经连接的键相匹配的记录。

Enter image description here


SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
 ON S.Advisor_ID = A.Advisor_ID


引用

如果在联接的其他( 右边) 一侧有匹配记录,内部联接只显示行。

( 左边) 外部联接为左边的每条记录显示行,即使在联接的另一个( 右边) 边上没有匹配的行。 如果没有匹配的行,则其他( 右边) 侧边的列将显示空值。

简单地说:

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

尽管的外部联接从一个表和其他表中的所有行检索匹配的行。。结果取决于你使用的是哪一个:

  • :正确的表中匹配的行,左表中的所有行

  • : 左表格中的匹配行和右侧表格中的所有行

  • Full: 所有表中的所有行。 是否有匹配与否无关紧要

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

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

例如你有一个订单和一个OrderDetails表。 它们是由一个"订单订单"相关的。

磅订单

  • 订单订单
  • CustomerName

  • 订单明细 ID
  • 订单订单
  • 产品名称
  • 数量
  • 价格

请求


SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

将只返回在OrderDetails表中也有某些内容的顺序。

如果你将它更改为外部 LEFT JOIN


SELECT Orders.OrderID, Orders.CustomerName FROM Orders 
LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

然后,即使没有OrderDetails记录,它也会从订单表中返回记录。

你可以使用它来找到订单,没有任何orderdetails表示一个可能的孤儿 ORDER BY 添加where子句 WHERE OrderDetails.OrderID IS NULL

使用内部联接返回两个表中的所有行。 IE 。在结果表中,所有行和colums都有值。

在外部联接中,relulting表可能有空的colums 。 外部联接可以是左或者右

LEFT OUTER 联接返回第一个表中的所有行,即使第二个表中没有匹配项。

右外部联接返回第二个表中的所有行,即使第一个表中没有匹配项。

...