SQL查漏补缺

有这么一道题，先看题目，表的内容如下

显示GDP比非洲任何国家都要高的国家名称(一些国家的GDP值可能为NULL)。

错误的查询：

SELECT name FROM bbcWHERE gdp > ALL (SELECT gdp FROM bbc WHERE region = 'Africa')

正确的查询：

SELECT name FROM bbcWHERE gdp > (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa')

or

SELECT name FROM bbcWHERE gdp > ALL (SELECT gdp FROM bbc WHERE region = 'Africa' AND gdp IS NOT NULL)

发现第一种查询的问题在哪儿了吗？注意题目中的一句话：GDP可能为NULL。这里有个知识点就是ALL和MAX的区别。

ALL

ALL是SQL中的一种限定谓词，它可以和比较谓词一起使用，用来表达“与所有的xx都相等”，或“比所有的xx都大”的意思。请看以下两张表：
Class_B.table:

ClassA.table

接下来，我们查询“比B班住在东京的所有学生年龄都小的A班学生”的SQL语句：

SELECT * FROM Class_A WHERE age < ALL(SELECT age FROM CLassB WHERE city = '东京')；

执行结果是：

name	age	city
拉里	19	埼玉

查询到的只有比山田年龄小的拉里，到这里都没有问题。但是如果山田年龄为NULL，就会有问题了。凭直觉来说，此时查询到的可能是比22岁的齐藤年龄小的拉里和伯杰。然而，这条SQL执行的结果是空。这是因为ALL谓词其实是多个以AND连接的逻辑表达式的省略写法，具体分析步骤如下：

1.执行子查询获取年龄列表
SELECT * FROM Class_A WHERE age < ALL(22,23,null);

2.将ALL谓词改写成AND
SELECT * FROM Class_A WHERE (age < 22) AND (age<23) AND(age < NULL);

3.对NULL使用“<”后，结果变为unknown

4.如果AND运算里包含unknown，则结果不为true
SELECT * FROM Class_A WHERE false 或 unknown;

回到一开始的题目，正因为存在gdp为null的数据，用ALL谓词是得不到结果的，除非使用极值函数max/min，或者在ALL语句中加入限定条件gdp is not null。

ALL 与 极值函数

用极值函数重写刚才的SQL：

SELECT * From Class_A Where age < (Select MIN(age) from Class_B Where city='东京')；

执行结果：

name	age	city
拉里	19	埼玉
伯杰	21	千叶

即使山田的年龄为null，这段代码也能查询到拉里和伯杰，这是因为极值函数在统计时把为NULL的数据排除掉了。这样的话任何时候使用极值函数岂不是都是更安全的？也许有人会这么想。

然而，ALL谓词和极值函数表达的含义本就不同：

• ALL: 他的年龄比在东京住的所有学生都要小
• 极值函数：他的年龄比在东京住的年龄最小的学生都要小

在现实世界中，两者结果一致，意思相同。但是当表里存在NULL时它们时不等价的，还有一种情况下它们也是不等价的。

当谓词的深入为空集的情况。
Class_B表中没有住在东京的学生！

这时使用ALL会查询到A班的所有学生。然而使用极值函数则查询不到任何数据。这是因为极值函数在输入为空表时会返回NULL。因此使用极值函数的SQL语句会像下面这样执行：

1.极值函数返回NULL
SELECT * From Class_A Where age < NULL;

2.对NULL使用“<”后结果转为unknown
select * from Class_A where unkonwn;

实际上，当输入为空表时，除COUNT以外的聚合函数都会查询不到任何数据。