WHERE 子句使用可能为 NULL 的值 [英] WHERE clause using values that could be NULL

问题描述

我需要做这样的事情:

DECLARE @firstname VARCHAR(35)
DECLARE @lastname VARCHAR(35)

SELECT *
FROM Customers 
WHERE Firstname = @firstname AND Lastname = @lastname

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

The problem is that @firstname and @lastname could sometimes be NULL. In those cases, the query above would fail to find matching rows because NULL is never equal to NULL.

如果这些变量之一是 NULL，如果数据库中的相应值也是 NULL，我想返回一个匹配项.但是，当然，SQL Server 使用 IS 来比较 NULL.如果上述示例中的任一值是 NULL，则不会将其视为匹配项.

If one of these variables is NULL, I'd like to return a match if the corresponding value in the database is also NULL. But, of course, SQL Server uses IS to compare for NULL. And if either value is NULL in the example above, it is not considered a match.

有没有办法做到这一点?

Is there any way to accomplish this?

推荐答案

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

Just use AND/OR logic e.g.

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);

这篇关于WHERE 子句使用可能为 NULL 的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！