3

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

Swati
  • 50,291
  • 4
  • 36
  • 53
  • Can you make sure the g.groupname contains NULL value instead of string like "NULL". Sometimes it might confuse you. – Hong Ning Sep 14 '11 at 19:48
  • Yes. I did make sure. Besides if it didn't, ISNULL(g.groupname, null) IS NOT NULL wouldn't make any difference. – Swati Sep 14 '11 at 19:50

5 Answers5

4

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.

ssiscott
  • 56
  • 2
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
Magnus
  • 45,362
  • 8
  • 80
  • 118
  • Then this is equivalent: ISNULL(g.groupname, null) IS NOT NULL and g.groupName IS NOT NULL, right? Since when g.groupname is null, ISNULL will resolve to null. However, my queries are indicating that it is not equivalent. – Swati Sep 14 '11 at 19:31
2

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
six8
  • 2,886
  • 21
  • 20
  • It is possible that even when r.IsGroup = 1, it is not a group (or anything for that matter). I want to omit results which are not found in either of the tables, which happens only when r.IsGroup = 1 and and it's not in the groups table (happens because groups can be deleted). I am not able to do so. – Swati Sep 14 '11 at 19:44
  • Thanks. But I already know how to get the result I want, I am just wondering why my first query doesn't work but the second one doesn't - and the only difference is the use of ISNULL. Also, your first query gives me the same exact problem -that my original query gives me. It doesn't work and returns the extra row that should've been filtered out. – Swati Sep 14 '11 at 20:12
  • That's odd. Either it's some artifact of SQL server causing odd behavior or the data isn't as you expect it. You could add `u.userid` and `g.groupid` to the select result to make sure they are the values you expect them to be. With your queries, you have a large risk of unexpected results if you have `groupid`s and `userid`s that overlap unless you handle that condition in the on clause. – six8 Sep 14 '11 at 20:30
  • You can also change `where u.userid IS NOT NULL or g.groupid IS NOT NULL` to `where (r.isGroup != 1 and u.userid IS NOT NULL) or (r.isGroup == 1 and g.groupid IS NOT NULL)` but that shouldn't be necessary if the data is as expected - and it would perform horribly. Sorry I can't address the ISNULL problem directly as it seems there's an issue elsewhere causing the unexpected results. – six8 Sep 14 '11 at 20:33
  • The groupids and the userids never overlap. Never. I am quite certain the data is as I expected - otherwise ISNULL wouldn't work either :( – Swati Sep 14 '11 at 20:33
  • I did that too. Individually they both give me the expected results. So if `(r.isGroup != 1 and u.userid IS NOT NULL)` gives me 2 results, and `(r.isGroup == 1 and g.groupid IS NOT NULL)` gives me 3 results the OR of them is giving me 9 results instead of the expected 5. I am getting results in addition to what I'd be getting otherwise. I have no idea why. It's as if it is completely disregarding the WHERE clause. – Swati Sep 14 '11 at 20:35
0

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.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
0

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
Wil
  • 4,130
  • 1
  • 16
  • 15