2

Please tell me why this does not work:

SELECT t1.id
FROM table1 t1
where t1.id not in
(select t2.id
from table2 t2
where t2.somedate between '2015-01-01 00:00:00' and '2015-04-30 23:59:59'
)
group by t1.id

The subquery works and the first select works but when I run the whole thing it comes up blank.

I am trying to find records (IDs) in one table that are not in another.

Any help would be much appreciated. This is on SQL Server btw.

Thanks

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
Imm
  • 23
  • 2
  • I have had the same problem using "not in". It's either a bug in sqlserver, or something about it that I don't understand. Use a left join as proposed. Maybe "not exists" works, don't quite remember. If anyone has any inputs as to why "not in" doesn't work, I would love to hear it.. – Tobb May 29 '15 at 07:51
  • Is your subquery returning any `NULL`s? – Rowland Shaw May 29 '15 at 07:55
  • This is due to NULL id in table, you can use Not Exists/Left Join(see my answer below)/Convert Null to something like "-1". – Anil May 29 '15 at 07:56
  • [Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?](http://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join) – Felix Pamittan May 29 '15 at 08:01
  • Thank you all...all makes sense now :) – Imm May 29 '15 at 08:39
  • another good-to-read..: http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null – tjeloep May 29 '15 at 09:10

3 Answers3

1

You can use the left join

SELECT t1.id
FROM table1 t1
Left Join table2 t2 on T2.Id=t1.id and
t2.somedate between '2015-01-01 00:00:00' and '2015-04-30 23:59:59'
where t2.id is null  group by t1.id

Other option is to use exists

select
    t1.id
from
    table1 t1
where
    not exists
    (select 1 from table2 t2 where t1.id = t2.id and 
     t2.somedate between '2015-01-01 00:00:00' and '2015-04-30 23:59:59'
    and t2.id is null  group by t1.id)
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
0

Use Left join 

SELECT t1.id
FROM table1 t1
Left Join table2 t2 on T2.Id=t1.id and
t2.somedate between '2015-01-01 00:00:00' and '2015-04-30 23:59:59'
where t2.id is null
group by t1.0

Or your query updated

SELECT t1.id
FROM table1 t1
where isnull(t1.id,-1) not in
(select isnull(t2.id,-1)
from table2 t2
where t2.somedate between '2015-01-01 00:00:00' and '2015-04-30 23:59:59'
)
group by t1.id
Anil
  • 3,722
  • 2
  • 24
  • 49
0

Your table2 may contains NULL result of id, so NOT IN not performed as expected.

Reference https://stackoverflow.com/a/3925698/1287352

Community
  • 1
  • 1
Eric
  • 5,675
  • 16
  • 24