文档章节

SQL left join

清风漂漂
 清风漂漂
发布于 2016/06/28 10:57
字数 2390
阅读 5
收藏 0
点赞 0
评论 0

通过一个小问题来学习SQL关联查询

原话题:

   

是关于一个left join的,没有技术难度,但不想清楚不一定能回答出正确答案来:

   TabA表有三个字段Id,Col1,Col2 且里面有一条数据1,1,2

   TabB表有两个字段Id,Col1且里面有四条数据

  •    1,1
  •    2,2
  •    3,2
  •    4,2

问题:

如下语句会返回多少条数据? 在不写测试脚本的情况下,如果你能在5分钟内准备回答出答案,且能说出些所以然来(及不是凭感觉猜出来的结果),那么请继续看后面的问题。

Select * from TabA a

Left join TabB b1 on a.Col1=b1.Col1

 Left join TabB b2 on a.Col2=b2.Col1

延深问题:

现在表A多增加一条数据2,3,4 ,此时再运行上面的语句会几条数据?如果你能在2分钟内回答出正常答案,那么请继续看后面的问题。

理论问题:

  1.  是否知道 sql server的join包含 hash匹配,嵌套循环以及合并联接?不同于left join, inner join的概念,属于执行计划中的概念。
  2. 上面三种的查询机制是否能画简单的示意图?
  3. 上面三种查询机制的应用场景是什么?即什么样的情况下适合应用三种中的哪一种?

我发现就上面这个问题不少人回答不正确,这其中也包括我自己。为什么如此简单的问题往往会回答错误,我认为可能有如下原因:

  1. 本身对SQL查询知识就很欠缺,比如不知道left join与inner join的区别等等;
  2. 平时工作中也写SQL查询,只知道怎么用,不知道稍微详细一点的细节;
  3. 没经过大脑思考,随口说的,往往仔细想想就能回答正确。

对于第一种情况的人,短时间内无法解决,只有通过自身的学习来补救,对于第二种情况的人就需要稍微学习一些基本的理论知识就够用,对于第三种情况的人是一个态度问题。

left join的概念

 

简单来讲就是以左表做为外层循环表,每条每条去内层表去查找匹配记录,如果找到就返回join好的值,如果没找到返回外层表的值,内层表统一赋值为null。这里之所以说成简单来讲,是因为我是拿嵌套循环的例子来分析,因为这比较容易让非SQL方面的程序员明白,毕竟对于.net程序员来讲编写双层或者多层循环的例子会很多。而对于hash匹配以及合并联接的应用场景在.net程序中相对较少,类似如下的双层循环。

复制代码

foreach(var colA in tabA)
{
     foreach(var colB in tabB)
     {
           if(colA==colB)
           {
                   ......
           }
     }
}

复制代码

 这里需要注意下,上面说到的外层表的记录循环去内层表查找时,这里有个问题,看这条语句:

Select * from TabA a Left join TabB b1 on a.Col1=b1.Col1

 这里的TabA 就是我这里讲的外层表,TabB就是内层表,外层表就一行数据,内层表有4行数据,从上面给出的数据来看,用来做等值判断的条件是外层表的Col1字段与内层表的Col1字段,拿外层表的Cole=1这行数据去内层表查询时,内层表的第一条数据符合条件,其它三条不符合,此时的结果会是下面的哪一种呢?

  • 4条记录

a.Id  a.Col1  a.Col2 b.Id  b.Col1

1      1         2          1     1

1      1         2          null  null

1      1         2          null  null

1      1         2          null  null

  • 一条记录

a.Id  a.Col1  a.Col2  b.Id  b.Col1

1       1        1          2      1    

这要理解当在内层表中找到数据以及找不到数据的区别,我们拿外层表Col1=1这条数据去内层表查找时,需要查找4次,其中有一条符合,三条不符合,这说明找到了匹配数据,所以只返回匹配的数据行,即一条数据,而不会出现上面的第一种结果返回4条数据。

这是我当时遇到这个问题时产生的误解。

再看后面的那个left join

Select * from TabA a Left join TabB b1 on a.Col1=b1.Col1 Left join TabB b2 on a.Col2=b2.Col1

 容易产生的问题,再进行第二次left join 的时候,外层表是TabA原始表呢还是第一次left join 之后的结果集呢? 看下我列出来的表头,就很容易理解了,这里的a.Col2就是第一次left join后的结果集。( a.Id  a.Col1  a.Col2 b.Id  b.Col1)

我们可以做下测试,这里使用inner join来做测试,因为这加容易比较出差异,运行下面的语句,此时TabA中有两条数据,就是上面延深问题中添加的2,3,4这条。

Select * from TabA a inner join TabB b1 on a.Col1=b1.Col1 inner join TabB b2 on a.Col2=b2.Col1

 分两步来看:

Select * from TabA a inner join TabB b1 on a.Col1=b1.Col1

 这里只会返回一条数据,因为inner join返回的交集。 

