February 25, 2004 at 8:57 am
I noticed that if I do a "select where not in" and the table in the subquery contains a row that has a null value in the column I'm comparing, the select will return no rows.
Ex:
Table A contains 50 rows with a values in compare_column that do not exist in Table B
Table B contains 1 row where compare_column is null
select * from A where compare_column not in (select compare_column from B) = 0 Rows
In order to get the proper results, I have to specify "where compare_column is not null" in the subquery. Has anyone else come across this? Is this a bug? Can you assume that a row exists in a table just because there's a row with a null value in the field you're comparing?
February 25, 2004 at 9:13 am
I believe that this is by design. When a column contains null, what is really means is that it contains no value. Therefore, you can't use any comparison operators (including NOT IN). I make it a practice to try to always use IS NULL or IS NOT NULL with any column that could contain nulls. I think that you can use the WHERE NOT EXISTS syntax and get the results you desire.
February 25, 2004 at 12:58 pm
In this situation you are better off using NOT EXISTS with a correlated subquery.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply