第八章-多表连接及子查询

《第八章-多表连接及子查询》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

第8章多表连接及子查询

1知识点回顾SELECT语句的基本语法在SELECT子句中执行基本的算术运算ORACLE中的伪列使用DISTINCT或UNIQUE关键字删除重复列常用聚合函数及分组子句GROUPBY的使用使用ORDERBY对查询结果排序2

2本章目标表连接原理多表连接查询等值连接非等值连接交叉连接内连接外连接特殊连接子查询的概念及应用单行子查询多行子查询3

3表连接原理4基本书写方式1：SQL>select*fromt1,t2[wheret1.id=t2.id];--方括号内为可选基本书写方式2：SQL>select*fromt1joint2[ont1.id=t2.id];--方括号内为可选SQL>select*fromt1joint2[using(id)];--方括号内为可选

4表连接原理5

5表连接原理NESTEDLOOP数据子集较小时使用HASHJOIN两个巨大的表之间的连接在一个巨大的表和一个小表中的连接SORTMERGEJOIN在行已经被排序的前提下使用为佳6

6表连接原理7

7数据查询语句数据查询语句是最常用也是最复杂的语句，在介绍查询语句之前，我们先定义两个表，以供示例使用：学生信息表(t_student)：学生编号(s_id)，学生姓名(s_name)、学院编号(c_id)、学生已修课程数(s_source)和学生所在班班长编号(p_id)的表。学院信息表(t_college)：学院编号(c_id)，学院名称(c_name)和院系平均已修课程数(c_source_avg)。8

8实验数据实验数据t_studentt_college9

9表连接的分类实际应用场景中，表连接是对一个公共列中存储了相同类数据的两个（或多个）表进行关联的。根据表连接运算符的使用可以分为：等值连接和非等值连接。根据表连接的模式的使用可以分为：内部连接和外部连接。另外还有一些特殊连接：自连接和自然连接。公共列是两个或更多表中存在相同数据的列，比如，t_student表和t_college表都包含一个c_id的公共列。这里的公共列的列名不必一致，主要是存放的数据要有一定的相关性，甚至是业务上的同类数据。10

10等值连接等值连接就是我们进行公共列关联时，使用的比较运算符使用的是“=”等任意表示相等的运算符。例：查询学生与院系的关联信息。11SQL>SELECTt1.s_id,t1.s_name,t2.c_nameFROMt_studentt1,t_colleget2WHEREt1.c_id=t2.c_id;

11非等值连接非等值连接与等值连接最大的区别就是公共列关联时使用的比较运算符不是“=”等相等类的运算符而是不等于一类的比较运算符，如：>、<、>=、<=等。这类连接实际应用场景较等值连接来说较为少见，例：查询一下学生自修课程数大于院系平均自修课程数的学生与院系的相关信息。12SQL>SELECTt1.s_id,t1.s_name,t2.c_name,t1.s_courseFROMt_studentt1,t_colleget2WHEREt1.s_course>t2.c_course_avgANDt1.c_id=t2.c_id;

