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.s 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子句时，我才能得到额外的行。

现在，当我改变我的查询(从ISNULL更改为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

所以，问题是，为什么?为什么把ISNULL在我的SELECT语句改变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将是什么，groupname将是NULL，但如果它是一个组，first_name和last_name将是NULL, groupname将是什么。相反，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.groupname

from WHERE子句:

g.groupname

Answer 5

这似乎得到了您想要的结果，尽管如果从NULLS瑞士奶酪中获得任何漏洞，它可能无法工作:

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