SQL在执行JOIN ON时，到底发生了什么？

2020-07-08 00:00:00 查询执行返回笛卡尔保留

点击关注上方“SQL数据库开发”，

设为“置顶或星标”，时间送达干货

对于一些SQL初学者，写一个简单的单表查询那是信手拈来。

(文末准备送几本技术书籍给小伙伴们~)

但是遇到写多表关联查询可能就懵逼了：为什么会有多表查询这种“怪物”？要怎么写？为什么要这样为难我？这是谁发明的？

进而可能会引申出人生的终极哲学问题：我是谁？我在哪？我在做什么？

有点扯远了，但确实能够体会到一些初学者，对多表关联查询的困扰。今天我们就给大家讲解多表关联查询到底是怎么一回事。

大家都知道，多表关联查询的关键字是JOIN...ON，如果只关心怎么使用，可以移步《SQL基础知识V2——JOIN连接》。

本文主要讲解JOIN ON在数据库中是怎么执行的。

测试环境

SQL Server 2017

测试样表

我们新建两张测试表Customers和Orders，表结构如下：

表Customers

表Orders

要求：查询每个客户的订单数量

我们先直接写出查询语句：

SELECT a.CustomerName,Count(OrderID) Nums
FROM Customers a
LEFT JOIN Orders b ON a.CustomerID=b.CustomerID
GROUP BY a.CustomerName

结果如下：

问：这个结果是怎么来的呢？

要搞清楚这个结果是怎么来的，我们需要先知道一个概念：笛卡尔积。

1、执行笛卡尔积（交叉联接）

什么是笛卡尔积？

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积，表示为X×Y，个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

具体如下图：

而我们在进行SQL的表关联时，JOIN的步就是生成笛卡尔积。我们上面的代码

... Customers a LEFT JOIN Orders b ...

开始是在数据库中生成了一张笛卡尔积的虚表T1，T1里面的数量正是两个表（Customers和Orders）数量的乘积9条(3 × 3)，我们可以使用如下代码获得笛卡尔积：

SELECT 
B.CustomerID,
B.CustomerName,
A.OrderID,
A.CustomerID
FROM Orders A
CROSS JOIN Customers B

查询出来的数据如下：

虚表T1

2、执行过滤筛选

当JOIN将两个表生成笛卡尔积的虚表T1之后，随后的ON开始执行筛选功能，ON后面的条件就是指符合条件的返回结果TRUE，不符合条件的返回结果FALSE，以及未知情况UNKNOWN。

在继续讲解前我们需要先普及一下三值逻辑的概念

三值逻辑就是我们上面说到的ON返回的三个结果：TRUE，FALSE和UNKNOWN。

大多数的编程语言中返回的结果要么是TRUE，要么就是FALSE。而SQL中的UNKONWN通常与NULL值出现有关。

以下情况都会返回UNKNOWN：

NULL>21

NULL=NULL

X+NULL>Y

NULL代表一种缺失值，当把一个缺失值与其他任意值进行比较时，结果始终为UNKNOWN。

而我们上面的代码中的ON的条件是这样写的：

... a.CustomerID=b.CustomerID ...

意思就是取a(表Customers的别名)和b(表Orders的别名)中CustomerID相等的结果。根据这个筛选条件，虚表T1中可以生成相应逻辑值，如下图：

ON筛选的逻辑结果表

从上面我们可以看到完全满足条件（Logic Value的值为TRUE）的只有两条记录，数据库会将这些结果插入到虚表T2，进行下一步的操作准备。

虚表T2

3、添加外部行

这一步只在外联接(OUT JOIN)中才会发生。对于外联接，通过为其指定一种联接方式(LEFT，RIGHT或FULL)，就把一个或两个输入表标记为保留表。

把表标记为保留表，即表示希望返回该表的所有行，即使ON过滤了一些行。

左连接(LEFT OUT JOIN)是把左边的表作为保留表，右连接（RIGHT OUT JOIN）是把右边的表作为保留表，全连接（FULL OUT JOIN）则是把两个表都作为保留表。（我们在书写时，通常会省略掉OUT)

在执行完ON的筛选后，我们根据写法来添加这些保留表中记录。

我们上面的代码使用的是LEFT JOIN，所以我们需要将左表（注：LEFT JOIN 左边的表）Customers表作为保留表。

在ON筛选完后，我们发现Customers表中CustomerID为1的没有在T2中，我们需要将这条记录的相关信息添加到T2中生成虚表T3，并且将Order表中的所有数据置为NULL，因为他们（指表Orders中的两列）不属于保留表，不是我们需要保留的数据。这样汇总后虚表T3中的数据如下：

虚表T3

这样当我们再对表Orders中的OrderID计数时，CustomerID为1的客户因为没有订单，返回的结果将为0，而CustomersID为2,3的客户都有一个订单，返回的结果将为1。

至此整个表关联环节就执行完成了。

参考文献

《Microsoft SQL Server 2008技术内幕：T-SQL查询》

以上就是JOIN在数据库中执行的相关内容，如有不明白的地方，欢迎在底下留言。

下面是福利环节~

荐书：《Oracle高性能系统架构实战大全》
编辑推荐：
（1） 深入浅出：详解与 Oracle 数据库性能相关的方方面面，涵盖 Oracle 的体系架构及其背后的运行机制。 
（2）直击难点：全面解析Oracle SQL 执行计划和Oracle SQL 性能分析与优化。 
（3）全新实战：真实有效的实战案例再现Oracle数据库开发过程中的问题及解决思路。 
（4）双管齐下：先设定方案，然后从应用角度和数据库角度综合考虑，逐一分析实现环境。

如何购买：
点击阅读原文购买，也可点击下方小程序购买~
如何赠送：
1、必须是关注了本公众号的小伙伴
2、留言集赞数大于50赞且排名前五的小伙伴，每人赠送一本，定价128RMB。
3、截止2020年7月10日上午9:00

——End——

后台回复关键字：1024，获取一份精心整理的技术干货
后台回复关键字：进群，带你进入高手如云的交流群。
推荐阅读

SQL 语法速成手册

可怕，如果张东升是个程序员……
我只会SQL，到底能不能找到好工作呢？
Oracle常用函数整理

MySQL常用函数整理


这是一个能学到技术的公众号，欢迎关注

相关文章