数据查询2—连接查询:同时涉及两个以上的表的查询
连接条件：用来连接两个表的条件
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
连接字段：连接条件中的列名称(注意：连接字段类型必须是可比的，但名字不必相同)

连接查询—(1)等值与非等值连接查询:等值连接：连接运算符为=（对比关系代数中的等值连接）

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

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;

从两个表中查询，需要先用笛卡尔积将两个表连接起来，连接之后再找出符合条件相等的。
【思考】：如何用SQL表达笛卡尔积×？

SELECT Student.*,SC.*
FROM Student,SC;

不写条件时就是笛卡尔积，将两个表连接起来，形成一个25行，8列的大表
【拓展1】非等值连接：连接形成的表减去等值连接形成的表

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno!=SC.Sno;

【拓展2】自然连接：去掉重复的列，查询内容需要依次列出.注意：对于重复的列需要用表名指定属性名，不重复的列的属性名可以不用表名限制。

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

【拓展3】对于查询两个表的所有内容也可以写成下面这种形式，更加简单：

SELECT *
FROM Student,SC
WHERE Student.Sno=SC.Sno;

【拓展4】连接操作的执行过程：
（1）嵌套循环法：在表1中找到第一个元组，然后从头扫描表2找到满足条件的元组，再将这两个元组连接起来，待表2全部扫描后，按照此流程依次完成表1的其他元组的操作。（表2每次都需要从头扫描）
(2)排序合并法：首先将表1 和表2排序，使之有规律。对表1的第一个元组，开始从头扫描顺序查找满足条件的元组，找到后将两个元组连接起来。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续.找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后l连接起来。对表1的剩余元组做重复操作。
（2）与（1）相比，（2）的效率更高。（1）中对表2的扫描次数为n，而（2）中对表2 的扫描次数只为1次。

【例3.50】、对【例 3.49】用自然连接完成。

SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
 FROM   Student,SC
 WHERE  Student.Sno = SC.Sno;

一条SQL语句可以同时完成选择和连接查询。
【例3.51】查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。

SELECT Student.Sno, Sname
FROM     Student, SC
WHERE  Student.Sno=SC.Sno  AND  SC.Cno='2' AND SC.Grade>90;

选修和成绩涉及SC表，姓名涉及Student表，多表查询；属性名用表名限制，可读性强，更加清楚。
由于Cno和Grade在Student和SC中只出现在了SC表中，可以不用表名限制，直接写出来

    SELECT Student.Sno, Sname
    FROM     Student, SC
    WHERE  Student.Sno=SC.Sno  AND  Cno='2' AND Grade>90;

执行过程：先从SC中选择出Cno='2’并且Grade>90的元组形成一个中间关系，再和Student中满足连接条件的元组进行连接得到最终的结果关系。

连接查询—(2)自身连接：一个表与其自己进行连接
需要给表起别名以示区别
所有属性名都是同名属性，因此必须使用“别名”
【例3.52】查询每一门课的间接先修课（即先修课的先修课）.

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

第一个表的先修课Cpno与第二个表的课程号Cno相对应，我们需要查询的信息是第一个表的课程号Cno与对应的第二个表的先修课Cpno。



注意：对于标准SQL来说查询结果为NULL的不计入结果，但是T-SQL的结果会包含NULL。

连接查询—(3)外连接
外连接与普通连接的区别：普通连接操作只输出满足连接条件的元组；外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
左外连接：列出左边关系中所有的元组。
右外连接：列出右边关系中所有的元组。

【例3.53】改写【例3.49】：查询每个学生及其选修课程的情况

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno);

这种形式在SQL Server 中会出现错误，显示：“‘OUT’ 不是可以识别的 join 选项。”
注意：在T-SQL中，把OUT改为OUTER

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
/*或者是直接去掉OUTER
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student  LEFT JOIN SC ON (Student.Sno=SC.Sno);*/

由于没有选课，就没有他们的选课信息Student表的Sno无法与SC表的Sno匹配。用左外连接可以看到没有选课的学生，但是等值连接或自然连接不能看到，等值连接（自然连接）只可以看到选课学生的信息。
【拓展一】左外连接：LEFT JOIN 或者LEFT OUTER JOIN
【拓展二】右外连接：RIGHT JOIN 或者 RIGHT OUTER JOIN,以右表为基础，查询左表中符合条件的信息，不存在的用NULL补充。

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM SC RIGHT OUTER JOIN Student ON (Student.Sno=SC.Sno);
/*或者
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM SC RIGHT  JOIN Student ON (Student.Sno=SC.Sno);*/

【拓展三】外连接：（完全连接）FULL JOIN 或者FULL OUTER JOIN,将两个表中的信息都查出来，没有对应的信息用NULL补充。

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student FULL OUTER JOIN SC ON (Student.Sno=SC.Sno);

