WHERE 子句中的 IS NOT NULL 和 ISNULL( str, NULL )

Question

我有三个表（此处简化）：

recipients: recipientId, isGroup
users: userId, first name, last name
groups: groupid, groupname

如果收件人是用户，我想检索名字/姓氏；如果收件人是组，我想检索组名。收件人可能两者都不是（即已删除/不再存在的组），因此在这种情况下我不想返回任何内容。

这就是我所做的：

select u.first_name, u.last_name, g.groupname, r.isGroup
from recipients r
left join users u on u.userid = r.recipientId
left join groups g on g.groupid = r.recipientId
where r.isGroup IS NULL or g.groupname IS NOT NULL

上面的查询没有返回预期的结果。我得到了这个：

adam, smith, null, null
yolanda, smith, null, null
null, null, members, 1
null, null, null, 1

最后一行是意外的，因为显然没有组名称（g.groupname为NULL）和r.IsGroup = 1。

当我这样做时：

select u.first_name, u.last_name, g.groupname, r.isGroup
from recipients r
left join users u on u.userid = r.recipientId
left join groups g on g.groupid = r.recipientId
where r.isGroup IS NULL

我得到预期的结果：

adam, smith, null, null
yolanda, smith, null, null

当我这样做时：

select u.first_name, u.last_name, g.groupname, r.isGroup
from recipients r
left join users u on u.userid = r.recipientId
left join groups g on g.groupid = r.recipientId
where g.groupname IS NOT NULL

我得到预期的结果：

null, null, members, 1

只有当我用 OR 组合两个 where 子句时，我才会得到额外的行。

现在，当我将查询更改为（从 IS NULL 更改为 ISNULL）时：

select u.first_name, u.last_name, g.groupname, r.isGroup
from recipients r
left join users u on u.userid = r.recipientId
left join groups g on g.groupid = r.recipientId
where r.isGroup IS NULL or ISNULL(g.groupname, null) IS NOT NULL

我得到了预期的结果：

adam, smith, null, null
yolanda, smith, null, null
null, null, members, 1

事实上，我什至不必更改 where 子句，以下查询也同样有效，并为我提供了预期结果如上所示：

select u.first_name, u.last_name, ISNULL(g.groupname,null), r.isGroup
from recipients r
left join users u on u.userid = r.recipientId
left join groups g on g.groupid = r.recipientId
where r.isGroup IS NULL or g.groupname IS NOT NULL

那么，问题是，为什么？ 为什么在 SELECT 语句中放入 ISNULL 会改变 WHERE 子句的工作方式？ 为什么它会产生影响？ 为什么我的第一个查询不起作用？为什么只有当我添加 OR 时它才起作用 - 为什么没有它它也不会损坏？

提前致谢。

我正在使用 MS SQL Server 2008。

编辑：修复了拼写错误，澄清了问题

Answer 1

我们发现这是未安装服务包的 Sql Server 2008 中的问题。尝试安装 SP1 或 SP2。在我们的例子中，服务包解决了这个问题。

Answer 2

ISNULL 是一个函数，如果列为空，则返回指定的输入值，否则返回列值。

ISNULL(tbl.x, 'y') --returns 'y' if tbl.x is null otherwise the value of tbl.x

Answer 3

我相信你想做的是获取所有用户和组，如果是用户，则first_name和last_name将为NULL，groupname将为NULL，但如果它是一个组，first_name和last_name将为NULL，groupname将为NULL 。相反，您的 WHERE 子句会过滤结果，而不是定义数据连接方式的条件。我认为这将得到您正在寻找的内容（假设 r.isGroup == 1 意味着它是一个组）：

或多或少地使用原始查询，但消除不作为组存在的记录或用户：

select u.first_name, u.last_name, g.groupname, r.isGroup
from recipients r
left join users u 
    on u.userid = r.recipientId and r.isGroup != 1
left join groups g 
    on g.groupid = r.recipientId and r.isGroup == 1
where u.userid IS NOT NULL or g.groupid IS NOT NULL

但是，我认为具有内部联接的联合会表现更好：

select u.first_name, u.last_name, NULL, r.isGroup
from recipients r
inner join users u on u.userid = r.recipientId
where r.isGroup != 1
union
select NULL, NULL, g.groupname, r.isGroup
from recipients r
inner join groups g on g.groupid = r.recipientId
where r.isGroup == 1

Answer 4

从 SELECT 子句：

u.组名

来自 WHERE 子句的

：

g.groupname

让这两个匹配，如果您发现任何不同，请告诉我们。

Answer 5

这似乎得到了您想要的结果，尽管如果 NULLS swiss-cheesing 您的数据存在任何问题，它可能不起作用：

select u.first_name, u.last_name, g.groupname, r.isGroup
from #recipients r
left join #users u on u.userid = r.recipientId
left join #groups g on g.groupid = r.recipientId
WHERE  g.groupname IS NOT NULL 
    OR ISNULL(r.IsGroup,0) > CASE WHEN g.groupid IS NOT NULL AND g.groupname IS NULL THEN -1 ELSE 0 END