NOT IN and NULL - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

NOT IN and NULL

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-06-15 : 09:02:26

In a where clause I was using status_code <> 'A' as one of the condition and I noticed that none of the NULL valued rows are not picked up.I changed to status_code NOT IN ('A') again the same.So my final change was to make it (status_code NOT IN ('A') OR status_code IS NULL) and then the next condition.It is working fine now.Please let me know if there is any work around.

Below shown are the conditions in the query :
state NOT IN('NH','MA')AND
language_code NOT IN('SO','SP') AND
status_code NOT IN ('A') OR status_code IS NULL) AND
stage_indicator IN('C','D','E','O','P','X','V')

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-15 : 09:41:36

you should always simply explicitly state what you want.

(Status_code <>'A' or Status_Code is null)

Make sure you read up on Nulls and how they work; it is crucial to fully understand NULLS if you are working with SQL databases.

- Jeff
http://weblogs.sqlteam.com/JeffS

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-15 : 09:42:17

You have what you want - why do you want a work around.

Any expression (usually) that contains a null will return a null which is interpreted as false
so
null in ('a') = null = flase
not null in ('a') = not null = null = false

you could set ansi_nulls off
or
coalesce (status_code,'') NOT IN ('A')

but what you have should be good.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources