How To Use In Operator with NULL

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

General SQL Server Forums

New to SQL Server Programming

How To Use In Operator with NULL

Author

Topic

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2007-06-26 : 17:17:03

Anybody know how to use the In Operator in the select stmt when one of the values is NULL? Here is my code:

select * from Client
where AgencyId is not null
--and AgencyName is not null
and left(controlnumber,2)<>'72'
and left(controlnumber,2) <>'13'
and CPAFirmId IN ('0','',NULL)

Since the IN operator requires all values to be in quotes, it does not work properly when NULL is specified. I also tried 'NULL' but that gives an error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-26 : 17:31:20

IN operator does not require values to be in quotes.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-26 : 17:38:03

This makes no sense, because CPAFirmId can never be equal to NULL:

CPAFirmId IN ('0','',NULL)

Try this instead:

( CPAFirmId IN ('0','') or CPAFirmId is null )

If CPAFirmId is actually an integer, you should remove the quotes.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 00:18:25

or

coalesce(CPAFirmId,'') in ('0','')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources