当使用SELECT时，如何忽略WHERE子句中的NULL ?

Question

使用以下代码:

CREATE TABLE #MyTable 
(
PartNum VARCHAR(20),
PartColor VARCHAR(100)
)
INSERT INTO #MyTable 
VALUES
('00039','Blue'),
('00039','Red'),
('01234','Blue'),
('23456','Red')
CREATE TABLE #Variables 
(
VarName VARCHAR(20),
Value   VARCHAR(100)
)
INSERT INTO #Variables VALUES
('PartNum', '00039'),
('PartColor', NULL)
SELECT *
FROM MyTable
WHERE PartNum   = (SELECT Value FROM #Variables WHERE VarName = 'PartNum')
AND PartColor = (SELECT Value FROM #Variables WHERE VarName = 'PartColor')

如果PartColor为NULL，则应该忽略WHERE子句的那一部分，并且应该返回所有记录，而不管PartColor(假设PartNum = 00039)

我知道我可以这样做:

DECLARE @PartNum   VARCHAR(20) = '00039'
@PartColor VARCHAR(100) = NULL
SET     @PartColor = ISNULL(@PartColor, '-1')
SELECT *
FROM MyTable
WHERE PartNum   = @PartNum
AND PartColor IN (SELECT (@PartColor) OR @PartColor = '-1')

然而，我正在玩把变量放在一个表中，不确定如何实现相同的结果。

我尝试使用这个，但查询返回0结果:

AND PartColor IN ((SELECT Value from #Variables where VarName = 'PartColor' 
OR (SELECT ISNULL(Value, '-1') from #Variables where VarName = 'PartColor') = '-1'))

我很确定我不能这样检查值

这是一个想法，但语法无效:

AND PartColor IN ((SELECT Value from #Variables where VarName = 'PartColor') 
OR (SELECT Value from #Variables where VarName = 'PartColor') IS NULL)

Answer 1

要解决处理空值的特定问题，您可以简单地使用isnull

SELECT *
FROM MyTable
WHERE PartNum   = (SELECT isnull(Value, PartNum) FROM #Variables WHERE VarName = 'PartNum')
AND PartColor = (SELECT isnull(Value, PartColor) FROM #Variables WHERE VarName = 'PartColor')

Answer 2

首先，
您应该验证在您正在查询的列(在您的例子中是列"VarName")上有一个UNIQUE约束或PRIMARY KEY，因为在您的子查询中您期望一个单一的结果。

UNIQUE约束或PRIMARY KEY允许您从子查询中只提取一行(或者根本不提取行)。

为什么这很重要?
因为如果子查询提取的行数超过1行，就会出现错误，从而停止查询的执行，并导致正在查询数据库的应用程序出现异常。在你的情况下，你可能没有这个问题，但这仍然是你需要学习的东西。

CREATE TABLE #Variables 
(
VarName VARCHAR(20) PRIMARY KEY,
Value   VARCHAR(100)
)

然后，回答你的问题，你可以使用多个子查询@Stu已经回答https://stackoverflow.com/a/69020348/7327715

但是
当你在处理更大的数据库时，你可能想要增加SQL查询的可读性，为此，我建议使用变量来存储配置的值。

这样做的好处是可以减少查询的长度，避免使用子查询(子查询很容易变得难以阅读、编辑、修复和管理——例如，想象一下一个团队，每个成员在每次需要新的配置时都不断添加子查询)

DECLARE @PartNum VARCHAR(100) = (SELECT Value FROM #Variables WHERE VarName = 'PartNum')
DECLARE @PartColor VARCHAR(100) = (SELECT Value FROM #Variables WHERE VarName = 'PartColor')
SELECT *
FROM #MyTable
WHERE (PartNum = @PartNum OR @PartNum IS NULL)
AND (PartColor = @PartColor OR @PartColor IS NULL)