sql - MINUS 与 NOT 在 where 子句中

Question

我有一个从单个表中检索数据的查询。但是，查询使用 MINUS 子句，如下所示:

SELECT field1, field2
FROM tab
WHERE field3 = 'a'
AND field4 = 'b'
    MINUS
SELECT field1, field2
FROM tab
WHERE field5 = 'c'
AND field6 = 'd'

因为这是从同一个表中选择的，所以我试图重写它以去掉 MINUS 子句。我认为这样的事情应该可行:

SELECT DISTINCT field1, field2
FROM tab
WHERE field3 = 'a'
AND field4 = 'b'
AND NOT (field5 = 'c'
AND field6 = 'd')

我的理由是，如果 MINUS 子句排除了第二个查询中的记录，那么将 WHERE 子句包装在 AND NOT 中应该得到去掉相同的记录。 MINUS 子句也去除了重复项，这就是为什么我将 DISTINCT 添加到选择中的原因。然而，问题是我的查询返回的记录比原始记录更多。

我在这里错过了什么？

Answer 1

考虑这两行:

1, 2, a, b, x, y

1, 2, u, v, c, d

MINUS 运算不会返回 (1, 2) 对，但您的查询会。 c、d 值可能与相同的 1、2 出现，但与 a、b 在不同的行中

根本区别在于 MINUS 在集合级别运行，而 NOT 条件一次仅适用于一行(同一行在其他列中具有“必需”值)。

现在:您可以使您的查询更高效一些(尽管您无法避免读取基表两次)。使用 NOT IN 条件:

select field1, field2 from tab where field3 = 'a' and field4 = 'b'
and    (field1, field2) not in 
           (select field1, field2 from tab where field5 = 'c' and field6 = 'd');

注意(请参阅下面 spencer7593 的评论):在所有可能存在 NULL 的情况下，NOT IN 都不是一个好的解决方案。相反，应该使用 NOT EXISTS 条件。我不会详细说明，因为它似乎超出了所问问题的范围(这就是为什么“NOT”解决方案与“MINUS”解决方案不同)。