sql server中的集合查询

集合操作的种类

• 并操作UNION -or
• 交操作INTERSECT -and
• 差操作EXCEPT

参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同

1、查询软件工程系的学生及年龄不大于19岁的学生。

• 集合查询实现

select * from student where sdept = '软件工程'
union
select * from student where sage <= 19

• 嵌套查询实现

select * from student where sdept = '软件工程' or sage in (
	select sage from student where sage <= 19
);

• 单表查询实现

select * from student where sdept = '软件工程' or sage <= 19;

2、查询选修了课程c1或者选修选修了课程c2的学生

• 集合查询实现

select sno from sc where cno = 'c1' 
union 
select sno from sc where cno = 'c2';

• 嵌套查询实现

select distinct sno from sc where cno = 'c1' or sno in (
	select sno from sc where cno