General subselect question

Started by Tim Hartover 17 years ago1 messagesgeneral
Jump to latest
#1Tim Hart
tjhart@me.com

In the general case, is a subselect that uses union less performant
than a union?

I have a query that looks something like this:

select <some columns>
from table1,
table2,
table3
where <where clause>
union
select <select clause>
from table1,
table2,
table3 ref1,
table3 ref2
where <where clause>

I realized today that I could convert the query to

select <some columns>
from table1,
table2,
( select <some columns>
from table3
where <where clause>
union
select <some columns>
from table3 ref1,
table3 ref2
where <where clause> )
where <where clause>

For my specific case, I'll use EXPLAIN to determine the performance
difference. I'll weigh the performance benefits with the maintenance
benefits ( less duplication of code for me on the second case) and
decide (assuming I'm still motivated on Friday ;) ).

But in the general case, do I have to pay special attention with
unions in subselects?

Tim Hart
615-713-9956 :cell
timothyjhart :Y!
tjhart@me.com :AIM