I've used Oracle for the past 5 years, but I have new position doing data extraction from SQL 2008. My position does not allow access down to the admin or t-sql level, so some things are beyond my reach.

Here's the problem. I'm running a simple query on a 'code' table in our main application. It's not pulling the correct number of records based on my '=' vs '<>' criteria. Here's an example of what I'm seeing:

SELECT COUNT(DISTINCT [my_code]) My_code_countfrommytable

That produces the total number of distinct records in mytable which is 161. That is correct.

This should produce the total number of non-obsolete records. The correct number is 80, but it's returning 18 (?!?). When I change the where clause to ‘where obsolete is null’ it pulls 62 records…still not correct. But when I do ‘where obsolete <> ‘Y’ or obsolete is null’ it pulls 80…correct number. But that seems kinda goofy unless I’m missing something here.

I'm see the same (incorrect) results using a data extraction utility built into the application as well as doing an excel query with odbc link. So it seems like a syntax problem in my query (which I doubt) or a database issue.

I also used rtrim(ltrim(obsolete)) to trim any spaces...same incorrect results.

This seems like programming 101...<> should produce the exact opposite of =.

I want to understand if this is a quirk in how SQL handles null values. I don't want to just 'patch' the query so it works, because this will affect how I approach things in the future. This goes against my 28 years of programming experience where <> ALWAYS produces the exact opposite of =.