SQL Making me scratch my noggin, someone explain please!

Peace

I have a table (TBL_SomeTable) with 4 columns (A , B , C , D). Column D is a bit column that allows nulls.
Lets say there are 10 rows in TBL_Something: 5 rows that value in D is 1, and 5 rows the value in D is NULL

IF i do a:

'select * FROM TBL_Something WHERE D <> 1'

i get no rows returned. Surely the rows where the value of D is null should be returned, as NULL is not equal to 1!

Now what i did to fix my conundrum was to set a default value of 0 on colunm D so no NULLS appear in the table.

Can someone please explain whee my logic fell down.

seamus

To the best of my knowledge, the standard for all SQL queries is not to return any rows where the row on which you are querying returns a null value. That is, NULL is not a comparable value in a row, so SQL doesn't compare NULL and 1, it just ignores it. Thus, it only returns rows which return true for D <> 1, and since rows with a null value in D aren't tested and so do not return a TRUE value, they're not included.

In order to include the null values, just alter your query to

'select * FROM TBL_Something WHERE D <> 1 OR D IS NULL'

Phileas Fogg

Don't accept NULL values in bit fields and always default them to 0, that should avoid this problem. TBH I don't know why Sql Server allows you to accept NULLs - probably for a reason I haven't run into yet.

Peace

seamus wrote:

To the best of my knowledge, the standard for all SQL queries is not to return any rows where the row on which you are querying returns a null value. That is, NULL is not a comparable value in a row, so SQL doesn't compare NULL and 1, it just ignores it. Thus, it only returns rows which return true for D <> 1, and since rows with a null value in D aren't tested and so do not return a TRUE value, they're not included.

In order to include the null values, just alter your query to

Yup that explanation makes sense seamus, cheers.

Phileas Fogg i take you point, i was being a bit lazy with my dbase design

amen

I was doing this

'select * FROM TBL_Something WHERE D <> 1 OR D IS NULL'

I would do

'select * FROM TBL_Something WHERE ISNULL(D,0) <> 1 '

much nicer and removes the nasty or clause mind you <> isn't great either
and as a side note I much prefer the sql != instead of <>