not in clause, is there a better way

Question

I am trying to exclude id numbers that have been seen before, my query is

select id from table1 where date1 >= eDate1 and date1 <= eDate2 and zId = 256
and id no in
(select id from table1 where date1 < eDate1 and zId = 256)

MonetDB.

Is there a better way, I am trying to simply exclude any id number that has been seen in the zone before the selected date range?

Thanks.

Answer 1

Some database optimizers can deal with a large id list, other don't handle it well.

The best approach is to use a left join and filter on misses:

select a.id
from table1 a
left join table1 b on a.id = b.id and b.date1 < eDate1
where a.date1 between eDate1 and eDate2
and zId = 256
and b.id is null