经测试，OUTER可以存在也可以不存在。

连接查询—(4)多表连接：两个以上的表进行连接
【例3.54】查询每个学生的学号、姓名、选修的课程名及成绩.

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

要查询的内容涉及到了三个表的内容，有实际意义的可以连接。

数据查询3—嵌套查询
1、一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子或HAVING短语的条件中的查询称为嵌套查询。
2、SQL语言允许多层嵌套查询；子查询的语句中不能使用ORDER BY 子句
3、不相关子查询：子查询的查询条件不依赖于父查询。由里向外逐层处理，内层完成后向外层传递值。
4、相关子查询：子查询的查询条件依赖于父查询，由外向里
执行过程：
(1)首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表
(2)然后再取外层表的下一个元组
(3)重复这一过程，直至外层表全部检查完为止

嵌套查询—(1)带有IN谓词的子查询
【例3.55】查询与“刘晨”在同一个系学习的学生。
方法一：分步完成，先确定“刘晨所在的系”，再查询该系中的所有学生

/*①*/
SELECT Sdept
FROM Student
WHERE Sname='刘晨';/*结果为：CS*/
/*②*/
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept='CS';

方法二：不相关子查询：将第一步查询使用IN谓词嵌入到第二部查询的条件中,这种查询为不相关子查询。（更推荐使用）

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

方法三：使用自身连接完成查询要求。自身连接要有“别名”。将两张表用Sdept连接起来，第二张表的Sno为“刘晨”的所对应的第一张表的Sname即为所求。

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

【例3.56】查询选修了课程名为“信息系统”的学生学号和姓名.
方法一：不相关查询（由内到外），可以先

/*③*/SELECT Sno,Sname
FROM Student
WHERE Sno IN
	(/*②*/SELECT Sno
	FROM SC
	WHERE Cno IN
		(/*①*/SELECT Cno
		FROM Course
		WHERE Cname='信息系统')
	);

①先再Course表中找出“信息系统”的课程号，为3号。
②再在SC表中找出选修了3号课程的学生学号。
③最后在Student表中找出需要的学生学号和姓名。

方法二：连接查询：在连接之后的大表中找出选修课程名为“信息系统”的学号和姓名。
注意在查询时要明确是哪个表的Sno，不然会出现错误。

SELECT Student.Sno,Sname
FROM SC,Student,Course
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Course.Cname='信息系统';

【例3.57】找出每个学生超过他选修课程平均成绩的课程号。
相关查询

SELECT Sno, Cno
FROM    SC  x
WHERE Grade >=(SELECT AVG(Grade) 
		        FROM  SC y
                WHERE y.Sno=x.Sno);
                /*由于是自己的平均成绩，所以是两张表自己的学号*/

把一张表看作两张表，需要用“别名”，由于内层里面用到了外层的表SC x,所以顺序是由外向里的顺序.
执行过程：（1）从外层查询中取出SC的第一个元组x，将元组x的Sno值（201215121）传递给内层查询。

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

(2)外层传递的值不变，依次取出内层查询的各个满足条件元组，直至不满足条件的执行内层查询，得到Sno的平均成绩88，再将该值传递给外层.

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

(3）执行完成后再取出外层查询的下一个元组重复上述步骤，直至外层元组全部查询完毕。

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

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

嵌套查询—(3)带有ANY(SOME)或ALL谓词的子查询


其中，< ANY等价于< MAX, < ALL 等价于 < MIN
任意----ANY， 所有-----ALL
【例3.58】查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄.
方法一：

SELECT Sname,Sage
FROM Student
WHERE Sdept!='CS' AND Sage<ANY
					(SELECT Sage
					 FROM Student
					 WHERE Sdept='CS');

执行过程：
（1）首先处理子查询，找出CS系中所有学生的年龄，构成一个集合（20,19）
（2）处理父查询，找所有不是CS系且年龄小于 20 或 19的学生
方法二：用聚集函数实现

SELECT Sname,Sage
FROM Student
WHERE Sdept!='CS' AND Sage<
					(SELECT MAX (Sage)
					 FROM Student
					 WHERE Sdept='CS');

【例 3.59】 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
方法一：用ALL谓词

SELECT Sname,Sage
FROM Student
WHERE Sdept <> 'CS' AND Sage  < ALL
						(SELECT Sage
						FROM Student
						WHERE Sdept='CS');

方法二：用聚集函数

SELECT Sname,Sage
FROM Student
WHERE Sdept <> 'CS' AND Sage  < 
						(SELECT MIN( Sage)
						FROM Student
						WHERE Sdept='CS');