WHERE 子句中的 SQL 合并
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/635124/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Coalesce in WHERE clause
提问by mpeterb
I'm trying to implement optional parameters in a stored procedure that I have but I am running into a problem. Here's a simplified query to illustrate the issue:
我正在尝试在我拥有的存储过程中实现可选参数，但我遇到了问题。这是一个简化的查询来说明问题：
SET ANSI_NULLS OFF
DECLARE @MiddleName VARCHAR(20);
SET @MiddleName = NULL;
SELECT * FROM [Customer]
WHERE [LastName] = 'Torres'
AND [MiddleName] = COALESCE(@MiddleName, [MiddleName])
When I run this query I need to get one row back because one Torres has NULL in the [MiddleName] column. But the query returns zero rows. Using IFNULL() produces the same result. From researching COALESCE, I was under the impression that NULL would be returned if all expressions are NULL. As I am not a SQL expert I assume that I am missing something, but what is it.....
当我运行这个查询时，我需要返回一行，因为一个 Torres 在 [MiddleName] 列中有 NULL。但查询返回零行。使用 IFNULL() 产生相同的结果。通过研究 COALESCE，我的印象是，如果所有表达式都是 NULL，则将返回 NULL。由于我不是 SQL 专家，因此我认为我遗漏了一些东西，但它是什么.....
Thanks in advance for any help.
在此先感谢您的帮助。
回答by David
The problem is that in sql, "WHERE Null = Null" will never return any rows since Null does not equal itself.
问题是在 sql 中，“WHERE Null = Null”永远不会返回任何行，因为 Null 不等于自身。
You have to do
你必须要做
SELECT * FROM [Customer]
WHERE [LastName] = 'Torres'
AND ( @MiddleName IS NULL OR [MiddleName] = @MiddleName )
回答by MatBailie
You state you are looking for the query to return the row where the field MiddleName is NULL. Unfortunately (NULL = NULL) does not return true, it returns NULL.
您声明您正在寻找查询以返回字段 MiddleName 为 NULL 的行。不幸的是 (NULL = NULL) 不返回 true，它返回 NULL。
You need something like...
你需要像...
SELECT * FROM [Customer]
WHERE [LastName] = 'Torres'
AND ([MiddleName] = @MiddleName OR @MiddleName IS NULL)
回答by gcores
Are you trying to do this?
你想这样做吗？
SELECT * FROM [Customer]
WHERE [LastName] = 'Torres'
AND ([MiddleName] = @MiddleName OR @MiddleName IS NULL)
From what I understand it looks like it.
据我了解它看起来像。
回答by LukeH
Your COALESCEreturns NULLwhen the @MiddleNameparameter and the MiddleNamecolumn are both NULL, but the test will evaluate to false because a NULLdoes not equal any other NULL.
当参数和列都为时，您COALESCE将返回，但测试将评估为 false，因为a不等于任何其他。NULL@MiddleNameMiddleNameNULLNULLNULL
To workaround this you should explicitly test the @MiddleNameparameter for nullity:
要解决此问题，您应该明确测试@MiddleName参数是否为空：
SELECT *
FROM [Customer]
WHERE [LastName] = 'Torres'
AND (@MiddleName IS NULL OR [MiddleName] = @MiddleName)
