Mind blown by simple logic

Transact-SQL
1

Hi there,

In tblCONTACT there is a field DONOTMAILREASON

I am trying to filter out people who have died, who might have DONOTMAILREASON set to:

  • Deceased
  • Deceased_Flag

So my logic is:

WHERE DONOTMAILREASON<>'Deceased'
AND DONOTMAILREASON<>'Deceased_Flag'

To my utter surprise, the only records returned are ones where DONOTMAILREASON has some value other than 'Deceased' or 'Deceased_flag', like 'Gone Away' or 'Do not mail'.

All the thousand of records where DONOTMAILREASON is null have been removed from the results.

Is it just Hangover-Sunday or is NULL = 'Deceased' somehow?

Please help!

Thanks

2

NULL is not equal to anything, not even another NULL. It's meant to represent an unknown value. The query you've written is comparing the column against known values, and finding those that do not match either. The results are correct.

You'd need to change your WHERE clause to:

WHERE DONOTMAILREASON IS NULL
OR (DONOTMAILREASON<>'Deceased'
AND DONOTMAILREASON<>'Deceased_Flag')
2 Likes
3

Thank you Robert - great explanation and solution