Where子句排除了多于它应该排除的

Question

我有两个表，一个连接和一个where子句。两个表的示例内容:

Id         FieldA                   Id       FieldB
 1           100                     1        Yellow
 2           100                     2        Green
 3           200                     3        Green
 4           200                     4        Blue
 5           300                     5        Yellow
 6           300                     6        Orange

我试图返回一切，除了fieldA = 200和fieldB =绿色。因此，它仍然应该返回第2行，其中fieldA = 100, FieldB = Green。然而，这是我的查询，它不工作。它将排除所有包含200和绿色的行:

select t1.FieldA, t2.FieldB
FROM test1 t1 
JOIN test2 t2 ON t1.Id = t2.Id
WHERE (t1.FieldA <> 200 AND t2.FieldB <> 'Green')

我看到它的方式，运行这个查询后，唯一排除的行应该是第3行，因为它有fielda = 200和fieldb =绿色，但它只返回行1，行5 &row6。在我看来，只有当我使用OR时，它才应该这样做。

让我知道我错在哪里，这里是一些DDL，所以你可以玩它:

create table dbo.test1
(
    Id int not null,
    FieldA int
)
create table dbo.test2
(
    Id int not null,
    FieldB varchar(10)
)
INSERT INTO test1 (Id, FieldA)
VALUES 
    (1,100),
    (2,100),
    (3,200),
    (4,200),
    (5,300),
    (6,300)
INSERT INTO test2 (Id, FieldB)
VALUES 
    (1,'Yellow'),
    (2,'Green'),
    (3,'Green'),
    (4,'Blue'),
    (5,'Yellow'),
    (6,'Orange')

Answer 1

每个条件都是针对整个行集独立计算的。要组合它们，翻转操作符并将组合取反，如下所示:

select t1.FieldA, t2.FieldB
FROM test1 t1 
JOIN test2 t2 ON t1.Id = t2.Id
WHERE not (t1.FieldA = 200 AND t2.FieldB = 'Green')

您的原始查询基本上是说，首先消除所有FieldA不是200的行，然后，从剩下的行中，消除所有FieldB不是'Green'的行。

当您希望两个条件都适用于给定行时，您首先选择要排除的条件，这就是为什么您从<>切换到=，然后通过应用NOT操作符使WHERE子句排除整个内容。

EDIT re: comment

我认为关于您原始查询返回的结果的混淆以及括号中的条件"被评估为一个"的想法可能源于逻辑否定不是分布的事实，即A && B的否定不是~A && ~B，而是~(A && B)。

描述你想要的结果的第一句话非常接近查询的正确t-sql。你说"我试图返回所有东西，除了fieldA = 200和fieldB =绿色。"句子的最后一部分是where从句，即

except where fieldA = 200 AND fieldB = Green

用"not"代替"except"

not where fieldA = 200 AND fieldB = Green
-- or, to make the grouping explicit
not (where fieldA = 200 AND fieldB = Green)

并将其清理为有效的t-sql语法

where not (fieldA = 200 AND fieldB = Green)

相比之下，与WHERE (t1.FieldA <> 200 AND t2.FieldB <> 'Green')等价的英文表达式可能是:返回field1不等于200且field1不等于绿色的所有内容。在这种情况下，匹配200或green就足以排除该行。

要了解为什么错误地排除了第2行和第4行，请考虑原始where子句的真值表:

                       Field1 <> 200
                         T       F
                     -----------------
                   T |   T   |   F   |
                     |       | row 4 |
Field2 <> 'Green'    -----------------
                   F |   F   |   F   |
                     | row 2 |       |
                     -----------------

换句话说，第2行因为Field2 = 'Green'而被排除，使得条件Field2 <> 'Green'的求值为FALSE，所以Field1是什么并不重要，因为FALSE和任何其他值总是FALSE。

Answer 2

在Id上执行连接时，它创建了一个临时表，如下所示

Id  FieldA  FieldB
1   100  Yellow
2   100  Green
3   200  Green
4   200  Blue
5   300  Yellow
6   300  Orange

当您输入FieldA <> 200时，将排除第3行和第4行，其余行将是1,2,5,6。现在，当您说FieldB <> Green时，将排除第2行，从而产生第1,5,6行。

注意: where子句中的条件不按照您指定的顺序应用。相反，它在运行时应用于sql执行计划，并且指定where条件的顺序不会对结果产生任何影响。

要获得结果，使用下面的条件

select t1.FieldA, t2.FieldB
FROM test1 t1 
JOIN test2 t2 ON t1.Id = t2.Id
WHERE NOT (t1.FieldA = 200 AND t2.FieldB = 'Green')

Answer 3

select t1.FieldA, t2.FieldB
FROM test1 t1 
JOIN test2 t2 ON t1.Id = t2.Id
WHERE NOT (t1.FieldA = 200 AND t2.FieldB = 'Green')

Answer 4

你能试试这个吗?

select t1.FieldA, t2.FieldB
  FROM test1 t1 
  JOIN test2 t2 
    ON t1.Id = t2.Id
   AND t1.FieldA <> 200 
   AND t2.FieldB <> 'Green'