SQL table with a field that contains null values

Question

A table called table1 has a field called field1 with null values in it. This query does not return any rows:

SELECT *
FROM table1
WHERE field1 NOT IN
(
   SELECT field1
   FROM table1
)

I know there are better ways of writing this query. What causes this behaviour i.e. using not in with a field that contains null values.

The table is not normalised (I realise this is not a good idea but there is nothing I can do about it for now). The query I am trying to run is as follows: SELECT * FROM table1 where field1 = 'Value1' and field1 not in (select field1 from table1 where field1 = 'value2'). I am trying to find all the rows in table 1 where field1 = value1, but exclude from the resultset those where field1=value2 on other rows in the table. I realise there are better ways of writing this query. My question is about using NOT IN with columns that contain nulls. — w0051977, Dec 03 '11 at 21:41
Yes it does. Here is an example: create table TestTable (id int, type varchar(100), indexvalue integer) insert into TestTable values (1, 'book', 8) insert into TestTable values (2, 'video', 7) insert into TestTable values (3, 'video', null) select * from testtable where type = 'book' and indexvalue not in (select indexvalue from testtable where type='video') I would expect one row to be returned in the above query, but there are none. I believe this is because there are null values in 'indexvalue. — w0051977, Dec 03 '11 at 22:26

score 2 · Answer 1 · edited Dec 03 '11 at 21:49

2

OK, I might be missing the point here, but one way to rephrase your query is:

Step 1: Take all values of table1.field1.
Step 2: Return all table1 rows whose field1's value isn't among the values obtained in Step 1.

Surely this always returns the empty set?

edited Dec 03 '11 at 21:49

marc_s

732,580
175
1,330
1,459

answered Dec 03 '11 at 21:35

NPE

486,780
108
951
1,012

Thanks. Here is an example. create table TestTable (id int, type varchar(100), indexvalue integer) insert into TestTable values (1, 'book', 8) insert into TestTable values (2, 'video', 7) insert into TestTable values (3, 'video', null) select * from testtable where type = 'book' and indexvalue not in (select indexvalue from testtable where type='video') I would expect one row to be returned in the above query, but there are none. I believe this is because there are null values in 'indexvalue'. – w0051977 Dec 03 '11 at 22:04

score 0 · Answer 2 · answered Dec 03 '11 at 22:08

0

In assumption that there is primary key field exists in table, you may use this script:

select
    t1.*
from
    dbo.Table1 as t1
where 
    t1.field1 = 'Value1'
    and not exists( select 1 from Table1 as t2 where t1.ID != t2.ID and t2.field1 = 'Value2')

answered Dec 03 '11 at 22:08

Yuriy Rozhovetskiy

22,270
4
37
68

Thanks, but in my original quuestion I said that there are better ways of writing this query. My question relates specifically to using 'NOT IN' with columns that contain nulls. – w0051977 Dec 03 '11 at 22:36
I have discovered the following post, which answers my question: http://stackoverflow.com/questions/173041/not-in-vs-not-exists. – w0051977 Dec 04 '11 at 12:18

SQL table with a field that contains null values

2 Answers2