title: IS NOT NULL and ISNULL( str, NULL ) in WHERE clause
body: <p>I have three tables (simplified here):</p>
  
  <pre><code>recipients: recipientId, isGroup
  users: userId, first name, last name
  groups: groupid, groupname
  </code></pre>
  
  <p>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.</p>
  
  <p>So this is what I did:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The above query is not returning expected results. I am getting this back:</p>
  
  <pre><code>adam, smith, null, null
  yolanda, smith, null, null
  null, null, members, 1
  null, null, null, 1
  </code></pre>
  
  <p>The last row is unexpected, since clearly there is no group name (g.groupname is NULL) and r.IsGroup = 1.</p>
  
  <p>When I do this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I get expected results:</p>
  
  <pre><code>adam, smith, null, null
  yolanda, smith, null, null
  </code></pre>
  
  <p>And when I do this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I get expected results:</p>
  
  <pre><code>null, null, members, 1
  </code></pre>
  
  <p>It's only when I combine the two where clauses with an OR, do I get the additional row.</p>
  
  <p>Now, when I change my query to (change from IS NULL to ISNULL):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I get the expected results:</p>
  
  <pre><code>adam, smith, null, null
  yolanda, smith, null, null
  null, null, members, 1
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>SO, the question is, WHY? <strong>Why does putting ISNULL in my SELECT statement change how the WHERE clause works?</strong> Why does it make a difference at all? <strong>Why does my first query not work?</strong> Why is it that it fails to function only when I add the OR - why isn't it broken without it too?</p>
  
  <p>Thanks in advance.</p>
  
  <p>I am using MS SQL Server 2008.</p>
  
  <p>edits: fixed typo, clarified question</p>