Not in \ in中包含有null值的列,不会排除null(因为 null != null)
Oracle not in (如果子查询返回的值或是集合有null值的话,将查询不到记录
解决办法: 子查询中限制不能游null返回值 is not null
用 not exists 代替 not in
True and null 返回 null
true or null 返回true ,
false and null返回null
false or null 返回null
null要用is进行比较如果用其它的比较运算符都返回null,
如:(1<>null返回null 1=null返回null)
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?
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:
Since 3 = 3 is true, you get a result.
Query B is the same as:
[b]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.[/b]
Oracle not in (如果子查询返回的值或是集合有null值的话,将查询不到记录
解决办法: 子查询中限制不能游null返回值 is not null
用 not exists 代替 not in
True and null 返回 null
true or null 返回true ,
false and null返回null
false or null 返回null
null要用is进行比较如果用其它的比较运算符都返回null,
如:(1<>null返回null 1=null返回null)
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
[b]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.[/b]