sql - 为什么匹配 NULL 需要 SQL 中的特殊语法，我该如何绕过它？

Question

我有一个包含以下字段的 SQLite3 表:

id INTEGER PRIMARY KEY,
street_name TEXT NOT NULL,
house_number INTEGER NOT NULL,
entrance TEXT DEFAULT NULL,
postal_code INTEGER NOT NULL,
city TEXT NOT NULL

如您所见，entrance 可以是 NULL，甚至默认设置为该值。当我尝试检查特定地址是否存在时，这是一个问题:

SELECT 1 FROM street_addresses
WHERE
    street_name=? AND
    house_number=? AND
    entrance=? AND
    postal_code=? AND
    city=?

如果我将 NULL 传递给 entrance 字段，无论如何查询都不会产生任何结果，因为 entrance=NULL 无效。相反，我必须使用 entrance IS NULL，但我无法编辑查询以说 entrance IS ? 因为当我传递除 以外的任何内容时，这将不起作用空。

除了根据 entrance 的值有条件地构建查询之外，还有什么办法解决这个问题吗？

Answer 1

but I can't edit the query to say entrance IS ? since that won't work when I pass anything other than NULL.

是的，你可以。这不是标准 SQL，而是 SQLite 扩展。来自 SQLite Query Language :

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL. Operators IS and IS NOT have the same precedence as =.

标准 SQL 中也存在相同的操作，但使用不同的语法:它看起来像 a IS NOT DISTINCT FROM b。此语法尚未得到广泛支持，但大多数数据库确实有某种方式来表达它。 a IS b 是 SQLite 的变体。