原来以为not exists 会比not in 效率高,但在实际运用过程中,发现两表数据差不多的情况下,用另一种方式效率会更好. 原设计
原来以为not exists 会比not in 效率高,但在实际运用过程中,发现两表数据差不多的情况下,用另一种方式效率会更好.
原设计语句:
select * from (select * from ex_22222 where exattribute1 = '13') k1 where not exists
(
select * from
(
select ex_22222.entityid from ex_22222,ut_users where exattribute1 = '13'
and ex_22222.entityid = ut_users.uid
) k2
where k1.entityid = k2.entityid
)
新改进语句:
select k1.entityid,k2.entityid from (select * from ex_22222  where exattribute1 = '13') k1,
(
select * from
(
select ex_22222.entityid from ex_22222,ut_users where exattribute1 = '13'
and ex_22222.entityid = ut_users.uid
)
) k2
where k1.entityid = k2.entityid(+)
and k2.entityid is null
，