Does the optimizer filter subqueries with outer where clauses
- by Mongus Pong
Take the following query:
select * from
(
select a, b
from c
UNION
select a, b
from d
)
where a = 'mung'
Will the optimizer generally work out that I am filtering a on the value 'mung' and consequently filter mung on each of the queries in the subquery.
OR
will it run each query within the subquery union and return the results to the outer query for filtering (as the query would perhaps suggest)
In which case the following query would perform better :
select * from
(
select a, b
from c
where a = 'mung'
UNION
select a, b
from d
where a = 'mung'
)
Obviously query 1 is best for maintenance, but is it sacrificing much performace for this?
Which is best?
