CODEWITHSUNDEEP
sqlsql-server
Stephanie
Stephanie

Reputation: 516

Unable to retrieve NULL data

I have three fields Category, Date, and ID. I need to retrieve data that does not belong under certain ID. Here is an example of my query:

SELECT Category, Date, ID
FROM table
WHERE ID NOT IN('1','2','3')
AND Date = '01/06/2015'

After running this query I should only get records that do not have any ID meaning NULL values because for yesterday's record only ID 1,2,3 exist and rest do not have any value (NULL). For some reason when I run the query it takes away the NULL values as well so I end up with 0 rows. This is very stranger to me and I do not understand what is the cause. All I know that the ID numbers are string values. Any suggestions?

Upvotes: 0

Views: 748

Answers (7)

J.B.A.J. Berkvens
J.B.A.J. Berkvens

Reputation: 166

Try this:

SELECT Category, Date, ID
FROM table
WHERE ID N
AND Date = '01/06/2015'

Upvotes: 0

Dan
Dan

Reputation: 10680

Take a look at NULL comparison search conditions.

Use the IS NULL or IS NOT NULL clauses to test for a NULL value. This can add complexity to the WHERE clause. For example, the TerritoryID column in the AdventureWorks2008R2 Customer table allows null values. If a SELECT statement is to test for null values in addition to others, it must include an IS NULL clause:

SELECT CustomerID, AccountNumber, TerritoryID
FROM AdventureWorks2008R2.Sales.Customer
WHERE TerritoryID IN (1, 2, 3)
   OR TerritoryID IS NULL

If you really want to be able to compare values to NULL's directly, you can do that as well. This is also described in the above article:

Transact-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF.

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

Please note that when you use "IN" or "NOT IN" which will not fetch any values if the column has got NULL values..

In your case, if you want to fetch only records with ID=NULL, then you can try the solution vgSefa suggested above..

If you want to pull all records with NULL as well as ID NOT IN('1','2','3'), then you could try something like this..

SELECT Category, Date, ID
FROM table
WHERE ID IS NULL
AND Date = '01/06/2015'

UNION ALL

SELECT Category, Date, ID
FROM table
WHERE ID NOT IN('1','2','3')
AND ID IS NOT NULL
AND Date = '01/06/2015'

Upvotes: 0

Heinzi
Heinzi

Reputation: 172220

Others have already shown how to fix this, so let me try to explain why this happens.

WHERE ID NOT IN('1','2','3')

is equivalent to 

WHERE ID <> '1' AND ID <> '2' AND ID <> '3'

Since NULL <> anything yields UNKNOWN, your expression yields UNKNOWN and the record in question is not returned.

See the following Wikipedia article for details on this ternary logic:

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15140

You got a couple of options:

SELECT Category, Date, ID
FROM table
WHERE ISNULL(ID, '4') NOT IN('1','2','3')
AND Date = '01/06/2015'

Or what su8898 said

Upvotes: 0

Sefa
Sefa

Reputation: 8992

Are you sure about you want ID fields as null?

Here is how you do it: (Assumins rest of your query is ok)

SELECT Category, Date, ID
FROM table
WHERE ID IS NULL
AND Date = '01/06/2015'

If you want records that does not have a category than you need to change your query as

SELECT Category, Date, ID
FROM table
WHERE Category IS NULL
AND Date = '01/06/2015'

Upvotes: 0

su8898
su8898

Reputation: 1713

Try this. NULL values cannot not be equated to anything else.

SELECT Category, Date, ID
FROM table
WHERE (ID NOT IN('1','2','3') OR ID IS NULL)
AND Date = '01/06/2015'

Upvotes: 2

Related Questions