Which query has a better performance?

Question

SELECT *
FROM { SELECT * FROM BigMillionTable UNION ALL SELECT * FROM SmallTensTable } 
WHERE (some_condition)

Vs

SELECT * 
FROM BigMillionTable 
WHERE (some_condition)

UNION ALL

SELECT * 
FROM SmallTensTable
WHERE (some_condition) 

My questions:

1. Does the first query need to put all the rows in the BigMillionTable in the main memory to perform UNION ALL ?
2. Which query provides better performance ?

Answer 1

The where condition is actually executed before the select, so in the 2nd query you are only doing a union all on a smaller set, so that should be faster.

In the 1st query, the inner query will run first, which will do a union all on the whole table and then the where would be executed before selecting the records.

So yes, 2nd would provide better performance compared to the 1st one