WHERE子句使用可能为NULL的值

Question

我需要做这样的事情：

DECLARE @firstname VARCHAR(35)
DECLARE @lastname VARCHAR(35)
SELECT *
FROM Customers 
WHERE Firstname = @firstname AND Lastname = @lastname

问题是@firstname和@lastname有时可能是NULL。在这些情况下，上面的查询将无法找到匹配的行，因为NULL永远不等于NULL。

如果其中一个变量是NULL，那么如果数据库中对应的值也是NULL，我希望返回一个匹配。当然，SQL Server使用IS来比较NULL。在上面的例子中，如果其中一个值是NULL，则不认为它匹配。

有什么办法可以做到这一点吗？

Answer 1

只需使用AND/OR逻辑，例如

SELECT *
FROM Customers
WHERE ((Firstname IS NULL AND @firstname IS NULL) OR Firstname = @firstname)
AND ((Lastname IS NULL AND @lastname IS NULL) OR Lastname = @lastname);

Answer 2

您可以在相关的子查询中利用INTERSECT来实现这一点这是因为基于集合的操作将NULLs比较为相等。

编译器会自动将其编译为IS比较，不应该有任何性能打击。

DECLARE @firstname VARCHAR(35)
DECLARE @lastname VARCHAR(35)
SELECT *
FROM Customers c
WHERE EXISTS (SELECT c.Firstname, c.Lastname
INTERSECT
SELECT @firstname, @lastname)

逻辑是：对于每一行，创建一个具有两个值的单行虚拟表，并将其和两个变量相交，就必须有一个结果。

对于<>语义，将EXISTS更改为NOT EXISTS，而不是更改为EXCEPT，我发现前者优化得更好。

这一切都归功于保罗·怀特的把戏。

Answer 3

当@firstname、@lastname等为NULL时，可以使用ISNULL()函数返回替换值。如果等号运算符每一侧的两个默认值都相同，则表达式的计算结果将为true。例如：

SELECT *
FROM Customers 
WHERE ISNULL(Firstname, '') = ISNULL(@firstname, '')
AND ISNULL(Lastname, '') = ISNULL(@lastname, '')

我的解决方案假设数据中没有空字符串。但是，如果您确实希望某些值中包含空字符串，则可以将ISNULL()的默认值更改为其他值。

Answer 4

SQL Server 2022中现在提供了一个新选项。

您现在可以使用IS [NOT] DISTINCT FROM语法：

SELECT *
FROM Customers c
WHERE c.Firstname IS NOT DISTINCT FROM @firstname
AND c.Lastname  IS NOT DISTINCT FROM @lastname;

这编译成一个IS的比较路径，并且非常有效。