NOT IN子句和NULL值

查询A与以下内容相同:

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

自从 3 = 3 如果是真的,你会得到一个结果。

查询B与以下内容相同:

select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

什么时候 ansi_nulls 正在播放, 3 <> null

什么时候 ansi_空值 是关的,, 3<&燃气轮机;无效的 如果为true,则谓词的计算结果为true,您将得到一行。

无论何时使用NULL,实际上都是在处理三值逻辑。

    3 = 1 or 3 = 2 or 3 = 3 or 3 = null
which is:
    FALSE or FALSE or TRUE or UNKNOWN
which evaluates to 
    TRUE

第二条:

    3 <> 1 and 3 <> 2 and 3 <> null
which evaluates to:
    TRUE and TRUE and UNKNOWN
which evaluates to:
    UNKNOWN

未知与虚假是不同的 您可以通过调用以下命令轻松测试:

select 'true' where 3 <> null
select 'true' where not (3 <> null)

两个查询都不会给出任何结果

如果未知值与FALSE相同,那么假设第一个查询将为FALSE,第二个查询将必须计算为TRUE,因为它与NOT相同(FALSE)。

article on this subject on SqlServerCentral .

我想推荐的另一篇文章是 SQL Aggregate Functions and NULL .

NOT IN

自从 NULL 是一个未知的,一个 不在 包含 或 可能值列表中的将始终返回 0 记录,因为没有办法确保 无效的

除非使用is null,否则Compare to null未定义。

因此,当将3与NULL(查询A)进行比较时,它返回undefined。

和

将产生相同的结果,因为NOT(UNDEFINED)仍然是UNDEFINED,但不是TRUE

在撰写本文时,这个问题的标题是

SQL不在约束和空值中

从问题的文本来看,问题似乎发生在SQL DML中 SELECT 查询,而不是SQL DDL CONSTRAINT .

然而,特别是考虑到标题的措辞,我想指出,这里所作的一些陈述是潜在的误导性陈述,这些陈述大致如下(意译)

当谓词的计算结果为UNKNOWN时,不会得到任何行。

尽管SQL DML就是这种情况,但在考虑约束时,效果是不同的。

考虑一个非常简单的表,它有两个约束直接从问题的谓词中得到(并用@ Brnon的一个很好的答案来处理):

DECLARE @T TABLE 
(
 true CHAR(4) DEFAULT 'true' NOT NULL, 
 CHECK ( 3 IN (1, 2, 3, NULL )), 
 CHECK ( 3 NOT IN (1, 2, NULL ))
);

INSERT INTO @T VALUES ('true');

SELECT COUNT(*) AS tally FROM @T;

根据@Brannon的回答,第一个约束(使用 IN NOT IN )计算结果为未知。 然而 ,插入成功!因此,在这种情况下,说“您没有得到任何行”是不完全正确的,因为我们确实因此插入了一行。

就SQL-92标准而言,上述效果确实是正确的。比较和对比SQL-92规范中的以下部分

7.6 where条款

的结果是一个表,其中列出了

4.10完整性约束

当且仅当指定的

换言之:

WHERE 计算结果为未知,因为它 不 满足条件“为真”。

在SQL DDL(即约束)中,当行的计算结果为未知时,不会从结果中删除行,因为它是未知的 做 满足条件“不为假”。

尽管SQL DML和SQL DDL中的效果可能看起来相互矛盾,但通过允许未知结果满足约束(更准确地说,允许它们不不满足约束),为未知结果提供“怀疑的好处”是有实际理由的:如果没有这种行为,每一个约束都必须显式地处理空值,从语言设计的角度来看,这是非常不令人满意的(更不用说,这对编码人员来说是一种痛苦!)

P.S.S.如果你发现遵循这样的逻辑是“未知的不满足约束”,就像我要写的那样,那么考虑你可以简单地通过避免SQL DDL中的可空列和产生空值的SQL DML(例如外部连接)来省略所有这些。

在A中,对集合中的每个成员测试3是否相等,从而得出(FALSE、FALSE、TRUE、UNKNOWN)。因为其中一个元素为真,所以条件为真。(这里也可能发生一些短路,因此一旦达到第一个真值,它实际上就会停止,并且永远不会计算3=NULL。)

从这里的答案可以得出如下结论: NOT IN (subquery) NOT EXISTS NOT IN 它正确地处理空值并返回正确的结果,而不是 不存在

