EXCEPT和INTERSECT运算符

EXCEPT和INTERSECT运算符

EXCEPT运算符用于从前面的查询结果中返回后面查询没有找到的所有非重复记录。INTERSECT运算符用于返回前后两个查询结果集中包含的所有非重复值。和UNION运算符一样，EXCEPT 或 INTERSECT 的两个查询的结果集组合起来有以下基本规则。

所有查询中的列数和列的顺序必须相同。

数据类型必须兼容。

EXCEPT和INTERSECT运算符的使用语法如下所示。

SELECT <COLUMN_NAME>
FROM <TABLE_NAME >
WHERE < CONDITION>
{ EXCEPT | INTERSECT }
SELECT < COLUMN_NAME >
FROM < TABLE_NAME >
WHERE < CONDITION >
[,...n ]

提示
(1) 如果EXCEPT或INTERSECT运算符前后的查询结果集中包含的字段类型具有不同的排序规则，则根据排序规则优先级执行所需的比较。如果无法执行转换，Microsoft SQL Server 2005 Database Engine 将返回错误。

(2) 通过比较行来确定非重复值，当比较双方均为NULL值被视为相等。

(3) EXCEPT或INTERSECT返回的结果集的字段与操作数左侧的查询返回的列名相同。

(4) ORDER BY子句中的列名或别名必须引用左侧查询返回的列名。

【示例17】在sampleDB数据库中，查询student表和newstudent表中共有的学生的姓名、学号以及班级信息。

完整的设计步骤如下。

(1) 在查询编辑器中输入以下代码：

SELECT Name,Numb,Class FROM student INTERSECT SELECT Name,Numb,Class FROM newstudent

(2) 执行该语句，查询结果如图12.25所示。

图12.25 使用INTERSECT语句

【示例18】在sampleDB数据库中，查询newstudent表中保存有，而在student表中没有保存的学生的姓名、学号和班级。

完整的设计步骤如下。

(1) 在查询编辑器中输入以下代码：

SELECT Name,Numb,Class FROM student EXCEPT SELECT Name,Numb,Class FROM newstudent ORDER BY Numb

(2) 该例ORDER BY子句使用了上例中的字段名称，执行该语句，查询结果如图12.26所示。

图12.26 使用EXCEPT语句

12.3 嵌套查询与联接查询

本节介绍什么是嵌套查询和联接查询。

12.3.1 嵌套查询

在一个SELECT 语句的WHERE 子句或HAVING 子句中嵌套另一个SELECT 语句的查询称为嵌套查询，又称子查询。嵌套查询还可以嵌套在INSERT、UPDATE 或 DELETE语句或其他嵌套查询中。任何允许使用表达式的地方都可以使用嵌套查询。嵌套查询是SQL 语句的扩展，一般语法如下：

SELECT < COLUMN_NAME >[，...]
FROM < TABLE_NAME >
WHERE [CONDITION] (SELECT < COLUMN_NAME >[,...]
FROM < TABLE_NAME)>
[GROUP BY < CONDITIONS >]
[HAVING SEARCH_CONDITIONS
(SELECT <目标表达式2>[,...]
FROM < TABLE_NAME > )]

在嵌套查询中，内部查询形成的结果又成为外部查询的条件，在不影响理解的情况下，也可以称为子查询形成的结果成为父查询的条件。嵌套查询还可以嵌套多层，内部查询操作的数据表可以是外部查询不操作的数据表。

【示例19】在MyCompany数据库中，查询Products表，统计所有大于telephone商品的数量的商品的名称、编号和数量
完整的设计步骤如下。

在查询编辑器中输入以下代码：

SELECT ProductName,ProductID,Amount FROM Products WHERE Amount > ( SELECT Amount FROM products WHERE ProductName = 'telephone' )

在上面的语句中，嵌套查询语句“SELECT Amount FROM products WHERE ProductName = ‘telephone’”的目的是查询“telephone”的数量。因此，上面的语句实际上可以分为下面两个步骤。

