首页»数据库»深入详解SQL中的Null

深入详解SQL中的Null

来源:renfufei 发布时间:2015-04-17 阅读次数:

  NULL 在计算机和编程世界中表示的是未知,不确定。虽然中文翻译为 “空”, 但此空(null)非彼空(empty)。 Null表示的是一种未知状态,未来状态,比如小明兜里有多少钱我不清楚,但也不能肯定为0,这时在计算机中就使用Null来表示未知和不确定。

  虽然熟练掌握SQL的人对于Null不会有什么疑问,但总结得很全的文章还是很难找,看到一篇英文版的, 感觉还不错。

  Tony Hoare 在1965年发明了 null 引用, 并认为这是他犯下的“几十亿美元的错误”. 即便是50年后的今天, SQL中的 null 值还是导致许多常见错误的罪魁祸首.

  我们一起来看那些最令人震惊的情况。

  Null不支持大小/相等判断

下面的2个查询,不管表 users 中有多少条记录,返回的记录都是0行:

select * from users where deleted_at = null;

– result: 0 rows

select * from users where deleted_at != null;

– result: 0 rows

  怎么会这样子? 一切只因为 null 是表示一种“未知”的类型。也就是说,用常规的比较操作符(normal conditional operators)来将 null 与其他值比较是没有意义的。 Null 也不等于 Null(近似理解: 未知的值不能等于未知的值,两者间的关系也是未知,否则数学和逻辑上就乱套了)。

  – 注意: 下面的SQL适合于MySQL,如果是Oracle,你需要加上 … from dual;

select null > 0;

– result: null

select null < 0;

– result: null

select null = 0;

– result: null

select null = null;

– result: null

select null != null;

– result: null

  将某个值与 null 进行比较的正确方法是使用 is 关键字, 以及 is not 操作符:

select * from users

where deleted_at is null;

– result: 所有被标记为删除的 users
select * from users

where deleted_at is not null;

– result: 所有被标记为删除的 users

  如果想要判断两列的值是否不相同,则可以使用 is distinct from:

select * from users

where has_address is distinct from has_photo

– result: 地址(address)或照片(photo)两者只有其一的用户

  not in 与 Null

  子查询(subselect)是一种很方便的过滤数据的方法。例如,如果想要查询没有任何包的用户,可以编写下面这样一个查询:

select * from users 

where id not in (select user_id from packages)

  但此时假若 packages 表中某一行的 user_id 是 null 的话,问题就来了: 返回结果是空的! 要理解为什么会发生这种古怪的事情, 我们需要理解SQL编译器究竟干了些什么. 下面是一个更简单的示例:

select * from users 

where id not in (1, 2, null)

  这个SQL语句会被转换为:

select * from users 

where id != 1 and id != 2 and id != null

  我们知道,id != null 结果是个未知值, null. 而任意值和 null 进行 and 运算的结果都是 null, 所以相当于没有其他条件. 那么出这种结果的原因就是 null 的逻辑值不为 true.

  如果条件调换过来, 查询结果就没有问题。 现在我们查询有package的用户.

select * from users 

where id in (select user_id from packages)

  同样我们可以使用简单的例子:

select * from users

where id in (1, 2, null)

  这条SQL被转换为:

select * from users 

where id = 1 or id = 2 or id = null

  因为 where 子句中是一串的 or 条件,所以其中某个的结果为 null 也是无关紧要的。非真(non-true)值并不影响子句中其他部分的计算结果,相当于被忽略了。

  Null与排序

  在排序时, null 值被认为是最大的. 在降序排序时(descending)这会让你非常头大,因为 null值排在了最前面。

  下面这个查询是为了根据得分显示用户排名, 但它将没有得分的用户排到了最前面!

select name, points

from users

order by 2 desc;

– points 为 null 的记录排在所有记录之前!

  解决这类问题有两种思路。最简单的一种是用 coalesce 消除 null的影响:

– 在输出时将 null 转换为 0 :

select name, coalesce(points, 0)

from users

order by 2 desc;

– 输出时保留 null, 但排序时转换为 0 :

select name, points

from users

