IS NOT NULL and ISNULL( str, NULL ) in WHERE claus

问题:

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.

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.

Now, when I change my query to (change from IS NULL to 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:

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?

Thanks in advance.

I am using MS SQL Server 2008.

edits: fixed typo, clarified question

回答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.

回答2:

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

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):

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:

u.groupname

from the WHERE clause:

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