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

Improve this question
6
  • 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.. Commented May 29, 2015 at 7:51
  • Is your subquery returning any NULLs? Commented May 29, 2015 at 7: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". Commented May 29, 2015 at 7:56
  • Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS? Commented May 29, 2015 at 8:01
  • Thank you all...all makes sense now :) Commented May 29, 2015 at 8:39

3 Answers 3

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This is great, thank you very much. Both of these work and give the same results. Btw, after the dates in the second query you have 'where' - this should be 'and' :)
@Imm:- Upadated that!
0

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

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

Improve this answer

Comments

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.