CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ssn
ssn

Reputation: 509

Query not returning values for NULL

I am having a bit of trouble with a query for SQL Server 2008.

I have a table with some values and a category. This category can be e.g. Stock, Bond or NULL.

Then I may want to see everything in my table that is not bonds:

SELECT Value, Name, Currency, Assetclass
FROM MyTable
WHERE Assetclass <> 'Bond'

Here I expect to see all my assets that are Stock and uncategorised (NULL). But instead I only see the stocks. I get the same result as setting my Where-condition to Assetclass = 'Stock'.

I am aware that NULL is treated as an unidentified value, but I would expect it to only disregard rows that contain exactly 'Bond' and keep everything else, but this is apparently not the case?

Upvotes: 0

Views: 80

Answers (4)

Paolo
Paolo

Reputation: 2254

This is the expected behaviour.

You are asking for all the rows that have a value that is different from 'Bond'.

NULL is not a value but a 'marker' stating that the system have no clue about the content of that field; being the content unknown the system cannot say for sure that the value is different from 'Bond' hence the row is not returned.

Upvotes: 3

Pablo Francisco P&#233;rez Hidalgo
Pablo Francisco P&#233;rez Hidalgo

Reputation: 27702

You can't compare NULL values using <>, you should change your SQL to:

SELECT Value, Name, Currency, Assetclass
FROM MyTable
WHERE Assetclass <> 'Bond' OR Assetclass IS NULL;

In MySQL you have null safe comparators but that is not the case in SQL Server.

The idea is that NULL is not a value, therefore it can't be compared to other values. However, you can check if a field is NULL using IS NULL.

Please, check this question for more insight: Why is null<>null=null in mysql

Upvotes: 2

David Hedlund
David Hedlund

Reputation: 129792

As others have pointed out, this is the expected behavior. If you don't want to do an OR you could always replace null with something else in your comparison:

WHERE ISNULL(Assetclass, 'Anything but Bond') <> 'Bond'

Upvotes: 1

Abdul Rehman Sayed
Abdul Rehman Sayed

Reputation: 6672

Projection will ignore null values. Use Isnull function 

SELECT Value, Name, Currency, Assetclass
FROM MyTable
WHERE Isnull(Assetclass,'')  <> 'Bond'


OR 


SELECT Value, Name, Currency, Assetclass
FROM MyTable
WHERE Assetclass is null or  Assetclass <> 'Bond'

I would prefer the first approach

Upvotes: 1

Related Questions