《数据库》第八章 多表连接及子查询

右外连接的两个表之间是有主从关系的，右外连接中右表或 者说写在逗号或者join后面的为右表，运行结果中将包含所 有右表中的记录，左表记录根据匹配关系补充右表信息，如 无关联记录则置NULL处理（如需加索引，请在左表上与右表 关联的公共列上加索引，详见下面章节）。
例：查询出学生信息与院系信息相关连的查询结果集， 要求学院信息是全部的，学生信息作为补充。
SQL> SELECT t1.s_id, t1.s_name,c_id,t2.c_name FROM t_student t1 LEFT OUTER JOIN t_college t2 USING (c_id);
19
Inspur Education
外部连接-右外连接
右外连接(RIGHT [OUTER] JOIN)：
这类连接实际应用场景较等值连接来说较为少见，例 ：查询一下学生自修课程数大于院系平均自修课程数 的学生与院系的相关信息。
SQL> SELECT t1.s_id, t1.s_name, t2.c_name, t1.s_course FROM t_student t1, t_college t2 WHERE t1.s_course > t2.c_course_avg AND t1.c_id = t2.c_id;
right [outer] join子句+on子句连接
SQL> SELECT t1.s_id, t1.s_name,t2.c_id,t2.c_name FROM t_student t1 RIGHT OUTER JOIN t_college t2 ON t1.c_id = t2.c_id;
right [outer] join子句+using子句连接
公共列是两个或更多表中存在相同数据的列，比如， t_student表和t_college表都包含一个c_id的公共列 。这里的公共列的列名不必一致，主要是存放的数据 要有一定的相关性，甚至是业务上的同类数据。
10
Inspur Education
等值连接
等值连接就是我们进行公共列关联时，使用的比较运 算符使用的是“=”等任意表示相等的运算符。
14
Inspur Education
内部连接
内连接主要有三种书写方式
逗号+where子句 [inner] join+on子句 [inner] join+using子句
内连接的两个表之间是没有主从关系的，也就是说调整 内连接的两个表的排列顺序，对内连接运行结果没有影 响（索引的设置会有影响，详见下面章节）。内连接的 运行结果的记录数分别与两个表没有直接关系，但是与 两个表之间的数据的组合有一定的关系。只有公共列能 完全匹配上的记录才会输出到查询结果中。
left [outer] join子句+on子句连接
SQL> SELECT i1.u_id,i1.u_age,i1.u_name,i2.u_shortname FROM info1 i1 LEFT OUTER JOIN info2 i2 ON i1.u_id=i2.u_id;
Left [outer] join子句+using子句连接
12
Inspur Education
交叉连接
交叉连接又称为“笛卡尔积”连接，表1中的每一个记录与表2中 的每一个记录配对，这里匹配顺序是任意的，用表2去匹配表1与表 1匹配表2结果是一致的，只不过索引增加的位置会有不同(详见下 面章节）
如果第一个表中有m条记录，第二个表中有n条记录，结果是m*n条 记录
例：查询出学生信息与院系信息相关连的查询结果集， 要求学生信息是全部的，学院信息作为补充。
18
Inspur Education
外部连接-左外连接
逗号+where子句+从表(+)连接
SQL> SELECT t1.s_id, t1.s_name,t2.c_id,t2.c_name FROM t_student t1,t_college t2 WHERE t1.c_id = t2.c_id(+);
20
Inspur Education
外部连接-右外连接
逗号+where子句+从表(+)连接
SQL> SELECT t1.s_id, t1.s_name,t2.c_id,t2.c_name FROM t_student t1,t_college t2 WHERE t1.c_id(+) = t2.c_id;
Inspur Education
第8章 多表连接及子查询
Inspur Education
知识点回顾
SELECT语句的基本语法 在SELECT子句中执行基本的算术运算 ORACLE中的伪列 使用DISTINCT或UNIQUE关键字删除重复列 常用聚合函数及分组子句GROUP BY的使用 使用ORDER BY对查询结果排序
例：查询出学生信息与院系信息相关连的基础信息。
15
Inspur Education
内部连接
逗号+where子句连接
SQL> SELECT t1.s_id, t1.s_name,t1.c_id,t2.c_name FROM t_student t1,t_college t2 WHERE t1.c_id = t2.c_id;
full [outer] join子句+using子句连接
SQL> SELECT t1.s_id, t1.s_name,c_id,t2.c_name FROM t_student t1 FULL OUTER JOIN t_college t2 USING ( Education
13
Inspur Education
交叉连接
基本的交叉连接，下例没有实际的业务意义，仅仅展示 交叉连接运行效果。
SQL>SELECT * FROM t_student t1,t_college t2; SQL>SELECT * FROM t_student t1 CROSS JOIN t_college t2;
外部连接类型：
左外连接: LEFT [OUTER] JOIN 右外连接： RIGHT [OUTER] JOIN 全外连接： FULL [OUTER] JOIN
17
Inspur Education
外部连接-左外连接
左外连接(LEFT [OUTER] JOIN)：
左外连接的两个表之间是有主从关系的，左外连接中左表或 者说写在逗号或者join前面的为左表，运行结果中将包含所 有左表中的记录，右表记录根据匹配关系补充左表信息，如 无关联记录则置NULL处理（如需加索引，请在右表上与左表 关联的公共列上加索引，详见下面章节）。
22
Inspur Education
外部连接-全外连接
full [outer] join子句+on子句连接
SQL> SELECT t1.s_id, t1.s_name,t2.c_id,t2.c_name FROM t_student t1 FULL OUTER JOIN t_college t2 ON t1.c_id = t2.c_id;
[inner] join子句+on子句连接
SQL> SELECT t1.s_id, t1.s_name,t1.c_id,t2.c_name FROM t_student t1 INNER JOIN t_college t2 ON t1.c_id = t2.c_id;
[inner] join子句+using子句连接
表连接原理
NESTED LOOP
• 数据子集较小时使用
HASH JOIN
• 两个巨大的表之间的连接 • 在一个巨大的表和一个小表中的连接
SORT MERGE JOIN
• 在行已经被排序的前提下使用为佳
6
Inspur Education
表连接原理
7
Inspur Education
数据查询语句
数据查询语句是最常用也是最复杂的语句，在 介绍查询语句之前，我们先定义两个表，以供示 例使用： 学生信息表(t_student)：
特殊连接-自连接
自连接是一种特殊的表连接方式。这种连接方式的特殊 性在于关联的两个表为同一张表
自连接可以配合如内连接、外连接、等值连接或非等值 连接一起使用。
例：查询出学生和其所在班级的班长的一个组合信息。
SQL> SELECT t1.s_id, t1.s_name,t2.s_id,t2.s_name FROM t_student t1 INNER JOIN t_student t2 ON t1.s_pid = t2.s_id;
实验数据
实验数据
t_student
t_college
9
Inspur Education
表连接的分类
实际应用场景中，表连接是对一个公共列中存储了相 同类数据的两个（或多个）表进行关联的。
根据表连接运算符的使用可以分为：等值连接和非等值连接。 根据表连接的模式的使用可以分为：内部连接和外部连接。 另外还有一些特殊连接：自连接和自然连接。
例：查询学生与院系的关联信息。
SQL> SELECT t1.s_id, t1.s_name,t2.c_name FROM t_student t1, t_college t2 WHERE t1.c_id = t2.c_id;
11
Inspur Education
非等值连接
非等值连接与等值连接最大的区别就是公共列关联时 使用的比较运算符不是“=”等相等类的运算符而是不 等于一类的比较运算符，如：>、<、>=、<=等。
学生编号(s_id)，学生姓名(s_name)、学院编号 (c_id) 、学生已修课程数(s_source)和学生所在班班 长编号(p_id)的表。
学院信息表(t_college)：
学院编号(c_id)，学院名称(c_name)和院系平均已 修课程数(c_source_avg)。
8
Inspur Education
全外连接的两个表之间是没有主从关系的，也就是说调整内 连接的两个表的排列顺序，对内连接运行结果没有影响。内 连接的运行结果的记录数分别与两个表没有直接关系，但是 与两个表之间的数据的组合有一定的关系。无论公共列能否 完全匹配上的记录都会输出到查询结果中。
我们可以简单的理解为：FULL [OUTER] JOIN 相当于LEFT [OUTER] JOIN和RIGHT [OUTER] JOIN查询的结果集再通过 UNION(不是UNION ALL)连接在一起。
由于自连接的连接表为相同的表，所有字段都一致，所 以在select子句后列举字段时要清晰的注明字段的来源 表。
24
Inspur Education
SQL> SELECT t1.s_id, t1.s_name,c_id,t2.c_name FROM t_student t1 INNER JOIN t_college t2 USING (c_id);
16
Inspur Education
外部连接
外部连接主要有三种书写方式
逗号+where子句 [outer] join+on子句 [outer] join+using子句
基本书写方式2：
• SQL>select * from t1 join t2 [on t1.id = t2.id];--方括号内为可选
• SQL>select * from t1 join t2 [using (id)];--方括号内为可选
4
Inspur Education
表连接原理
5
Inspur Education
SQL> SELECT t1.s_id, t1.s_name,c_id,t2.c_name FROM t_student t1 RIGHT OUTER JOIN t_college t2 USING (c_id);
21
Inspur Education
外部连接-全外联接
全外连接(RIGHT [OUTER] JOIN)：
2
Inspur Education
本章目标
表连接原理 多表连接查询
• 等值连接 • 非等值连接 • 交叉连接 • 内连接 • 外连接 • 特殊连接
子查询的概念及应用
• 单行子查询 • 多行子查询
3
Inspur Education
表连接原理
基本书写方式1：
• SQL>select * from t1,t2 [where t1.id = t2.id];--方括号内为可选