Question about null values

Question

Post reply

Question about null values

newbie_dev

SSCommitted

Points: 1916
More actions
April 30, 2009 at 4:11 am

#204081

Hi
I have a pretty stupid question...
I have a bit column(flag) and I want to select the records that don't have that column false, that means also the null ones
But when I run the query it only returns the true(1) values. Is the query the problem?should I use something like isnull(flag,1) ?
the query is a simple one :
select *
from table
where flag <> 0
10q

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You need to check for null. You can do it with the isnull function or with the is null operator:

select * from table where isnull(flag,1) = 1

select * from table where flag 0 or flag is null

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mithun.gite SSCrazy Points: 2333 More actions · Answer 2

yeah u should try isnull(flag,1), have u tried it or not?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

shnex (4/30/2009)
I have a bit column(flag) and I want to select the records that don't have that column false, that means also the null ones
But when I run the query it only returns the true(1) values. Is the query the problem?should I use something like isnull(flag,1) ?
10q

This is actually the default behaviour of SQL Server when handling NULL values. This behaviour is explained in the following links..

http://www.sqlservercentral.com/articles/Advanced+Querying/2829/

http://www.sqlservercentral.com/articles/Basic+Querying/understandingthedifferencebetweenisnull/871/

--Ramesh

newbie_dev SSCommitted Points: 1916 More actions · Answer 4

i did the "isnull" solution and it is working

the question now is : is this the best...the most professional solution?

or to verify separate : value and null value?

steveb. SSC-Forever Points: 46733 More actions · Answer 5

shnex (4/30/2009)
i did the "isnull" solution and it is working
the question now is : is this the best...the most professional solution?
or to verify separate : value and null value?

I would use

select * from table where flag 0 or flag is null

over

isnull(flag,1)

as the first code is sargeable so the optimizer should be able to use the index (if there is one)

FNS SSCrazy Points: 2015 More actions · Answer 6

If I have a bit field in a table tend to give it a default value and avoid the problem with NULL altogether. That way there's no confusion as to whether 0 or NULL mean the same thing for the developers.

For example:

In a table listing staff members I might have a non-nullable bit field called "HasLeftCompany" with a default of 0. When they leave it gets updated to 1. Simples. 😛

newbie_dev SSCommitted Points: 1916 More actions · Answer 7

Yes, it's right what you say, but my field is not mandatory, it only shows me that some persons have or not have a certain attribution. I also have persons that have no connection with that attributes, and I need them also. So the case with allow null on bit column is needed sometime. My opinon

10q for the help

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 8

FNS (4/30/2009)
If I have a bit field in a table tend to give it a default value and avoid the problem with NULL altogether. That way there's no confusion as to whether 0 or NULL mean the same thing for the developers.
For example:
In a table listing staff members I might have a non-nullable bit field called "HasLeftCompany" with a default of 0. When they leave it gets updated to 1. Simples. 😛

I think that I have to disagree with you on this one. Null and 0 don’t mean the same thing just as Null and 1 don’t mean the same thing. Null means that a value is missing. It could be because the value is unknown, it could be because it is not applicable for this row or could be because of another reason, but it is not the same as another value.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/