时间戳

首页 > 解决方案 > NULL 值破坏搜索

sql - NULL 值破坏搜索

问题描述

我在查询中有以下 WHERE 语句，但如果 DateOfBirth 为 NULL，它不会返回记录的结果。我似乎无法弄清楚这一点，并想知道是否有人能看到原因？

WHERE
    (
        EXISTS( -- if we have words
                SELECT *
                FROM #SearchWords s
                WHERE 
                    ((@SearchType = 'FirstName' OR @SearchType = 'ALL') AND c.FirstName LIKE CONCAT (s.Word,'%'))
                    OR ((@SearchType = 'LastName' OR @SearchType = 'ALL') AND c.LastName LIKE CONCAT(s.Word,'%'))
                    OR ((@SearchType = 'PreferredName' OR @SearchType = 'ALL') AND c.PreferredName LIKE CONCAT(s.Word,'%'))
                    OR ((@SearchType = 'Password' OR @SearchType = 'ALL') AND c.Password LIKE CONCAT(s.Word,'%'))
                    OR ((@SearchType = 'IdentifierValue' OR @SearchType = 'ALL') AND CI.IdentifierValue LIKE CONCAT(s.Word,'%'))
            )
        OR @SearchWordCount = 0 --if we don't have words
    )
    AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth)

标签： sqlsql-servertsql

解决方案

您可以简单地使用OR表达式：

AND
(DateOfBirth IS NULL OR
 DateOfBirth BETWEEN COALESCE(@LowerDate, DateOfBirth) AND COALESCE(@UpperDate, DateOfBirth)
)

推荐阅读