Optimalization of select containing Union

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 '[e-mail]') -- id 7 returned
UNION
select * from B where ACCOUNT_ID in(select accound_id from ACCOUNTS where EMAIL like '[e-mail]')

How could be this optimalized to call select accound_id from ACCOUNTS where EMAIL like '[e-mail]' only once?

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 = '[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]'
             );

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 = '[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
                 );