Multiple Tables in FROM Clause

michael4606
michael4606 used Ask the Experts™
on
Hello,

In an Oracle 10g database I want to run a SQL statement for multiple tables that all contain the same column.  I know it can be done in PL/SQL using a cursor but would like to know if it can be done in standard SQL.

I am in affect wanting to run it for multiple tables in a FROM command.  I don't know if this is possible.

What can you recommend?


Thanks,

Michael


Pseudo Code
 
SELECT item_name
FROM t1.item_type,t2.item_type,t3.item_type ... t50.item_type
WHERE *.item_type = 'XYZ';

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
SELECT item_name
FROM t1.item_type
WHERE item_type = 'XYZ';

union
 
SELECT item_name
FROM t2.item_type
WHERE item_type = 'XYZ';

union
 
SELECT item_name
FROM t3.item_type
WHERE item_type = 'XYZ';

etc
Commented:
Can you not join the table on that colun? Ofcourse this will be a little heavy to run since the join is not a varchar type field. Something like this:
Select *
From  t1, t1, t3
Where  t1.Item_Type = t2.item_Type and
       t2.Item_Type = t3.Item_Type
Where t1.Item_Type = 'XYZ'

Open in new window

Commented:
Sorry I meant: "The join is ON A VARCHAR type column"
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2009
Commented:
Make sure there is an index on ITEM_TYPE on all those tables.
Naveen KumarProduction Manager / Application Support Manager
Commented:
if you want to have multiple tables in the from clause ,then best way is to give aliases to the table names and then prefix the column name with table alias because you said your tables are having same column names something like as shown below for 3 sample tables...

SELECT a.col1, b.col1, c.col1
FROM my_table1 a,my_table2 b, my_table3 c
WHERE a.col1 = 'XYZ'
and b.col1 = 'A'
and ....
and a.idcol = b.idcol   -- join condition while query more than one table if required
and b.idcol = c.idcol   -- join condition while query more than one table if required
... ;

SELECT a.col1, b.col1, c.col1
FROM my_table1 a,my_table2 b, my_table3 c
WHERE a.col1 = 'XYZ' 
and b.col1 = 'A' ;

Open in new window

Mark GeerlingsDatabase Administrator

Commented:
I'm not clear on what you are trying to achieve.  In your pseudo-code example, it looks like you want to retrieve just a single column value, but this could come from any of many different tables.  Is that correct?  That seems like an unusual request in a relational database.  A "union" or "union all" query can do that, if that is what you really want.

Do you need to know which table the result was found in, or doesn't that matter?

Author

Commented:
Thanks for the posts!

I tried all the examples and the UNION command came closest to how I wanted to implement it.  Surprisingly it did not place a large load on the system.  

I used this form of it:

SELECT '1' FROM schema1.CONFIGURATION a WHERE a.ConfigurationName LIKE '%XYZ%'
UNION
SELECT '2' FROM schema2.CONFIGURATION a WHERE a.ConfigurationName LIKE '%XYZ%'
;

I'll leave this open a bit longer to see if anyone has more feedback on this.
Mark GeerlingsDatabase Administrator
Commented:
The amount of load this kind of (non-optimized) query will place on your system will depend on the numbers of records in those tables, and on the size of the BUFFER_CACHE in the SGA.  (It may also depend on the speed of your disks and/or disk system if the data blocks for these tables are not already cached.)

Using "union all" instead of "union" may also help a bit, since then Oracle does not have to sort the results and eliminate duplicates.  (When you use "union" Oracle will do those actions whether you need it or not.)

If these tables are indexed on the ConfigurationName column, and if you can modify the query to be:
WHERE a.ConfigurationName LIKE 'XYZ%'
(with no leading wild-card)
then Oracle could use an index and likely be *MUCH* more efficient.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial