优化选择包含联合

Question

我有并且很容易选择：

define account_id = 7
select * from A where ACCOUNT_ID = &account_id
UNION
select * from B where ACCOUNT_ID = &account_id;

我想让account_id作为另一个选择的输入，我是这样做的：

select * from A where ACCOUNT_ID in(select accound_id from ACCOUNTS where EMAIL like '[e-mail]') -- id 7 returned
UNION
select * from B where ACCOUNT_ID in(select accound_id from ACCOUNTS where EMAIL like '[e-mail]')

如何优化只呼叫select accound_id from ACCOUNTS where EMAIL like '[e-mail]'一次？

Answer 1

我的第一个问题是union是否可以用union all代替。 因此，我的第一个尝试是使用exists和union all：

select a.*
from a
where exists (select 1
from accounts aa
where aa.account_id = a.account_id and
aa.email = '[e-mail]'
)
union all
select b.*
from b
where exists (select 1
from accounts aa
where aa.account_id = b.account_id and
aa.email = '[e-mail]'
);

对于此结构，您需要在accounts(account_id, email)上建立索引。exists只是查找索引中的值。 这确实需要扫描a和b。

如果查询返回少量行，并且您想要删除重复项，则union并替换union all。 如果它返回一大组行 - 并且每个表中没有重复项，并且有一种简单的方法来识别重复项 - 那么您可以改为执行以下操作：

with cte_a as (
select a.*
from a
where exists (select 1
from accounts aa
where aa.account_id = a.account_id and
aa.email = '[e-mail]'
)
)
select cte_a.*
from ctea_a
union all
select b.*
from b
where exists (select 1
from accounts aa
where aa.account_id = b.account_id and
aa.email = '[e-mail]'
) and
not exists (select 1
from cte_a
where cte_a.? = b.?  -- whatever is needed to identify duplicates
);

Answer 2

这就是WITH派上用场的地方

WITH ids AS (select account_id from ACCOUNTS where EMAIL like '[e-mail]')
select * from A where ACCOUNT_ID in ids
UNION ALL
select * from B where ACCOUNT_ID in ids;

我也把它改成了UNION ALL，因为它快得多。