Question regarding Null values

[BOA_Dev]

SELECT * from Table1 where ID = '11927254'

Returns one Row.


Select * from Table2 where ID = '11927254'

That returns nothing.




SELECT * from Table1 where ID NOT IN (SELECT ID FROM Table2)

And I was surpised to find out that this doesnt return anything, not even the row from Table1 with ID '11927254'. I discoverd that some rows in Table2 have a null ID, and because of this, this query was not returning the row. But I assumed that even if the Table2's ID was null, the clause "NOT IN (SELECt ID FROM Table2)" would still pass but apparently not. When it runs into a null value, the whole query doesnt work and returns nothing


So I am a bit confused about null values and why you can't compare a real value to a null value. Anyone want to take a stab at this?

 

[cjard]

I cant fully answer because you havent given the entire data contents of table1 or table2.

I will however, impart a little wisdom on nulls and SQLs in general

1) Do not use the IN clause in combination with a select statement unless you are sure that the select statement will never return more than about 20 rows. A far better performing method is to join the two blocks of data using an outer join and look for nulls that arise from mismatches:

SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.key = t2.key WHERE t2.ID IS NULL

2) An ID that can be null isnt a very good ID. Choose another name for the column, because people usually assume that the ID is a primary key or part of it

3) NULL shouldnt be used with most statements other than IS and IS NOT. NULLs cannot be equated to anything, even other NULLs. A NULL is never greater or less than anything, though it usually appears at a predefined point in a sort order (like, the top or the bottom) - you shouldnt use this to infer that a NULL is greater than or less than anything else though.



NULL is the absence of any value. It's the schrodingers cat of the data world. Because it is unknown, you cannot say it definitely does or does not equal another thing.
THink of a number between one and ten. I will too. Do you think we are thinking of the same number? i.e. are our numbers equal? Yes? No? Dont know?
Tell me your number. OK, it's a 4. Now tell me, are our numbers equal? Yes? No? Dont know?

So NULL as an unknown (my number was always null to you) cannot be equated or not equated. You can only say whether you know my number or you dont. i.e. IS NULL 9is unknown to you) IS NOT NULL (is soemthing you know)


As a result, IN doesnt work with nulls because IN, conceptually is:

SELECT * FROM t WHERE c IN (1,2,3)
SELECT * FROM t WHERE c = 1 OR c = 2 OR c = 3

With NULLs:


SELECT * FROM t WHERE c IN (1, NULL)
SELECT * FROM t WHERE c = 1 OR c = NULL


Remember, nulls cannot be equated and IN will DELIBERATELY NOT EXPAND TO:

SELECT * FROM t WHERE c = 1 OR c IS NULL

i.e. IN is not intelligent


It just doesnt do that. Thats fixed. MS chose that, ANSI SQL chose that, standards and compliance chose that. You must know it and program accordingly. Its a rule of the database and you cannot alter it.


Note that other clauses still work:

SELECT * FROM t WHERE c IN (1, NULL)
SELECT * FROM t WHERE c = 1 OR c = NULL


You WILL get rows where C = 1
You WILL NOT get rows where C is NULL


If your SELECT .. WHERE NOT IN (SELECT ..) query is returning nothing, then it is simply because the entire list of the sub-select is present in the super select

i.e. your super table contains 1, 2, 3, and your sub-select returns (1, 2, 3, NULL)
All supertable rows are accounted for.. none of them is NOT IN the sub-list


-

Try this for yourself:

SELECT * from Table1 where ID IN ('11927254', NULL)
--> 1 row

SELECT * from Table1 where ID NOT IN ('11927254', NULL)
--> 0 rows

SELECT * from Table1 where ID NOT IN ('11927254')
--> 0 rows

 

[BOA_Dev]

sorry its taken me so long to respond. I still don't understand why a query like this:

SELECt * from table1 where field1 = 'Hello'

would fail completely if some rows in table1 had null in field1. It won't even return the rows that have 'Hello' in field1. It just returns an empty set.

I have to do this in order to get it to return something:

SELECt * from table1 where ISNULL(field1,'') = 'Hello'


that returns the rows where field1 is 'Hello'. But I don't see why ISNULL check is necessary.

 

[cjard]

sorry its taken me so long to respond. I still don't understand why a query like this:

SELECt * from table1 where field1 = 'Hello'

would fail completely if some rows in table1 had null in field1. It won't even return the rows that have 'Hello' in field1. It just returns an empty set.

Pardon my disbelief, but this is (in my experience) impossible

But I don't see why ISNULL check is necessary.

It isnt. There is something very wrong with your RDBMS. Please install Windows Media Encoder and use it to capture the screen of you replicating this issue (use a 640x480 region, Low quality 2fps, keeps the sizes reasonable). In 10 years of coding I have never come across a database that returns 0 rows from a query if a row in the WHERE column is null..

PS; ISNULL is not a check, its a replacement -> any nulls are replaced by an empty string. ISNULL(field1, 'blank') would replace any nulls with the word blank

 

[Arg81]

Originally Posted by BOA_Dev
sorry its taken me so long to respond. I still don't understand why a query like this:

SELECT * from table1 where field1 = 'Hello'

would fail completely if some rows in table1 had null in field1. It won't even return the rows that have 'Hello' in field1. It just returns an empty set.

I've just run a few tests on some of my tables that have null fields, and I still get a results set. From this the problem is likely to be on your end.

When you run that query above, was it in TableAdapter Wizard, or SQL Query Analyser?

What ever one you did it in, try the other. I personally cannot replicate what you are getting...