Check if any of the values is in a subquery result

Try:

where exists (select * .... 
        where Customers.orderId = ... 
        or Customers.secondaryId = ...
     )

Eg, if you were planning on:

where orderId in (select value from ...)
or secondaryorderid in (select value from ...)

Then you make it so that you only call your subquery once, and build your OR clause into it.

 where exists (select * from ... 
        where Customers.orderId = value 
        or Customers.secondaryOrderId = value
     )

The whole point of this is to ensure that the complicated subquery is only executed one time. That doesn't happen with a CTE, or by replacing two INs with two EXISTSs.

You query should probably be rewritten as an exists instead of an in

See this link for more examples.

Your query would then look something along the lines of

select *
from Customers C
where exists (select 'x' from ordertable o where c.orderid = o.orderid) 
or exists (select 'x' from ordertable o where c.secondaryOrderId = o.orderid) 

If both subqueries are the same you could remove one of them and combine them like so

select *
from Customers C
where exists (select 'x' from ordertable o where c.orderid = o.orderid or c.secondaryOrderId = o.orderid) 

Why not use a Common Table Expression aka with clause? It's designed for exactly this purpose (among others).

with orderIds as (
  select orderId
  from ...
)
select *
from Customers
where orderId in (select orderId from orderIds) 
or secondaryOrderId in (select orderId from orderIds);

See https://msdn.microsoft.com/en-us/library/ms175972%28v=sql.105%29.aspx for Microsoft's documentation.