SQL查询--连接查询

连接查询

同时涉及多个表的查询称为连接查询

连接查询的WHERE子句（前面只是用它在一个表中设定条件）中用来连接两个表的条件称为连接条件或连接谓词，一般格式：

1. [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
   1. 比较运算符：=、>、<、>=、<=、!=
   2. 连接运算符为=时，称为等值连接（若同时把结果列中重复的属性列去掉[其实就是你写select语句的时候不加进去] 就叫自然连接）；使用其他运算符称为非等值连接（下面这个也是）
2. [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2>AND [<表名2>.]<列名3>

连接字段

1. 连接谓词中的列名称为连接字段
2. 连接条件中的各连接字段类型必须是可比的，但不必是相同的

1. 入门

【例】查询每个学生及其选修课程的情况

Student SC

学生情况存放在Student表中，学生选课情况存放在SC表中，所以本查询实际上涉及两个表，这两个表之间的联系是通过公共属性Sno实现的

SELECT Student.* , SC.* //加上表名前缀，是为了混淆，如果属性名在参加连接的各表中是唯一的，可以省略表名前缀

FROM Student,SC

WHERE Student.Sno=SC.Sno; //连接

于是查询结果：

RDBMS执行该连接操作的几种可能过程是：

嵌套循环法(NESTED-LOOP)

1. 首先在表Student属性列Sno中找到第一个元组的属性，然后从头开始扫描表SC的Sno属性列，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组
2. 表2全部查找完后，再找表1中第二个元组，跟一一样，再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。
3. 重复上述操作，直到表1中的全部元组都处理完毕

排序合并法(SORT-MERGE)

1. 常用于=连接
2. 首先按连接属性对表1和表2排序
3. 对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续
4. 找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续
5. 重复上述操作，直到表1或表2中的全部元组都处理完毕为止

索引连接(INDEX-JOIN)

1. 对表2按连接字段建立索引
2. 对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组

2. 广义笛卡尔积（复习：关系代数）

R——n目（属性）关系，k1个元组

S——m目关系，k2个元组

R×S

1. 列：（n+m）列的元组的集合
   1. 元组的前n列是关系R的一个元组
   2. 后m列是关系S的一个元组
2. 行：k1×k2个元组
   1. R×S = {tr,ts |tr∈R ∧ ts∈S }

广义笛卡尔积对应了不带连接谓词的连接

两个表的广义笛卡尔积是两表中元组的交叉乘积，其连接的结果会产生一些没有意义的元组，这种语法很少使用

例：

SELECT Student.* , SC.*

FROM Student, SC

关系代数：Student×SC

结果如图：

3.等值与非等值连接查询

3.1.复习：关系代数

1. 连接也称为θ连接
2. 连接运算的含义
   1. 从两个关系的笛卡尔积中选取属性间满足一定条件的元组
   2. 
      1. A和B：分别为R和S上度数相等且可比的属性组
      2. θ：比较运算符
   3. 连接运算从【R和S的广义笛卡尔积R×S】中选取【R关系在A属性组上的值 与 S关系在B属性组上的值 满足比较关系 的元组】
3. 两类常用连接运算
   1. 等值连接（equi join）
      1. θ为“＝”的连接运算称为等值连接
      2. 从关系R与S的广义笛卡尔积中选取A、B属性值相等的那些元组，即等值连接为：
         
      3. 
   2. 自然连接（Natural join）
      1. 自然连接是一种特殊的等值连接
         1. 两个关系中进行比较的分量必须是相同的属性组
         2. 在结果中把重复的属性列去掉
      2. R和S具有相同的属性组B
      3. 
      4. 一般的连接操作是从行的角度进行运算，自然连接还需要取消重复列，所以是同时从行和列的角度进行运算
      5. 自然连接要做三件事：
         1. 计算R х S
         2. 挑选R.A[i]=S.A[i]的 所有元组。
         3. 去掉重复属性。
      6. 

3.2.等值连接查询

连接运算符为 = 的连接操作

1. [<表名1>.]<列名1> = [<表名2>.]<列名2>
2. 任何子句中引用表1和表2中同名属性时，都必须加表名前缀
3. 引用唯一属性名时可以加也可以省略表名前缀

【例32】查询每个学生及其选修课程的情况。

SELECT Student.*，SC.*

FROM Student，SC

WHERE Student.Sno = SC.Sno；

关系代数：

查询选修了课程的学生的学号和姓名。

SELECT SNO,SNAME

FROM STUDENT,SC

WHERE STUDENT.SNO=SC.SNO

3.3.自然连接连接查询

【例】 对[例32]用自然连接完成（就是在select语句中手动不写重复列）

SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade //因为Sno在两个表都出现了，因此引用时必须加上表名前缀

FROM Student，SC

WHERE Student.Sno = SC.Sno；

关系代数：

3.4.非等值连接查询

连接运算符 不是 = 的连接操作

1. [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
   1. 比较运算符：>、<、>=、<=、!=、<>（不等于）
2. [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2>AND [<表名2>.]<列名3>

SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade

FROM Student，SC

WHERE Student.Sno <> SC.Sno；

4.自身连接查询

1. 一个表与其自己进行连接，称为表的自身连接
2. 需要给表起别名以示区别
3. 由于所有属性名都是同名属性，因此必须使用别名前缀

[例34] 查询每一门课的间接先修课（即先修课的先修课）

在Course表中，只有每门可的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程，这就要将Course表与其自身连接。

为此，要为Course表取两个别名（怎么取？下面有），一个是FIRST，另一个是SECOND

查询语句为：

SELECT FIRST.Cno，SECOND.Cpno

FROM Course FIRST，Course SECOND

WHERE FIRST.Cpno = SECOND.Cno；

查询结果

5.复合条件连接

1. WHERE子句中含多个连接条件时（上面都是只有一个），称为复合条件连接

[例35]查询选修2号课程且成绩在90分以上的所有学生的学号、姓名

SELECT Student.Sno, student.Sname

FROM Student, SC

WHERE Student.Sno = SC.SnoAND /* 连接谓词*/

SC.Cno= ' 2 ' AND SC.Grade > 90；/* 其他限定条件 */

该查询的一种优化（高效）的执行过程是：

1. 先从SC中挑选出Cno= ' 2 '并且Grade > 90的元组形成一个中间关系
2. 再和Student中满足连接条件的元组进行连接得到最终的结果关系

6.多表连接

1. 连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，叫多表连接

[例36] 查询每个学生的学号、姓名、选修的课程名及成绩（涉及到三个表）

SELECT Student.Sno，Sname，Cname，Grade

FROM Student，SC，Course

WHERE Student.Sno = SC.Sno

and SC.Cno = Course.Cno；

结果：

Student.Sno Sname Cname Grade

95001 李勇 数据库 92

95001 李勇 数学 85

95001 李勇 信息系统 88

95002 刘晨 数学 90

95002 刘晨 信息系统 80

7.连接高级语法

1. 20世纪80年代的SQL标准中，SELECT语句中无直接的连接和自然连接操作。
2. SQL对此进行改进，用较为直接的形式表示各式各样的连接操作，这些操作可在From子句中以直接形式指出
3. 高级语法中连接操作符分成连接类型和连接条件。
   1. 连接类型决定了如何处理连接条件中不匹配的元组。
   2. 连接条件决定了两个关系中哪些元组应该匹配。

连接类型：

INNER JOIN (内连接）

LEFT OUTER JOIN (左外连接)

RIGHT OUTER JOIN(左外连接)

FULL OUTER JOIN (全外连接)

连接条件；

NATURAL ( 应写在连接类型的左边）

ON 等值连接条件 ( 应写在连接类型的右边）

USING (A1,A2,…An) ( 应写在连接类型的右边）

连接条件：

1. NATUAL：表示两个关系执行自然连接，即在两个关系的公共属性上作等值连接，运算结果中公共属性只出现一次
2. ON等值条件：具体列出两个关系在哪些相应属性上做等值连接，类似于where
3. USING (A1,A2,…An)类似于NATUAL， 这里A1,A2,…An是两个关系上的公共属性，可以不是全部公共属性。连接结果中，公共属性A1,A2,…An 只出现一次

若连接条件是INNER JOIN，没有连接条件，则相当于笛卡尔积，此操作称为CROSS JOIN

例：

STUDENT NATURAL LEFT OUTER JOIN S

STUDENT LEFT OUTER JOIN SC USING(SNO)

STUDENT LEFT OUTER JOIN SC ON

STUDENT.SNO=SC.SNO

7.1.外连接（Outer Join）

外连接与普通连接的区别

1. 普通连接操作只输出从两个关系的笛卡尔积中选取属性间满足条件的元组
2. 外连接操作以指定表为连接主体，不仅从两个关系的笛卡尔积中选取属性间满足一定条件的元组 ，还将主体表中【某些根据条件选中】的不满足连接条件的元组一并输出（某些属性可以填Null）

外连接

1. 非主体表有一“万能”的虚行，该行全部由空值组成
2. 虚行可以和主体表中所有不满足连接条件的元组进行连接
3. 由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值
4. 有些系统支持在表名后面加外连接操作符(*)或(+)指定非主体表
   1. 左外连接—— 外连接符出现在连接条件的左边
   2. 右外连接—— 外连接符出现在连接条件的右边

【例】查询每个学生及其选修课程的情况

Student SC

查询语句：

SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade

FROM Student INNER JOIN SC

ON Student.Sno = SC.Sno

【例】查询每个学生及其选修课程的情况包括没有选修课程的学生----用外连接操作

1）左外连接查询语句：

SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade

FROM Student LEFT OUTER JOIN SC

ON Student.Sno = SC.Sno

结果：

Student.Sno Sname Ssex Sage Sdept Cno Grade

95001 李勇 男 20 CS 1 92

95001 李勇 男 20 CS 2 85

95001 李勇 男 20 CS 3 88

95002 刘晨 女 19 IS 2 90

95002 刘晨 女 19 IS 3 80

95003 王敏 女 18 MA

95004 张立 男 19 IS

2）右外连接查询语句（假设后边多一行9005）：

SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade

FROM Student RIGHT OUTER JOIN SC

ON Student.Sno = SC.Sno

结果：

Student.Sno Sname Ssex Sage Sdept SC.Sno Cno Grade

95001 李勇 男 20 CS 95001 1 92

95001 李勇 男 20 CS 95001 2 85

95001 李勇 男 20 CS 95001 3 88

95002 刘晨 女 19 IS 95002 2 90

95002 刘晨 女 19 IS 95002 3 80

95005 2 80

3）全外连接查询语句：

SELECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade

FROM Student FULL OUTER JOIN SC

ON Student.Sno = SC.Sno

结果：

Student.Sno Sname Ssex Sage Sdept SC.Sno Cno Grade

95001 李勇 男 20 CS 95001 1 92

95001 李勇 男 20 CS 95001 2 85

95001 李勇 男 20 CS 95001 3 88

95002 刘晨 女 19 IS 95002 2 90

95005 3 80

95003 王敏 女 18 MA

95004 张立 男 19 IS