order by coalesce(points, 0) desc;

  还有一种方式需要数据库的支持,指定排序时将 null 值放在最前面还是最后面:

select name, coalesce(points, 0)

from users

order by 2 desc nulls last;

  当然, null 也可以用来防止错误的发生,比如处理除数为0的数学运算错误。

  被 0 除

  除数为0是一个非常 egg-painfull 的错误。昨天还运行得好好的SQL,突然被0除一下子就出错了。一个常用的解决方法是先用 case 语句判断分母(denominator)是否为0,再进行除法运算。

select case when num_users = 0 then 0 

else total_sales/num_users end;

  ase 语句的方式其实很难看,而且分母被重复使用了。如果是简单的情况还好,如果分母是个很复杂的表达式,那么悲剧就来了: 很难读,很难维护和修改,一不小心就是一堆BUG.

  这时候我们可以看看 null 的好处. 使用 nullif 使得分母为0时变成 null. 这样就不再报错, num_users = 0 时返回结果变为 null.

select total_sales/nullif(num_users, 0);

nullif 是将其他值转为 null, 而Oracle的 nvl 是将 null 转换为其他值。

  如果不想要 null,而是希望转换为 0 或者其他数, 则可以在前一个SQL的基础上使用 coalesce函数:

select coalesce(total_sales/nullif(num_users, 0), 0);

null 再转换回0

  Conclusion

  Tony Hoare 也许会后悔自己的错误, 但至少 null 存在的问题很容易地就解决了. 那么快去练练新的大招吧,从此远离 null 挖出来的无效大坑(nullifying)!

  原文链接: Understanding SQL’s Null 翻译: http://blog.csdn.net/renfufei

QQ群:WEB开发者官方群(515171538),验证消息:10000
微信群:加小编微信 849023636 邀请您加入,验证消息:10000
提示:更多精彩内容关注微信公众号:全栈开发者中心(fsder-com)
SQL | Null
网友评论(共0条评论) 正在载入评论......
理智评论文明上网,拒绝恶意谩骂 发表评论 / 共0条评论
登录会员中心
大发国际网址 阳谷县| 荥经县| 郁南县| 隆林| 大竹县| 渭南市| 永顺县| 崇明县| 衡水市| 晴隆县| 宣武区| 霍山县| 崇文区| 江城| 万宁市| 开远市| 新密市| 抚州市| 遂川县| 广河县| 泾源县| 桃园市| 瑞安市| 万安县| 搜索| 密山市| 尤溪县| 锡林郭勒盟| 尤溪县| 长宁区| 莎车县| 松溪县| 东阿县| 浑源县| 炉霍县| 屯昌县| 湖州市| 左权县| 德江县| 东阿县| 德令哈市| 得荣县| 若羌县| 微山县| 绥德县| 南江县| 什邡市| 泸定县| 湛江市| 方城县| 乐东| 黄浦区| 江油市| 河北区| 兴文县| 南涧| 灵台县| 庆阳市| 元阳县| 南丹县| 高要市| 孟州市| 杭州市| 陈巴尔虎旗| 威信县| 镇赉县| 潢川县| 深泽县| 伊宁县| 凌源市| 藁城市| 万山特区| 资阳市| 潞西市| 射洪县| 从化市| 宁南县| 嘉义县| 霸州市| 昌江| 新丰县| 巴里| 凤城市| 资溪县| 赣州市| 紫金县| 密山市| 潮州市| 无极县| 鱼台县| 陇西县| 辽中县| 平顶山市| 泗洪县| 牙克石市| 中西区| 驻马店市| 克什克腾旗| 山东省| 玉门市| 奈曼旗| 平远县| 尉氏县| 新乡市| 乐业县| 孟连| 若羌县| 莱阳市| 正阳县| 繁昌县| 辽中县| 香河县| 宜春市| 文安县| 永城市| 收藏| 准格尔旗| 兖州市| 滦平县| 陕西省| 饶平县| 锡林浩特市| 田阳县| 铁岭县| 漠河县| 甘南县| 滕州市| 长子县| 南安市| 彭水| 荔浦县| 绥化市| 泽库县| 沅陵县| 西安市| 朝阳区| 江北区|