需要优化not in sql语句

Question

我有一个sql查询：

select person from table t1
inner join person_history ph
on t1.person = ph.person
and t1.person not in (select person from person_history 
                      where effective_date < '01-01-2013')
and ph.person.effective_date > '01-01-2013'

由于person_history包含大量记录，因此此查询花费的时间太长。

我如何优化这段代码？

Answer 1

你就不能这样做吗：

select person from table t1
inner join person_history ph
on t1.person = ph.person
where ph.effective_date >= '01-01-2013'

Answer 2

您不需要排除NOT IN，因为它已经被排除在WHERE过滤器中！SQL将非常简单，如下所示：

select person from table t1
inner join person_history ph on t1.person=ph.person
where effective_date > '01-01-2013'

或者：

select person from table t1
WHERE person IN(select ph.person from person_history ph
where effective_date > '01-01-2013' and t1.person=ph.person)