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.