NOT IN clause and NULL values
https://stackoverflow.com/questions/129077/not-in-clause-and-null-values
This issue came up when I got different records counts for what I thought were identical queries one using a not in
where
constraint and the other a left join
. The table in the not in
constraint had one null value (bad data) which caused that query to return a count of 0 records. I sort of understand why but I could use some help fully grasping the concept.
To state it simply, why does query A return a result but B doesn't?
A: select 'true' where 3 in (1, 2, 3, null)
B: select 'true' where 3 not in (1, 2, null)
This was on SQL Server 2005. I also found that calling set ansi_nulls off
causes B to return a result.
答案:
Query A is the same as:
select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null
Since 3 = 3
is true, you get a result.
Query B is the same as:
select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null
When ansi_nulls
is on, 3 <> null
is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don't get any rows.
When ansi_nulls
is off, 3 <> null
is true, so the predicate evaluates to true, and you get a row.
最新文章
- 十五天精通WCF——第七天 Close和Abort到底该怎么用才对得起观众
- APT 常用功能
- mongo 学习笔记
- 动画Animation
- Shiro初识与总结
- 如何阻止sql注入(pdo篇)
- C语言结构体指针初始化(转)
- 【转】使用notepad运行python
- spring 基于注解的@Scheduled和quartz定时器两种实现
- MySQL_插入更新 ON DUPLICATE KEY UPDATE
- (NOI2014)(bzoj3669)魔法森林
- mysql xtrabackup工具备份
- VIM中一些按键的作用:
- Java NIo 笔记001
- 《转载》struts旅程《1》
- EF Code First 学习笔记:关系(转)
- 模板CodeTemplate
- UVA 1085 House of Cards(对抗搜索)
- openstack nova 基础知识——Quota(配额管理)
- C - The C Answer (2nd Edition) - Exercise 1-1
热门文章
- Linux(12):期中架构(4)--- 前端部分:HTTP &; Nginx &; LNMP架构
- Jackson工具类(各种转换)
- N*N数码问题
- poj 1031 多边形对点(向周围发射光线)的覆盖
- 洛谷——P1057 传球游戏
- 7.Java web&mdash;tomcat9部署
- 2.JAVA语言基础部分
- pycharm、idea插件代理设置,插件安装
- POJ 3928 Ping pong 树状数组模板题
- Type cannot use &#39;try&#39; with exceptions disabled