考虑一张桌子 sp 代表供应商( sno )谁知道供应零件( pno )数量上( qty

      VALUES ('S1', 'P1', NULL), 
             ('S2', 'P1', 200),
             ('S3', 'P1', 1000)

任务是找到已知供应零件号“P1”但数量不为1000的供应商。

以下用途 不在 要正确识别供应商“S2”,请执行以下操作:

WITH sp AS 
     ( SELECT * 
         FROM ( VALUES ( 'S1', 'P1', NULL ), 
                       ( 'S2', 'P1', 200 ),
                       ( 'S3', 'P1', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT DISTINCT spx.sno
  FROM sp spx
 WHERE spx.pno = 'P1'
       AND 1000 NOT IN (
                        SELECT spy.qty
                          FROM sp spy
                         WHERE spy.sno = spx.sno
                               AND spy.pno = 'P1'
                       );

但是,下面的查询使用相同的一般结构,但是 不存在 但结果中错误地包含供应商“S1”(即数量为空):

WITH sp AS 
     ( SELECT * 
         FROM ( VALUES ( 'S1', 'P1', NULL ), 
                       ( 'S2', 'P1', 200 ),
                       ( 'S3', 'P1', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT DISTINCT spx.sno
  FROM sp spx
 WHERE spx.pno = 'P1'
       AND NOT EXISTS (
                       SELECT *
                         FROM sp spy
                        WHERE spy.sno = spx.sno
                              AND spy.pno = 'P1'
                              AND spy.qty = 1000
                      );

不存在 不是它可能出现的银弹!

当然,问题的根源是空值的存在,因此“真正”的解决方案是消除这些空值。

这可以通过使用两个表来实现(除其他可能的设计外):

• 服务提供商 已知供应零件的供应商
• spq

注意,可能存在外键约束,其中 spq 参考资料 服务提供商 .

然后可以使用“减号”关系运算符(即 EXCEPT 标准SQL中的关键字)例如。

WITH sp AS 
     ( SELECT * 
         FROM ( VALUES ( 'S1', 'P1' ), 
                       ( 'S2', 'P1' ),
                       ( 'S3', 'P1' ) )
              AS T ( sno, pno )
     ),
     spq AS 
     ( SELECT * 
         FROM ( VALUES ( 'S2', 'P1', 200 ),
                       ( 'S3', 'P1', 1000 ) )
              AS T ( sno, pno, qty )
     )
SELECT sno
  FROM spq
 WHERE pno = 'P1'
EXCEPT 
SELECT sno
  FROM spq
 WHERE pno = 'P1'
       AND qty = 1000;

Null表示没有数据,也就是说它是未知的,不是一个空的数据值。编程背景的人很容易混淆这一点,因为在C类型语言中,使用指针时,null实际上什么都不是。

选择“真”,其中3不在(空)

如果要使用NOT IN筛选包含null的子查询,只需检查NOT null

SELECT blah FROM t WHERE blah NOT IN
        (SELECT someotherBlah FROM t2 WHERE someotherBlah IS NOT NULL )

SQL对真值使用三值逻辑。这个 IN 查询生成预期的结果:

SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE col IN (NULL, 1)
-- returns first row

NOT 不会反转结果:

SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE NOT col IN (NULL, 1)
-- returns zero rows

这是因为上述查询相当于以下查询:

SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE NOT (col = NULL OR col = 1)

下面是where子句的计算方法:

| col | col = NULL⁽¹⁾  | col = 1 | col = NULL OR col = 1 | NOT (col = NULL OR col = 1) |
|-----|----------------|---------|-----------------------|-----------------------------|
| 1   | UNKNOWN        | TRUE    | TRUE                  | FALSE                       |
| 2   | UNKNOWN        | FALSE   | UNKNOWN⁽²⁾            | UNKNOWN⁽³⁾                  |

请注意:

1. 比较涉及 NULL UNKNOWN
2. 这个 OR 不包含任何操作数的表达式 TRUE 并且至少有一个操作数是 产量 未知的 ( ref )
3. 不 属于 未知的 未知的 ( 裁判 )

无效的 那么就没有一行相匹配了。

这是给男孩的:

select party_code 
from abc as a
where party_code not in (select party_code 
                         from xyz 
                         where party_code = a.party_code);

无论ansi设置如何,此选项都有效

此外,这可能有助于了解联接、exists和in之间的逻辑差异 http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx