Does NULL equal to ZERO? - 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 2005 Forums

Transact-SQL (2005)

Does NULL equal to ZERO?

Author

Topic

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-03-03 : 18:05:39

I have a query which everytime I filter it to not show the value that has 0 the null doesn't show as well.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-03 : 18:18:20

no it does not equal to zero as you can guess from the results of your queries. you could have a design problem where you have no default value for your fields where you have NULL values or whichever application is interacting with it is not doing INSERT properly in that the offending field sometimes is 0 sometimes is NULL. or it is by design then you have to account for those in your queries. so why are there 0 values and NULL values in the same field?

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-03-03 : 18:26:27

I intentionally set the value to 0 or null. How will I filter it if the is data something like that?

col1|col2
data null
data2 0.00

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-03-03 : 18:32:57

what do you mean by how will you filter it?
where col2 = 0 or col2 is null

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-03-03 : 18:34:57

where col2 <> 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-04 : 02:15:03

quote:
Originally posted by k_cire0426

where col2 <> 0

If you want to show NULL, use

where col2 <> 0 or col2 is null

Madhivanan

Failing to plan is Planning to fail

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-03-04 : 09:35:21

yeah i used col2 <> 0 but it filters out also the NULL value...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:40:32

quote:
Originally posted by k_cire0426

yeah i used col2 <> 0 but it filters out also the NULL value...

yup. thats because as you said earlier, NULL is not regarded as a value unless you set ANSI NULL setting to off. So unless ANSI NULL setting is off, you need to use something like