12交叉连接交叉连接又称为“笛卡尔积”连接，表1中的每一个记录与表2中的每一个记录配对，这里匹配顺序是任意的，用表2去匹配表1与表1匹配表2结果是一致的，只不过索引增加的位置会有不同(详见下面章节）如果第一个表中有m条记录，第二个表中有n条记录，结果是m*n条记录13

13交叉连接基本的交叉连接，下例没有实际的业务意义，仅仅展示交叉连接运行效果。SQL>SELECT*FROMt_studentt1,t_colleget2;SQL>SELECT*FROMt_studentt1CROSSJOINt_colleget2;14

14内部连接内连接主要有三种书写方式逗号+where子句[inner]join+on子句[inner]join+using子句内连接的两个表之间是没有主从关系的，也就是说调整内连接的两个表的排列顺序，对内连接运行结果没有影响（索引的设置会有影响，详见下面章节）。内连接的运行结果的记录数分别与两个表没有直接关系，但是与两个表之间的数据的组合有一定的关系。只有公共列能完全匹配上的记录才会输出到查询结果中。例：查询出学生信息与院系信息相关连的基础信息。15

15内部连接逗号+where子句连接[inner]join子句+on子句连接[inner]join子句+using子句连接16SQL>SELECTt1.s_id,t1.s_name,t1.c_id,t2.c_nameFROMt_studentt1,t_colleget2WHEREt1.c_id=t2.c_id;SQL>SELECTt1.s_id,t1.s_name,t1.c_id,t2.c_nameFROMt_studentt1INNERJOINt_colleget2ONt1.c_id=t2.c_id;SQL>SELECTt1.s_id,t1.s_name,c_id,t2.c_nameFROMt_studentt1INNERJOINt_colleget2USING(c_id);

16外部连接外部连接主要有三种书写方式逗号+where子句[outer]join+on子句[outer]join+using子句外部连接类型：左外连接:LEFT[OUTER]JOIN右外连接：RIGHT[OUTER]JOIN全外连接：FULL[OUTER]JOIN17

17外部连接-左外连接左外连接(LEFT[OUTER]JOIN)：左外连接的两个表之间是有主从关系的，左外连接中左表或者说写在逗号或者join前面的为左表，运行结果中将包含所有左表中的记录，右表记录根据匹配关系补充左表信息，如无关联记录则置NULL处理（如需加索引，请在右表上与左表关联的公共列上加索引，详见下面章节）。例：查询出学生信息与院系信息相关连的查询结果集，要求学生信息是全部的，学院信息作为补充。18

18外部连接-左外连接逗号+where子句+从表(+)连接left[outer]join子句+on子句连接Left[outer]join子句+using子句连接19SQL>SELECTt1.s_id,t1.s_name,t2.c_id,t2.c_nameFROMt_studentt1,t_colleget2WHEREt1.c_id=t2.c_id(+);SQL>SELECTi1.u_id,i1.u_age,i1.u_name,i2.u_shortnameFROMinfo1i1LEFTOUTERJOINinfo2i2ONi1.u_id=i2.u_id;SQL>SELECTt1.s_id,t1.s_name,c_id,t2.c_nameFROMt_studentt1LEFTOUTERJOINt_colleget2USING(c_id);

19外部连接-右外连接右外连接(RIGHT[OUTER]JOIN)：右外连接的两个表之间是有主从关系的，右外连接中右表或者说写在逗号或者join后面的为右表，运行结果中将包含所有右表中的记录，左表记录根据匹配关系补充右表信息，如无关联记录则置NULL处理（如需加索引，请在左表上与右表关联的公共列上加索引，详见下面章节）。例：查询出学生信息与院系信息相关连的查询结果集，要求学院信息是全部的，学生信息作为补充。20

20外部连接-右外连接逗号+where子句+从表(+)连接right[outer]join子句+on子句连接right[outer]join子句+using子句连接21SQL>SELECTt1.s_id,t1.s_name,t2.c_id,t2.c_nameFROMt_studentt1,t_colleget2WHEREt1.c_id(+)=t2.c_id;SQL>SELECTt1.s_id,t1.s_name,t2.c_id,t2.c_nameFROMt_studentt1RIGHTOUTERJOINt_colleget2ONt1.c_id=t2.c_id;SQL>SELECTt1.s_id,t1.s_name,c_id,t2.c_nameFROMt_studentt1RIGHTOUTERJOINt_colleget2USING(c_id);

21外部连接-全外联接全外连接(RIGHT[OUTER]JOIN)：全外连接的两个表之间是没有主从关系的，也就是说调整内连接的两个表的排列顺序，对内连接运行结果没有影响。内连接的运行结果的记录数分别与两个表没有直接关系，但是与两个表之间的数据的组合有一定的关系。无论公共列能否完全匹配上的记录都会输出到查询结果中。我们可以简单的理解为：FULL[OUTER]JOIN相当于LEFT[OUTER]JOIN和RIGHT[OUTER]JOIN查询的结果集再通过UNION(不是UNIONALL)连接在一起。22

22外部连接-全外连接full[outer]join子句+on子句连接full[outer]join子句+using子句连接23SQL>SELECTt1.s_id,t1.s_name,t2.c_id,t2.c_nameFROMt_studentt1FULLOUTERJOINt_colleget2ONt1.c_id=t2.c_id;SQL>SELECTt1.s_id,t1.s_name,c_id,t2.c_nameFROMt_studentt1FULLOUTERJOINt_colleget2USING(c_id);

23特殊连接-自连接自连接是一种特殊的表连接方式。这种连接方式的特殊性在于关联的两个表为同一张表自连接可以配合如内连接、外连接、等值连接或非等值连接一起使用。例：查询出学生和其所在班级的班长的一个组合信息。由于自连接的连接表为相同的表，所有字段都一致，所以在select子句后列举字段时要清晰的注明字段的来源表。24SQL>SELECTt1.s_id,t1.s_name,t2.s_id,t2.s_nameFROMt_studentt1INNERJOINt_studentt2ONt1.s_pid=t2.s_id;

24特殊连接-自然连接自然连接是一种特殊的表连接，这种连接方式的特殊性在于关联的两个表中有同名字段。自然连接可以配合如内连接、外连接、等值连接或非等值连接一起使用。例：我们再做一遍内连接的例子。由于自然连接是直接使用两个连接表中同名字段作为关联关系，所以这个同名字段如果需要在select后列举时，请一定不要注明字段的来源表。25SQL>SELECTt1.s_id,t1.s_name,c_id,t2.c_nameFROMt_studentt1NATURALINNERJOINt_colleget2;

25子查询子查询是一个嵌套查询——另一个查询内部的完整查询子查询说明单行子查询将包含一列的一行结果返回到外部查询多行子查询将多行结果返回到外部查询相关(关联)子查询引用外部查询中的一列，对外部查询中的每一行执行一次子查询不相关（非关联）子查询首先执行子查询，然后将值传递给外部查询26

26子查询的使用方式当我们需要一个中间结果集/值时，我们就需要使用子查询。返回单值的子查询通常用在WHERE和HAVING子句里。子查询一般可以出现于限定条件处以及查询表处的位置：SQL>SELECTfield1,field2,field3FROMtable1WHEREfield1OPERATER(SELECTfield4FROMtable2);SQL>SELECTt.f1,t.f2,t.f3FROM(SELECTt2.*，t3.*FROMt2joint3ont2.f=t3.f)t;。27

27子查询及其用法规则：子查询必须“自身就是一个完整的查询”——也就是说至少包括一个SELECT子句和一个FROM子句子查询不能包括ORDERBY子句。如果显示输出需要按照特定顺序显示，那么ORDERBY子句应该作为外部查询的最后一个子句列出子查询“必须包括在一组括号中”，以便将它与外部查询分开。如果将子查询在外部查询的WHERE或HAVING子句中，那么该子句推荐放在比较运算符的“右边”。28

28单行子查询单行子查询就是子查询中查询出的结果集只有一条记录。单行子查询使用的比较运算符等于：=大于：>大于等于:>=小于:<小于等于:<=不等于:!=例：查询学院一里的所有学生的全部信息。29SQL>SELECT*FROMt_studentWHEREc_id=(SELECTc_idFROMt_collegeWHEREc_name='学院一');

29多行子查询多行子查询就是子查询中查询出的结果集不止一条记录。多行子查询使用的运算符inexistsanyAll注意：多行子查询使用的运算符也可以应用于单行子查询，但是单行子查询的运算符不可用于多行子查询。30

30多行子查询-IN运算符in是包含的意思，也就是说将外部查询条件限定为内部查询中的所有记录，限定条件包含在内部查询结果中。例：查询一下学院一和学院三中的学生信息SQL>SELECT*FROMt_studentWHEREc_idIN(SELECTc_idFROMt_collegeWHEREc_nameIN('学院一','学院三'));31

31多行子查询-EXISTS子句exists为存在的意思，外部查询中需要的查询条件在子查询中存在，或者说子查询中存在的结果就是外部查询中需要的条件。例：继续使用讲解in的例子场景。32SQL>SELECT*FROMt_studentt1WHEREEXISTS(SELECTc_idFROMt_colleget2WHEREc_nameIN('学院一','学院三')ANDt1.c_id=t2.c_id);

32IN与EXISTS的区别与联系IN与EXISTS子查询的相似点适用于外部查询中的条件限定部分。可以在in或exists前配合not关键字达到逻辑非的效果。IN与EXISTS子查询的不同点IN子句外部查询与子查询之间的关联字段信息分别存在于外部查询与子查询里。EXISTS子句外部查询与子查询之间的关联字段信息完全存在于子查询中。IN子句适合外部查询数据量比较大，子查询数据量相对较小的模式。而EXISTS子句适合外部查询数据量较小，子查询数据量较大的情况。如外部查询与子查询数据量接近，那么整体查询效率也接近。33

33多行子查询-ANY和ALL运算符说明>ALL大于子查询返回的最大值ANY大于子查询返回的最小值=ANY等于子查询返回的任何值（于IN相同）34

34多行子查询—ANY运算符any意为任意一个，也就是子查询查询结果中的任意一个，这里如果使用“=”进行比较运算，效果等同于IN子句，如果使用“>”进行比较运算，效果等同于大于最小的一个，大于结果集最小的一个就等于大于结果集任意一个，至少有一个符合。如果用“<”进行运算，效果等同于小于最大的一个，小于最大的一个就等于小于结果集中任意一个，至少有一个符合。例：查询出任意一个学生所修课程数高于院系平均课程数的学院信息SQL>SELECTc_id,c_nameFROMt_collegeWHEREc_course_avg

35多行子查询—ANY运算符等同于any的另一种写法。例：继续使用any的演示场景外部查询的“<”和子查询的MAX函数以及外部查询的“>”和子查询的MIN函数的组合都可以模拟与any运算的一个相同的结果。SQL>SELECTc_id,c_nameFROMt_collegeWHEREc_course_avg<(SELECTMAX(s_course)FROMt_student);36

36多行子查询—ALL运算符all意为全部，也就是子查询查询结果中的全部，如果使用“>”进行比较运算，效果等同于大于最大的一个，大于结果集最大的一个就等于大于结果集中所有的。如果用“<”进行运算，效果等同于小于最小的一个，小于最小的一个就等于小于结果集中所有的。例：查询出全部学生所修课程数都要高于院系平均课程数的学院信息SQL>SELECTc_id,c_nameFROMt_collegeWHEREc_course_avg

37多行子查询—ALL运算符等同于all的另一种写法。例：继续使用all的演示场景外部查询的“<”和子查询的MIN函数以及外部查询的“>”和子查询的MAX函数的组合都可以模拟与all运算的一个相同的结果。SQL>SELECTc_id,c_nameFROMt_collegeWHEREc_course_avg

38在FROM里面的子查询当子查询的位置处于查询表位置，或者说FROM后面时，那么子查询的这个查询结果就相当于一个普通的表的作用，因此，在FROM子句我们同样可以使用子查询。这个特性很有用，FROM里的子查询还可以让我们获得多于一个回合的分组/聚集特性，而不需要求助于临时表。这种嵌套可以使用多次，大大提高了查询的便利性。例：只查询学生编码与学生名称的信息。39SQL>SELECT*FROM(SELECTs_id,s_nameFROMt_student);

39总结表连接原理多表连接查询等值连接非等值连接交叉连接内连接外连接特殊连接子查询的概念及应用单行子查询多行子查询40

40谢谢!41