实操环境:SQL Server Management Studio 15.0.18369.0

查询所用数据库可在《数据库笔记（七）SQL的查询操作》中查看

一、交并差运算

SQL语言：并运算 UNION, 交运算INTERSECT, 差运算EXCEPT。
基本语法形式：

        子查询 { Union [ALL] | Intersect [ALL] | Except [ALL] 子查询 }

通常情况下自动删除重复元组：不带ALL。若要保留重复的元组，则要带ALL。

例：求学过002号课的同学或学过003号课的同学学号

模式1

Select S# From SC Where C# = ‘002’
UNION
Select S# From SC Where C# = ‘003’;

模式2

Select S# From SC Where C# = ‘002’ OR C# = ‘003’;

例：已知两个表

Customers(CID, Cname, City, Discnt)
Agents(AID, Aname, City, Percent)

Select City From Customers
UNION
Select City From Agents ;

例：求既学过002号课，又学过003号课的同学学号

Select S# From SC Where C# = ‘002’
INTERSECT
Select S# From SC Where C# = ‘003’;

也可采用如下不用INTERSECT的方式来进行

Select S# From SC Where C# = ‘002’ and S# IN
(Select S# From SC Where C# = ‘003’);

例： 假定所有学生都有选课，求没学过002号课程的学生学号

Select DISTINCT S# From SC
EXCEPT
Select S# From SC Where C# = ‘002’;

当然，也可以不用UNION来表达

Select DISTINCT S# From SC SC1
Where not exists ( Select * From SC
Where C# = ‘002’ and S# = SC1.S#) ;

二、空值的处理:

假如数据有一格为空值，那么求平均数如何处理？
空值检测

is [not ] null

例：找出年龄值为空的学生姓名

select Sname FROM Student
where Sage is null

不要写成Where Sage = null;
为空的值是不参与运算的

set Sage = null Where Sname = '张三'
select Count(Sage) FROM Student

如果检索到的数据该列均为空值，等同于未检索到任何数据

select AVG(Sage) FROM Student
where Sname = '张三'

输出为null