WHERE 子句中的 SQL 合并 [英] SQL Coalesce in WHERE clause

问题描述

我正在尝试在我拥有的存储过程中实现可选参数，但我遇到了问题.这是一个用于说明问题的简化查询:

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

当我运行这个查询时，我需要返回一行，因为一个 Torres 在 [MiddleName] 列中有 NULL.但查询返回零行.使用 IFNULL() 产生相同的结果.通过研究 COALESCE，我的印象是，如果所有表达式都是 NULL，则将返回 NULL.由于我不是 SQL 专家，因此我认为我遗漏了一些东西，但它是什么......

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.....

在此先感谢您的帮助.

推荐答案

问题是在 sql 中，WHERE Null = Null"永远不会返回任何行，因为 Null 不等于自身.

The problem is that in sql, "WHERE Null = Null" will never return any rows since Null does not equal itself.

你必须做的

SELECT * FROM [Customer]
WHERE [LastName] = 'Torres'
AND ( @MiddleName IS NULL OR [MiddleName] = @MiddleName )

这篇关于WHERE 子句中的 SQL 合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！