April 30, 2009 at 4:11 am
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
April 30, 2009 at 4:21 am
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/
April 30, 2009 at 4:23 am
yeah u should try isnull(flag,1), have u tried it or not?
April 30, 2009 at 4:49 am
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 onesBut 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
April 30, 2009 at 5:44 am
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?
April 30, 2009 at 5:57 am
shnex (4/30/2009)
i did the "isnull" solution and it is workingthe 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)
April 30, 2009 at 8:14 am
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. ๐
April 30, 2009 at 9:02 am
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
April 30, 2009 at 9:18 am
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/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply