I have and easy select:
define account_id = 7 select * from A where ACCOUNT_ID = &account_id UNION select * from B where ACCOUNT_ID = &account_id;
I would like to have account_id as input from another select and I did it this way:
select * from A where ACCOUNT_ID in(select accound_id from ACCOUNTS where EMAIL like 'aa@aa.com') -- id 7 returned UNION select * from B where ACCOUNT_ID in(select accound_id from ACCOUNTS where EMAIL like 'aa@aa.com')
How could be this optimalized to call select accound_id from ACCOUNTS where EMAIL like 'aa@aa.com' only once?
Advertisement
Answer
My first question is whether the union can be replaced by the union all. So, my first attempt would be to use exists and union all:
select a.*
from a
where exists (select 1
from accounts aa
where aa.account_id = a.account_id and
aa.email = 'aa@aa.com'
)
union all
select b.*
from b
where exists (select 1
from accounts aa
where aa.account_id = b.account_id and
aa.email = 'aa@aa.com'
);
For this structure, you want an index on accounts(account_id, email). The exists simply looks up the values in the index. This does require scanning a and b.
If the query is returning a handful of rows and you want to remove duplicates, then union and replace union all. If it is returning a large set of rows — and there are not duplicates in each table and there is an easy way to identify the duplicates — then you can instead do:
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 = 'aa@aa.com'
)
)
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 = 'aa@aa.com'
) and
not exists (select 1
from cte_a
where cte_a.? = b.? -- whatever is needed to identify duplicates
);