CODEWITHSUNDEEP
sql-server
Imm
Imm

Reputation: 23

Find rows in one table that are not in another - SQL Server query

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

Upvotes: 2

Views: 742

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

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)

Upvotes: 1

Anil
Anil

Reputation: 3752

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

Upvotes: 0

Eric
Eric

Reputation: 5733

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

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

Upvotes: 0

Related Questions