Most efficent way to limit rows returns from union query- TSQL

Question

I have a simple stored proc with two queries joined with a union:

select name as 'result'
from product
where...

union

select productNum as 'result'
from product
where...

I want to limit this to the TOP 10 results.

If I put TOP 10 in each seperate query I get 20 results total.

What is the most efficient way to limit total results to 10? I dont want to do TOP 5 in each because I may end up in a situation where I have something like 7 "names" and 3 "productsNumbers".

Answer 1

WITH Results (Result)
AS
(

select name as 'result'
from product
where...

union

select productNum as 'result'
from product
where...
)

SELECT TOP 10 * FROM Results

Common Table Expression

Answer 2

select top 10 * from
(
select top 10 ....
from ....
where ....

union

select top 10 ....
from ....
where ....
) x

is the basic idea. Adding the top 10 to each union means you will have a smaller set to limit in the outer query.

If you want to prioritise (i.e. return as many as possible from first result) then you could do this:

select top 10 * from
(
select top 10 
1 as prio_col, ....
from ....
where ....

union

select top 10 
2 as prio_col....
from ....
where ....
) x
order by prio_col

so that you get as many as possible from the first set, and only use results from the second set as a "fallback".