sql - 否定任意 where 子句条件(包括空测试)

Question

我想有效地检查一个表是否包含任何匹配 并且不匹配 的行，其中条件是任意的。

在 Oracle 中，这几乎有效:

select count(*) from dual
where exists (
  select * from people
  where (<condition A>)
  and not (<condition B>)
);
-- returns zero if all rows that match <condition A> also match <condition B>
-- (well, almost)

问题是可怕的空值。假设 <条件 A> 是 姓名 = '亚伦' 并且 <条件 B> 是 年龄 = 21 .该查询将正确识别任何年龄不等于 21 的 Aarons，但无法识别任何年龄为 null 的 Aarons。

这是一个正确的解决方案，但在具有数百万条记录的表上可能需要一段时间:

select (
  select count(*) from people
  where (<condition A>)
) - (
  select count(*) from people
  where (<condition A>)
  and (<condition B>)
) from dual;
-- returns zero if all rows that match <condition A> also match <condition B>
-- (correct, but it is s l o w...)

不幸的是，这两个条件将是任意的、复杂的、变化的，并且通常超出我的控制。它们是从用户搜索的应用程序持久性框架中生成的，虽然我们试图让我们的索引与我们的用户保持同步，但很多时候它们会导致大表扫描(这就是为什么第一个带有“exists”子句的查询是比第二个快得多——它可以在找到一个匹配的记录后立即停止，并且不必进行两次单独的扫描)。

我怎样才能有效地做到这一点而不会搞砸空值？

Answer 1

假设你的表有一个主键 id ，一种可能的方法是:

select count(*)
from people p1
left join people p2
  on (p1.id = p2.id
  and (p2.<condition A>)
  and (p2.<contition B>))
where p1.<condition A>
  and p2.id IS NULL

您确实需要对条件进行一些简单的预处理(根据情况在每个列名前面加上 p1. 或 p2.)，但这比使用 NULL 正确否定条件要容易得多。你提到的问题。
LEFT JOIN sometable ON whatever WHERE ... AND sometable.id IS NULL是表达“并且在 sometable 中没有满足 whatever 约束的相应记录的流行方式，所以我希望一个好的引擎能够很好地优化该习语，尽可能多的可用索引允许。