a.Id  a.Col1  a.Col2 b.Id  b.Col1

1     1           2        1     1

如果第二次join 时,如果连接的是原始表TablA,那么循环查询的次数应该是TabA的总条数2,但从下面的执行计划图可以分析出执行顺序。

  1. 上图一的结构图很明显,第二次join的是第一次join的结果集而不是原始表TabA。
  2. 上图二的实际行数也足以说明关联的不是原始表TabA

解决了上面两个问题,那么应该能容易分析出文章前面提到的两个问题的答案了。但这只是解决了一个小问题,如果从学习的角度来讲我们应该通过这一个问题来将其周边涉及的主要知识都学习一下,这里我们非常有必要了解了执行计划的join分类。

Join在执行计划中的分类

我只是简单的对这三种分类做简单的概述,后续为这三种join分别进行稍微详细点的总结。执行计划中的三种Join各有各的优缺点,不能说哪一种绝对是最好的,也不能说哪一种能够适用于所有的查询应用场景,我下面提供的常见应用场景只是一些案例,且是有运行环境的,因为执行计划的选择非常复杂,有时只要有一个环境因素不同就会造成执行计划的不同,比如会受到下面因素的因素的影响:

  • 数据量,当数据量比较小时可能是一种执行计划,当数据量慢慢增大时执行计划可能会发生改变。
  • join关联的不同也会使执行计划发生改变,比如用inner join与left join时产生的执行计划有可能是不同的。

Hash匹配   

常见适用场景:   

  • 条件列没有索引,这里说的条件列是关联表的所有关联键都没有索引   
  • 大数据表关联   

约束条件:只能用于等值条件,比如a.Col1=b.Col1这种类型的,值的注意的是这里讲的等值条件,不是说所有的条件都需要是等值的,只有其中有一个是等值条件就行,比如下面这几种都符合等值条件:   

Select * from TabA a inner join TabB b1 on a.Col1=b1.Col1 AND a.Col3 LIKE '%1%'

上面提到的适用场景以及约束条件,不是绝对的,受很多其它因素影响,比如上面提到的join方式的不同,例如下面这两条SQL的执行计划就不同,这里就不贴图了,情况很复杂。

  • 下面是嵌套查询

Select * from TabA a

Left join TabB b1 on <span style="color: #000000;">a.Col1=b1.Col1

Left</span> join TabB b2 on a.Col2=b2.Col1

  •  下面是Hash匹配

Select * from TabA a

inner join TabB b1 on a.Col1=b1.Col1

inner join TabB b2 on a.Col2=b2.Col1

Hash匹配还可以根据需要生成的Hash表的大小细分,分为In-Memory,grace以及recursive 这三种,它们对于内存的要求逐步提高。

Hash匹配的优点:只需要扫描两次表,IO占用相对较少。

Hash匹配的缺点:构建Hash表,比较消耗CPU资源。

嵌套循环   

常见适用场景:一个表数据量大,一个表数据量小,且关联键有索引。当只有一个表的关联键有索引时,将具有索引的表做为内层表可以获取最佳的IO性能。不局限于等值条件。

合并联接

 

常见适用场景,关联键上需要有已经经过排序后的索引做为数据源,一般情况下需要有一个关联键是唯一索引。当两个关联表数据量相当时且具有排序后的索引那么比较适合用合并联接,不局限于等值条件。

总结:

   sql查询机制非常复杂,受各种因素的影响,很难有统一的标准去衡量一条SQL语句的性能。而只有了解了它的一些基本原理后,才有可能不被一些看起来非常简单的问题难倒,也才有可能编写也适用于当前项目性能最佳的SQL来。

本文转载自:http://www.cnblogs.com/ASPNET2008/p/3308601.html

共有 人打赏支持
清风漂漂
粉丝 0
博文 10
码字总数 751
作品 0
南京
程序员
oracle下left join查询报ora-03113:一表多字段另一表同一字段关联

用友nc的web service接口报500,跟踪代码后发现是一个sql查询报错引起的。 该sql是多个表通过left join on连接起来的。其中一个表很多字段另一个表的一个字段关联, 一查询就报 ORA-03113: 通...

caoxie
2015/06/18
179
3
sql多表查询之一:Where 和 On的秘密

今天给大家转载一篇《sql多表查询之一:Where 和 On的秘密》,对于还在SQL初级阶段的朋友来说,sql多表查询问题是一个比较有趣也容易出错的技术。什么时候会用到sql多表查询呢?是在两张或两...

oecp
2011/07/27
0
1
Mysql 多表联合查询效率分析及优化

多表连接类型 1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用',' 如: [sql]view plaincopyprint? SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM...

蓝狐乐队
2014/04/30
0
0
SQL--Advanced tutorials

LIKE--模糊查询 选取 url 以字母 https开始的所有网站 选取 url 包含模式 "oo" 的所有网站 JOIN--外连接查询 SQL JOIN 子句: 用于把来自两个或多个表的行结合起来,==基于这些表之间的共同字...

