SELECT ... FROM ... WHERE a.Blah = @Blah AND (a.VersionId = @VersionId OR (@VersionId IS NULL AND a.VersionId IS NULL));
上面的WHERE检查@VersionId的非NULL值和NULL值.
在性能方面是否更好地使用IF语句并将查询复制到一个搜索非NULL而另一个搜索NULL的问题呢？ ：
IF @VersionId IS NULL BEGIN
SELECT ...
FROM ...
WHERE a.Blah = @Blah AND a.VersionId IS NULL;
ELSE BEGIN
SELECT ...
FROM ...
WHERE a.Blah = @Blah AND a.VersionId = @VersionId;
END
或者查询优化器使它基本相同？
更新：
(注意：我使用的是sql Server)
(据我所知,对于这两种情况使用a.VersionId = @VersionId都行不通,是吗？)
解决方法
column = @argument OR (@argument IS NULL AND column IS NULL)
可以替换为
EXISTS (SELECT column INTERSECT SELECT @argument)
这将允许您将NULL与NULL匹配,并允许引擎有效地使用列上的索引.有关此技术的深入分析,请参阅Paul White的博客文章：
> Undocumented Query Plans: Equality Comparisons
由于在您的特定情况下有两个参数,您可以使用与@Blah相同的匹配技术 – 这样您就可以或多或少地简洁地重写整个WHERE子句：
WHERE EXISTS (SELECT a.Blah,a.VersionId INTERSECT SELECT @Blah,@VersionId)
这将使用索引(a.Blah,a.VersionId)快速工作.
Or the query optimizer makes it essentially the same?
在这种情况下,是的.在从sql Server 2005开始的所有版本(至少)中,优化器可以识别模式col = @var OR(@var IS NULL和col IS NULL)并将其替换为正确的IS比较.这确实依赖于内部重写匹配,因此可能存在更复杂的情况,这并不总是可靠的.
在sql Server from 2008 SP1 CU5 inclusive的版本中,您还可以选择通过OPTION(RECOMPILE)使用Parameter Embedding Optimization,其中任何参数或变量的运行时值在编译之前作为文字嵌入查询中.
因此,至少在很大程度上,在这种情况下,选择是一种风格问题,尽管INTERSECT结构无疑是紧凑和优雅的.
以下示例显示了每个变体的“相同”执行计划(文字与变量引用除外)：
DECLARE @T AS table
(
c1 integer NULL,c2 integer NULL,c3 integer NULL
UNIQUE CLUSTERED (c1,c2)
);
-- Some data
INSERT @T
(c1,c2,c3)
SELECT 1,1,1 UNION ALL
SELECT 2,2,2 UNION ALL
SELECT NULL,NULL,NULL UNION ALL
SELECT 3,3,3;
-- Filtering conditions
DECLARE
@c1 integer,@c2 integer;
SELECT
@c1 = NULL,@c2 = NULL;
-- Writing the NULL-handling out explicitly
SELECT *
FROM @T AS T
WHERE
(
T.c1 = @c1
OR (@c1 IS NULL AND T.c1 IS NULL)
)
AND
(
T.c2 = @c2
OR (@c2 IS NULL AND T.c2 IS NULL)
);
-- Using INTERSECT
SELECT *
FROM @T AS T
WHERE EXISTS
(
SELECT T.c1,T.c2
INTERSECT
SELECT @c1,@c2
);
-- Using separate queries
IF @c1 IS NULL AND @c2 IS NULL
SELECT *
FROM @T AS T
WHERE T.c1 IS NULL
AND T.c2 IS NULL
ELSE IF @c1 IS NULL
SELECT *
FROM @T AS T
WHERE T.c1 IS NULL
AND T.c2 = @c2
ELSE IF @c2 IS NULL
SELECT *
FROM @T AS T
WHERE T.c1 = @c1
AND T.c2 IS NULL
ELSE
SELECT *
FROM @T AS T
WHERE T.c1 = @c1
AND T.c2 = @c2;
-- Using OPTION (RECOMPILE)
-- Requires 2008 SP1 CU5 or later
SELECT *
FROM @T AS T
WHERE
(
T.c1 = @c1
OR (@c1 IS NULL AND T.c1 IS NULL)
)
AND
(
T.c2 = @c2
OR (@c2 IS NULL AND T.c2 IS NULL)
)
OPTION (RECOMPILE);
