Query not returning values for NULL

Question

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?

@huMptyduMpty -> This will not work - I am interested in selecting everything except the few rows where the assetclass actually contains 'Bond' — ssn
– ssn, Commented Oct 3, 2014 at 8:47
@Steffen: It will work. It will select all non-null values except bond (satisfies the Assetclass <> 'Bond' condition) as well as all null values (satisfies the Assetclass is null condition). — David Hedlund
– David Hedlund, Commented Oct 3, 2014 at 8:52
@DavidHedlund I agree it should, but I received a lot of unexpected rows using this and can't really explain why. EDIT: Ah forgot the parenthesis ;-) — ssn
– ssn, Commented Oct 3, 2014 at 8:57

Paolo · Accepted Answer · 2015-07-31 12:45:28Z

3

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.

edited Jul 31, 2015 at 12:45

answered Oct 3, 2014 at 8:44

Paolo

2,2731 gold badge17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:43:52Z

2

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

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Oct 3, 2014 at 8:44

Pablo Francisco Pérez Hidalgo

27.8k8 gold badges39 silver badges64 bronze badges

Comments

David Hedlund · Accepted Answer · 2014-10-03 08:47:00Z

1

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'

answered Oct 3, 2014 at 8:47

David Hedlund

130k33 gold badges205 silver badges223 bronze badges

Comments

Abdul Rehman Sayed · Accepted Answer · 2014-10-03 08:46:53Z

1

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

answered Oct 3, 2014 at 8:46

Abdul Rehman Sayed

6,7129 gold badges51 silver badges79 bronze badges

Collectives™ on Stack Overflow

Query not returning values for NULL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related