T-newcomer
01/31
0
0
You have an error in your SQL syntax;

String sql = "SELECT DISTINCT torder.contractnum AS CONTRACTNUM, torder.contractdate AS CONTRACTDATE," + "tordcontracts.oconttotal AS OCONTTOTAL, torder.empname AS EMPNAME, tcus......

frank_Wf5
2017/06/21
71
1
mysql join用法分析

student表 score表 1、left JOIN 左联接 sql:SELECT FROM student sc left JOIN ss on sc.id=ss.studentid; 结果: left join是以student表的记录为基础的,student可以看成左表,score可以看......

卢瑶
2016/04/14
16
0
自动数据库抽取想要的查询结果,自动生成txt(utf-8)文件,然后自动ftp上传到外网服务器

需求:无人值守的把数据库中的数据,生成txt文本,自动上传到ftp服务器,与外部客户进行数据对接; =============================================== 步骤:1 写存储过程 2 数据库中建立计划...

51GT
05/17
0
0
数据库表的连接(Left join , Right Join, Inner Join)用法详解

left join 是left outer join的简写,left join默认是outer属性的。 Inner Join Inner Join 逻辑运算符返回满足第一个(顶端)输入与第二个(底端)输入联接的每一行。这个和用select查询多表...

张悟空
2014/08/19
0
0
Hive学习总结之四:HiveQL查询操作

第三部分:HiveQL 查询操作 Hive的SQL操作 •基本的Select 操作 SELECT [ALL | DISTINCT] select_expr, select_expr, … FROM table_reference [WHERE where_condition] [GROUP BY col_list ......

酷帅CoolBash
2013/01/30
0
0
数据库表的连接(Left join , Right Join, Inner Join)用法详解

left join 是left outer join的简写,left join默认是outer属性的。 Inner Join Inner Join 逻辑运算符返回满足第一个(顶端)输入与第二个(底端)输入联接的每一行。这个和用select查询多表...

Carl_
2014/09/15
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

shell中的函数、shell中的数组、告警系统需求分析

shell中的函数 格式: 格式: function f_name() { command } 函数必须要放在最前面 示例1(用来打印参数) 示例2(用于定义加法) 示例3(用于显示IP) shell中的数组 shell中的数组1 定义数...

Zhouliang6
今天
2
0
用 Scikit-Learn 和 Pandas 学习线性回归

      对于想深入了解线性回归的童鞋,这里给出一个完整的例子,详细学完这个例子,对用scikit-learn来运行线性回归,评估模型不会有什么问题了。 1. 获取数据,定义问题     没有...

wangxuwei
今天
1
0
MAC安装MAVEN

一:下载maven压缩包(Zip或tar可选),解压压缩包 二:打开终端输入:vim ~/.bash_profile(如果找不到该文件新建一个:touch ./bash_profile) 三:输入i 四:输入maven环境变量配置 MAVEN_HO...

WALK_MAN
今天
0
0
33.iptables备份与恢复 firewalld的9个zone以及操作 service的操作

10.19 iptables规则备份和恢复 10.20 firewalld的9个zone 10.21 firewalld关于zone的操作 10.22 firewalld关于service的操作 10.19 iptables规则备份和恢复: ~1. 保存和备份iptables规则 ~2...

王鑫linux
今天
2
0
大数据教程(2.11):keeperalived+nginx高可用集群搭建教程

上一章节博主为大家介绍了目前大型互联网项目的系统架构体系,相信大家应该注意到其中很重要的一块知识nginx技术,在本节博主将为大家分享nginx的相关技术以及配置过程。 一、nginx相关概念 ...

em_aaron
今天
1
0
Apache Directory Studio连接Weblogic内置LDAP

OBIEE默认使用Weblogic内置LDAP管理用户及组。 要整理已存在的用户及组,此前办法是导出安全数据,文本编辑器打开认证文件,使用正则表达式获取用户及组的信息。 后来想到直接用Apache Dire...

wffger
今天
2
0
HFS

FS,它是一种上传文件的软件。 专为个人用户所设计的 HTTP 档案系统 - Http File Server,如果您觉得架设 FTP Server 太麻烦,那么这个软件可以提供您更方便的档案传输系统,下载后无须安装,...

garkey
今天
1
0
Java IO类库之BufferedInputStream

一、BufferedInputStream介绍 /** * A <code>BufferedInputStream</code> adds * functionality to another input stream-namely, * the ability to buffer the input and to * sup......

老韭菜
今天
0
0
STM 32 窗口看门狗

http://bbs.elecfans.com/jishu_805708_1_1.html https://blog.csdn.net/a1985831055/article/details/77404131...

whoisliang
昨天
1
0
Dubbo解析(六)-服务调用

当dubbo消费方和提供方都发布和引用完成后,第四步就是消费方调用提供方。 还是以dubbo的DemoService举例 -- 提供方<dubbo:application name="demo-provider"/><dubbo:registry address="z...

青离
昨天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部