连接查询与嵌套查询

连接查询

1. 等值连接
2. 自身连接
3. 外连接
4. 多表连接

一、等值连接

• 连接符号 "="

连接操作的几种算法

1. 循环嵌套法（NESTED-LOOP）
   • 相当于两层for循环，数据量大时效率不高
   • 先在表1中找到第一个元组，然后从头开始遍历表2的所有元组。找到并拼接表一和表2的元组后，再找表1中的第2个元组，再遍历表2.
   • 重复上述操作，知道表1中的全部元组都处理完毕。
2. 排序合并连接算法（SORT-MERGE）
   • 常用于等值连接 , 相当于循环嵌套连接的变种
   • 首先按连接属性对表1和表2排序
   • 设置指针分别指向表1和表2的一个元组，此时以表1指针为参照，表2扫描，如果符合连接条件，则元组拼接并两个指针一起后移动一位。
   • 如果不匹配，则以表2指针为参照，表1扫描，（也就是教材中所说的：较小值得指针后移一个元组。）
   • 重复以上步骤，（核心：表1表2得指针轮换着来扫描）
3. 索引连接（INDEX-JOIN） 好像很少用，网上关于它的信息很少
   • 也相当于嵌套循环连接得变种
   • 对表2按连接字段建立索引
   • 对表一的每个元组，依次查询表2的索引。

二、自身连接

• 一个表与其自己进行连接，是一种特殊的连接
• 需要给表起一个别名以示区别
• 由于所有属性名都是同名属性，因此必须使用别名前缀。

例题1.查询每一门课的直接先修课程的名称

SELECT FIRST.Cname , SECOND.Cname
FROM Course FIRST , Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;

结果：

三、外连接

外连接与普通连接的区别：

• 普通连接只输出满足条件的元组
• 外连接以指定表为连接主体，将主题表中不满足的元组也一并输出（怎么说？？？）
• 左外连接：
  • 列出左边关系中所有的元组
• 右外连接
  • 列出右边关系中所有的元组

例题2：

//普通连接
SELECT Student.Sno,Sname, Cno 
FROM Student , SC
WHERE Student.Sno = SC.Sno;

//左外连接
SELECT Student.Sno,Sname, Cno 
FROM Student LEFT OUT JOIN  SC ON
	(Student.Sno = SC.Sno);    //WHERE都省去了
这样写会出现 ‘OUT’ 不是可以识别的 join 选项 的报错。需要将OUT改成OUTER，或者将OUT去掉也行。

//左外连接 ，有些商业系统更简介的表达
SELECT Student.Sno,Sname, Cno 
FROM Student , SC
WHERE Student.Sno （+）= SC.Sno;

四、 多表连接

• 两个以上的表进行连接

**例题3：.**查询每个学生的学号、姓名、选修的课程名以及成绩

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno
		AND SC.Cno = Course.Cno;

五、嵌套查询

• 子查询限制：不能使用ORDER BY 子句，因为这个是针对结果用的
  不相关查询
• 子查询条件不依赖外层查询，由内向外逐层查询
• 带IN谓词的子查询

[例 3.55] 查询与“刘晨”在同一个系学习的学生。
方法一：分步完成
① 确定“刘晨”所在系名：

SELECT  Sdept  
FROM  Student                            
WHERE  Sname= '刘晨';

结果为： CS
② 查找所有在CS系学习的学生：

SELECT   Sno, Sname, Sdept     
FROM   Student                 
WHERE   Sdept= 'CS';

方法二：将第一步查询嵌入到第二步查询的条件中

SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
      (SELECT Sdept
       FROM Student
       WHERE Sname= '刘晨');

方法三：自身连接

SELECT  S1.Sno, S1.Sname,S1.Sdept
FROM  Student S1,Student S2
WHERE  S1.Sdept = S2.Sdept 
       AND S2.Sname = '刘晨';

• 带有比较运算符的子查询
  当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。
  在[例 3.55]中，由于一个学生只可能在一个系学习，则可以用 = 代替IN ：

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept =
      (SELECT Sdept
      FROM Student
      WHERE Sname='刘晨');

[例 3.57 ]找出每个学生超过他选修课程平均成绩的课程号。

SELECT Sno, Cno
FROM SC x
WHERE Grade >=(SELECT AVG(Grade)
               FROM SC y
               WHERE y.Sno=x.Sno);

相关子查询：子查询的查询条件依赖于父查询
首先取外层查询表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表。然后再取外层表的下一个元组。重复这一过程，直至外层表全部检查完为止。

执行过程 ：
从外层查询中取出SC的一个元组x，将元组x的Sno值（201215121）传送给内层查询。

SELECT AVG(Grade)
FROM SC y
WHERE y.Sno='201215121‘;

执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询：

SELECT Sno,Cno
FROM SC x
WHERE Grade >=88;

执行这个查询，得到：

（201215121,1）
（201215121,3）

然后外层查询取出下一个元组重复做上述①至③步骤，直到外层的SC元组全部处理完毕。结果为:

（201215121,1）
（201215121,3）
（201215122,2）