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

I have three tables (simplified here):

我有三个表(这里简化了):

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

I want to retreive the first name / last name if the recipient is a user, and the group name if the recipient is a group. It's possible that the recipient is neither (i.e. a group that was deleted/no longer exists), so in that case I want to return nothing.

如果收件人是用户，我想要重新命名第一个名称/姓氏，如果收件人是一个组，则使用组名。可能接收方不是(即被删除/不再存在的组)，所以在这种情况下，我不想返回任何东西。

So this is what I did:

这就是我所做的:

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

The above query is not returning expected results. I am getting this back:

上面的查询没有返回预期的结果。我把它拿回来:

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

The last row is unexpected, since clearly there is no group name (g.groupname is NULL) and r.IsGroup = 1.

最后一行是出乎意料的，因为显然没有组名(g。groupname为NULL)和r。IsGroup = 1。

When I do this:

当我这样做:

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

I get expected results:

我得到预期结果:

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

And when I do this:

当我这样做的时候:

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

I get expected results:

我得到预期结果:

null, null, members, 1

It's only when I combine the two where clauses with an OR, do I get the additional row.

只有当我把两个where子句组合在一起时，我才会得到额外的一行。

Now, when I change my query to (change from IS NULL to ISNULL):

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

I get the expected results:

我得到了预期的结果:

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

In fact, I don't even have to change the where clause, the following query works just as well too and gives me the expected result shown above:

实际上，我甚至不需要更改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

SO, the question is, WHY? Why does putting ISNULL in my SELECT statement change how the WHERE clause works? Why does it make a difference at all? Why does my first query not work? Why is it that it fails to function only when I add the OR - why isn't it broken without it too?

所以，问题是，为什么?为什么在SELECT语句中放置ISNULL会改变WHERE子句的工作方式?为什么会有不同呢?为什么我的第一个查询不工作?为什么它不能只在我添加的时候才起作用呢?为什么没有它它也会被破坏呢?

Thanks in advance.

提前谢谢。

I am using MS SQL Server 2008.

我正在使用MS SQL Server 2008。

edits: fixed typo, clarified question

编辑:固定排版，明确问题。

5 个解决方案

#1

We found this to be an issue in Sql Server 2008 with no service packs installed. Try installing SP1 or SP2. In our case, the service pack resolved the issue.

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

#2

ISNULL is a function that retuns the specified input value if the column is null otherwise the column value.

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

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

#3

What I believe you're trying to do is get all users and groups, where if it's a user first_name and last_name will be something and groupname will be NULL, but if it's a group, first_name and last_name will be NULL and groupname will be something. Instead your WHERE clause is filtering the results, not defining the conditions of how your data is joined. I think this will get what you're looking for (assuming r.isGroup == 1 means it's a group):

我认为你要做的是得到所有的用户和组，如果它是一个用户first_name和last_name将是什么，groupname将是NULL，但是如果它是一个组，first_name和last_name将是NULL, groupname将是什么。相反，WHERE子句是过滤结果，而不是定义如何连接数据的条件。我想这会得到你想要的(假设r。isGroup == 1表示它是一个组):

To use more or less the original query but eliminate records that does not exist as a group or user:

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

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

However, I think a union with inner joins will perform better:

然而，我认为与内部联结的结合会表现得更好:

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

#4

From the SELECT clause:

从SELECT子句:

u.groupname

u.groupname

from the WHERE clause:

WHERE子句:

g.groupname

g.groupname

Make those two match and let is know if you see anything different.

让这两场比赛，让我们知道你是否看到了什么不同。

#5

This seems to get the results that you want, although if there are any gotchas from the NULLS swiss-cheesing your data it might not work:

这似乎得到了你想要的结果，尽管如果你的数据有任何问题，你的数据可能不会起作用:

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

#1