NULL values missing on a select query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

NULL values missing on a select query

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-03-02 : 14:20:23

Hi

I have been racking my brains over this but can't resolve it....

I have a query that as well as returning some data values, returns some null values also on a particular field, but when I put a NOT IN condition on the field my NULL values go missing.

i.e.

SELECT * FROM TABLE1 T1, TABLE2 T2
WHERE T1.ID=T2.ID
AND T1.TYPE NOT IN ('HOUSE','CAR','GARAGE')

I get the required results i.e. the things i don't want are not shown but it also does not show the NULL values which I want in the results.

If I don't put in the NOT IN condition in i get everything including the NULL values, but they go when i do put it in.

Can anybody help?

Thanks

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-02 : 14:46:01

[code]SELECT * FROM TABLE1 T1, TABLE2 T2
WHERE T1.ID=T2.ID
AND T1.TYPE NOT IN ('HOUSE','CAR','GARAGE')
OR T1.TYPE IS NULL
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 15:24:35

SELECT * FROM TABLE1 T1, TABLE2 T2
WHERE T1.ID=T2.ID
AND (T1.TYPE NOT IN ('HOUSE','CAR','GARAGE')
OR T1.TYPE IS NULL)

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-02 : 17:07:24

Fundamentally, this comes down to boolean logic with NULLs. Take a look at BOL for "Null Values." That has the truth tables and some explanation of how boolean comparisons work with NULLs. Also, it is good to understand what IN and NOT IN do. IN functions as a series of ORs and NOT IN works as a series of ANDs. For example:

DECLARE @Foo TABLE (ID INT IDENTITY(1,1), A VARCHAR(10))

INSERT @Foo
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'

SELECT *
FROM @Foo
WHERE A IN ('A', 'C', NULL) 


SELECT *
FROM @Foo
WHERE A NOT IN ('A', 'C', NULL)

If you run it you'll see that the second query does not return any results. The logic is equivalent to:

WHERE A <> 'A' AND A <> 'C' AND A <> NULL

Becasue TRUE and UNKNOWN (null) evaluates to UNKNOWN, you will never get any results. Unless of course you SET ANSI_NULLS OFF, but that's a different topic.

Here are the NULL truth tables I produced for another topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139528

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-03 : 03:01:17

[General advice]

When you use JOINs always use table qualifier instead * so use either t1.* ,t2.* or explicitely type the required columns with respective table qualifier

[/General advice]

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-03 : 04:20:37

quote:
Originally posted by madhivanan

[General advice]

When you use JOINs always use table qualifier instead * so use either t1.* ,t2.* or explicitely type the required columns with respective table qualifier

[/General advice]

Madhivanan

Failing to plan is Planning to fail

Thanks! Nice advice. I try to remember it every time in my practices.
And it will be very important at SEMI JOINS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources