SQL querying multiple schemas

Question

I am looking to run a query on several schemas in workbench. bascially, they are all symmetric , just different dates. In workbench, i can only select one of them and run the query. Is there a way to aggregate them and run the query over a selection of schemas?

EDIT: To elaborate a bit more, I have schemas with names yyyy_mm_dd for each day. Ideally, instead of doing a union over them as suggested by Guish below, If would like a dynamic query that would be able to turn the name of the schema into a valid date and Union all of them where the date is within a defined range. Is this possible? I am using Oracle and sql workbench

There is for most DB platforms -- but they are platform specific -- what database platform? — Hogan, May 08 '14 at 01:01
Please provide examples of the “schemas” you are talking about, and the results you want. — Dour High Arch, May 08 '14 at 01:25

score 1 · Accepted Answer · edited May 23 '17 at 10:26

1

I guess you are using mySql workbench.

Use an union operator.

(SELECT a FROM `schema1`.`t1` )
UNION
(SELECT a FROM `schema2`.`t1`);

Info here

You can then create a view from your query.

A thread here on querying multiple shema

In know Transact-SQL a lot more and it is similar.

SELECT ProductModelID, Name
FROM Schema1.ProductModel
UNION ALL
SELECT ProductModelID, Name
FROM Schema2.ProductModel
ORDER BY Name;

edited May 23 '17 at 10:26

Community

1
1

answered May 08 '14 at 01:13

Guish

4,968
1
37
39

`UNION ALL` is faster for statements like this which are guaranteed to be mutually exclusive, since `UNION` will attempt to remove duplicates (which don't exist). – Hogan May 10 '14 at 14:18

SQL querying multiple schemas

1 Answers1