(1) 执行“SELECT Amount FROM products WHERE ProductName = ‘telephone’”语句，计算得到1500。

(2) 执行“SELECT ProductName，ProductID FROM products WHERE Amount >1500”语句。

最终的执行结果如图12.27所示。

图12.27　嵌套查询

【示例20】在MyCompany数据库中，查询Products表，查找与lighter商品数量相等的商品名称、编号和数量。

完整的设计步骤如下。

(1) 在查询编辑器中输入以下代码：

SELECT ProductName,ProductID,Amount FROM Products WHERE Amount IN ( SELECT Amount FROM products WHERE ProductName = 'lighter' )

(2) 该例使用了关键字IN，执行该语句，查询结果如图12.28所示。

图12.28 嵌套查询

另外，也可以在查询字段中嵌套查询语句，如下例所示。

【示例21】在MyCompany数据库中，查询Products表中商品的名称，每种产品的总价值，以及其占所有商品总价值的百分比。

完整的设计步骤如下。

(1) 在查询编辑器中输入以下代码：

SELECT ProductName,total, 100*total/(SELECT SUM(total)FROM products) '百分比(%)' FROM products

(2) 执行该语句，查询结果如图12.29所示。

图12.29 嵌套查询

(3) 在本例中，首先，子查询“SELECT SUM(total)FROM product”语句返回结果为4032730，然后将查询的结果插入到外部查询中，也就是：

SELECT ProductName,total,
100*total/403730 '百分比(%)'
FROM Products

(4) 执行后的结果如图12.29所示。

在8.6节中，曾经列举了WHERE子句中经常使用的关键字EXISTS，主要与子查询一起使用，用于检查子查询是否包含结果集，举例如下。

【示例22】查询所有选修了课程编号为DB001的学生的姓名、学号和班级。

完整的设计步骤如下。

(1) 这里先创建两个表并插入数据。首先需要创建课程表。

USE sampleDB GO CREATE TABLE Course (CNumb VARCHAR(8) NOT NULL UNIQUE, CName VARCHAR(20), CHour INT, CCredit FLOAT) GO

(2) 其中，Cnumb、Cname、CHour和CCredit字段分别表示课程编号、课程名称、课程的学时和学分。然后再创建学生选课表。

USE sampleDB GO CREATE TABLE SC ( Numb VARCHAR(10) NOT NULL, CNumb VARCHAR(10) NOT NULL, Grade INT) GO

(3) 其中，Snumb、CNumb和Grade分别表示选课学生的学号、学生选课的课程编号和学生的考试成绩。然后分别给Course和SC表插入数据。插入的数据如图12.30和图12.31所示。

图12.30 课程表的记录

图12.31 学生选课表的记录

(4) 在查询编辑器中输入以下代码：

SELECT Name, Numb, Class FROM student WHERE EXISTS ( SELECT * FROM SC WHERE SC.Numb = Student.Numb AND CNumb = 'DB001' )

(5) 执行上面的语句，查询结果如图12.32所示。

图12.32 EXISTS嵌套查询

其中EXISTS表示一个子查询至少返回一行时条件成立。只要子查询包含任意行，则返回 TRUE，如果不能查询任意一行，则返回FALSE。

注意

(1) 嵌套查询的SELECT查询总是使用圆括号括起来且不能包含COMPUTE子句。

(2) 内部查询必须在括号里面使用。

(3) 以比较操作符引导的子查询的选择列表只能包括一个表达式或列名，否则 SQL Server 会报错。

(4) 内部查询中不能有ORDER BY分组语句。

(5) 除SELECT外，支持子查询的关键字还有：INSERT、UPDATE和DELETE等。

(6) 子查询可以使用任何普通查询中使用的关键字：如DINSTINCT、GROUP BY、LIMIT、ORDER BY、UNION、ALL、UNION ALL等。可以使用<、>、<=、>=、=、<>运算符进行比较，也可以使用ANY、IN和SOME进行集合的匹配。