Is it more efficient to union from sub-query or combine two outer queries?

Question

I searched for this but couldn't find the response I needed. So my apologies if this has already been asked.

I'm asking in regards to DB2 but I suppose this would apply, generally speaking, to just about any database.

Anyway, say I have two tables in the same database:

select ID, EMAIL from TBL1
select ID, EMAIL from TBL2

What I then want to do is search for a field within those two tables. The field could be in either table.

So, I have tried the following:

select ID, EMAIL from TBL1 where EMAIL = 'blah@aol.com'
union
select ID, EMAIL from TBL2 where EMAIL = 'blah@aol.com'

This works. But is redundant and ugly.

However, is it more efficient than:

select T.* from
(
    select ID, EMAIL from TBL1
    union
    select ID, EMAIL from TBL2
) T where T.EMAIL = 'blah@aol.com'

Both seem to work. Unfortunately, I don't have any real profiling tools for DB2 (along with restricted access) so I can't seem to figure out which is better. They both execute at roughly the same speed in the console but that isn't a good indicator.

Any tips on this (or even how to maybe profile it myself) would be appreciated.

Of course, this is a highly simplified example. The real queries are quite massive and span 9-18 tables, databases, etc.

Thanks

EDIT

The DB2 is on iSeries and I have no idea what version it is. Everything I try to use gives an error. I want to say it's version 8 but I really don't know.

If anyone wants to clue me in on how to give more information I will. I don't know much about DB2.

Answer 1

You need to use the explain facilities in DB2 to answer this question, it's highly specific to the actual queries you are executing, and you can't get a general answer.

IBM i 7.2 (and previous versions) have Visual Explain.

If you are restricted from doing this, then you need to speak to the system or database administrator for your environment.