SOME，ANY，All，EXISTS，IN

----start

  这几个关键字有一个共同点，那就是它们一般应用于子查询中。大家对 IN 都比较熟悉，这里我们就不介绍了，下面我们看一看其他几个关键字的使用，首先，我们定义如下表：

--学生

CREATE TABLE STUDENT

(

ID VARCHAR(8),---学号

NAME VARCHAR(20),---姓名

CLASS VARCHAR(20),---班级

CHINESE FLOAT,---语文成绩

MATH FLOAT---数学成绩

);

INSERT INTO STUDENT (ID, NAME, CLASS, CHINESE, MATH) VALUES

('20090001', '张三', '五年级A班', 80 ,90),

('20090002', '李四', '五年级A班', 60 ,75),

('20090003', '王五', '五年级A班', 90 ,95),

('20090004', '赵红', '五年级B班', 70 ,90),

('20090004', '李白', '五年级B班', 85 ,80),

('20090005', '王蓝', '五年级B班', NULL ,70);  

假设现在让你查询一下，A班哪些学生的数学成绩高于B班数学成绩的最小值，怎么办？我们可以采用如下SQL：

 SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND MATH >

(

SELECT MIN(MATH) FROM STUDENT WHERE CLASS='五年级B班'

); 

除此之外，我们还可以使用SOME或ANY。注意：ANY和 SOME 的作用完全和使用方式完全相同，不知道制定SQL标准的人为什么要定义两个关键字。我们来看一下以上问题通过SOME或ANY怎么实现。如下SQL所示：

 SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND MATH > ANY

(

SELECT MATH FROM STUDENT WHERE CLASS='五年级B班'

); 

假设现在让你查询一下，A班哪些学生的数学成绩高于B班数学成绩的最大值，怎么办呢？我们可以采用如下SQL：

 SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND MATH >

(

SELECT MAX(MATH) FROM STUDENT WHERE CLASS='五年级B班'

); 

除此之外，我们还可以使用ALL,如下SQL所示：

 SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND MATH > ALL

(

SELECT MATH FROM STUDENT WHERE CLASS='五年级B班'

); 

至此，我们可以总结出SOME,ANY,ALL 和 MIN, MAX 的对应关系：

 > ANY(sub-qurey) --- > MIN(sub-qurey)

< ANY(sub-query) --- < MAX(sub-qurey)

> ALL(sub-query) --- > MAX(sub-qurey)

< ALL(sub-query) --- < MIN(sub-qurey) 

至此，你应该理解了SOME,ANY,ALL关键字的作用了吧。下面我们看一看EXISTS关键字的作用。EXISTS的作用比较简单，它只关注它后面的子查询返没返回值，而不在乎返回多少。如果返回，则整个表达式就为真，否则为假。NOT EXISTS关键字则和EXISTS作用相反。假设现在让你查询一下有没有数学成绩为100的学生，如果有，则将所有学生的数学成绩输出，如果没有，则什么都不输出，我们使用EXISTS实现，如下：

SELECT NAME,MATH FROM STUDENT WHERE EXISTS

(

SELECT * FROM STUDENT WHERE MATH=100

); 

至此，以上几个关键字的作用全部介绍给大家了，不知大家理解了没有。以上操作都针对数学成绩，如果你认为对语文成绩也执行类似的操作会得到类似的答案的话，那么你就错了。一切的原因都是因为NULL引起的。下面，我们来讨论NULL对各个关键字的影响。

---语句1

SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND CHINESE >

(

SELECT MAX(CHINESE) FROM STUDENT WHERE CLASS='五年级B班'

);

---语句2

SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND CHINESE > ALL

(

SELECT CHINESE FROM STUDENT WHERE CLASS='五年级B班'

);

通常，我们认为语句1和语句2会返回同样的结果，然而上面两条语句返回的结果却令人吃惊，语句1返回王五，语句2则什么也没返回，为什么会出现这样的情况呢？MAX函数默认会忽略NULL值，所以语句1返回了王五。那么为什么语句2返回NULL呢？答案是我也不知道。哪位朋友知道的话请告诉我一下。不仅如此，下面两条语句也返回不同的结果：

---语句1

SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND CHINESE <

(

SELECT MIN(CHINESE) FROM STUDENT WHERE CLASS='五年级B班'

);

---语句2

SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND CHINESE < ALL

(

SELECT CHINESE FROM STUDENT WHERE CLASS='五年级B班'

);  

不仅如此，试一试下面的语句（子查询没有返回任何记录）：

---语句1

SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND CHINESE <

(

SELECT MIN(CHINESE) FROM STUDENT WHERE 1<>1

);

--语句2

SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND CHINESE < ALL

(

SELECT CHINESE FROM STUDENT WHERE 1<>1

);

上面的几个例子提醒大家，使用ALL的时候应该特别注意，一不留神就会返回我们不期望的结果。

还有个关键字需要大家注意，那就是NOT IN，请看下面的例子：

SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND CHINESE NOT IN

(

80,60,NULL

); 

如果你认为以上语句返回王五的话，那么，你就错了，虽然我们一般不会像上面那样主动写出NULL值，但是不能保证子查询也不会返回NULL值，所以在使用NOT NULL时也需要特别注意。

----更多参见：DB2 SQL 精萃

----声明：转载请注明出处。

----last updated on 2010.1.15

----written by ShangBo on 2009.11.23

----end